By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Export csv file from excel with line breaks

didajosh
P: 47
Please help me
Here is what my data looks like in excel:

NAME--ADDRLINE1--ADDRLINE2--CITY--STATE--ZIP--COUNTRY
DJ----12 APT 2---EAST LANE--NEW YORK--NY--45658--USA
MJ----13 PARK MAR--NORTH LANE--NEW YORK--NY--45658--USA

I want to export this in a text/csv file so that it looks like:

DJ
12 APT 2
EAST LANE
NEW YORK
NY
45658
USA

Any suggestions..!!
Feb 7 '14 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,597
You may have to perform this operation by Code since it is a non-standard Output Format, but it is a very simple matter as long as you can define the Data Range. I may be wrong on this assumption, so something better may come along. Any questions please feel free to ask.
  1. Code Definition:
    Expand|Select|Wrap|Line Numbers
    1. Dim rng1 As Range
    2. Dim rng2 As Range
    3.  
    4. Set rng1 = Range("A2:G3")   'Exclude Field Header
    5.  
    6. Open ActiveWorkbook.Path & "\Export.txt" For Output As #1
    7.  
    8. For Each rng2 In rng1
    9.   Print #1, rng2.Value
    10. Next
    11.  
    12. Close #1
  2. Contents of Export.txt:
    Expand|Select|Wrap|Line Numbers
    1. DJ
    2. 12 APT 2
    3. EAST LANE
    4. NEW YORK
    5. NY
    6.  45658 
    7. USA
    8. MJ
    9. 13 PARK MAR
    10. NORTH LANE
    11. NEW YORK
    12. NY
    13.  45658 
    14. USA
Feb 8 '14 #2

didajosh
P: 47
Holy monkey this worked like magic!!!!
Wow, thank you for helping me!!

OK so I simplified my problem to easily explain here.
And I thought I will get the hint and take it from there.

I am sorry for pushing my luck, but I am clueless in VBA.

Its slightly more complicated than that...
My data range is inconsistent. And I have a file header and a file trailer.

My excel file looks like :
--------------------------------------------------------------------------------------------
FILHDR---OWN---UUPVTLTD---02/06/2014---500
PAYHDR---UPS---PYMT-------02/06/2014---200---PAYN---DJ SMITH---123---PAYADD---12 APT 2---EAST LANE---NEW YORK
PAYHDR---UPS---PYMT-------02/06/2014---300---PAYN---MJ DAVID---456---PAYADD---15 LAKE PT---DRIVE LANE---MIAMI
PAYDESC---PAY MONEY NOW
FILTRL---5
--------------------------------------------------------------------------------------------

I want to export this in a csv file so that it looks like:

FILHDR,OWN, UUPVTLTD,02/06/2014,500
PAYHDR,UPS
PYMT,02/06/2014,200
PAYN,DJ SMITH,123
PAYADD,12 APT 2,EAST LANE,NEW YORK
PAYHDR,UPS,PYMT,02/06/2014,300
PAYN,MJ DAVID,456
PAYADD,15 LAKE PT,DRIVE LANE,MIAMI
PAYDESC,PAY NOW
FILTRL,5

------------------------------------------------------------
So file header, Description and trailer appears only once in the file.
The last number with file trailer has number of lines in the CSV file.


Any help will be greatly appreciated.
Feb 10 '14 #3

didajosh
P: 47
OK this I got this far:

Expand|Select|Wrap|Line Numbers
  1. Dim rng1 As Range
  2. Dim rng2 As Range
  3.  
  4. 'Finding last cell
  5.  
  6.   Dim lRealLastRow As Long
  7.   Dim lRealLastColumn As Long
  8.   Range("A1").Select
  9.   On Error Resume Next
  10.   lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, _
  11.                                                xlPrevious).Row
  12.   lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , _
  13.                                     xlByColumns, xlPrevious).Column
  14.  'Finding last cell
  15.  
  16. Set rng1 = Range("A1", Cells(lRealLastRow, lRealLastColumn))   
  17.  
  18. Open ActiveWorkbook.Path & "\Refund.txt" For Output As #1
  19.  
  20. For Each rng2 In rng1
  21.   Print #1, rng2.Value
  22. Next
  23.  
  24. Close #1
So far I am able to select entire sheet.And export one cell per line on a text sheet.
I am trying to export multiple cells on a single text line.
Feb 10 '14 #4

ADezii
Expert 5K+
P: 8,597
I struggled on finding the Logic that would generate the Export File given the overall inconsistencies of the Data. I have arrived at a workable solution, although I feel that it may not be the 'Optimal' one. In any event here it goes along with an explanation as well as the Demo Spreadsheet used with this Thread.
  1. Define the complete Data Range as it exists in the Spreadsheet. You can overextend, but not under.
  2. Loop through every Cell in the specified Range and search for specific Keywords such as: FLHDR, PAYHDR, PYMT, PAYN, PAYADD, PAYDESC, and FILTRL.
  3. If a Cell contains one of these Keywords, start building a Comma Delimited String starting with the Keyword itself plus (&) an x amount of successive Cell Values depending on the Keyword.
  4. Write the built String to the Export File.
  5. RESET the Build String (not really necessary).
  6. Close the Export File and inform User of its whereabouts.
  7. This Logic would handle most of the inconsistencies, but the Values for each of the Keywords must be adjacent to them and consist of the same number of Cells.
  8. Code definition.
    Expand|Select|Wrap|Line Numbers
    1. Sub Button1_Click()
    2. Dim rng1 As Range
    3. Dim rng2 As Range
    4. Dim strBuild
    5. Dim intCtr As Integer
    6.  
    7. Set rng1 = Range("A1:L12") 
    8.  
    9. Open ActiveWorkbook.Path & "\Export.txt" For Output As #1
    10.  
    11. For Each rng2 In rng1
    12.   Select Case rng2.Value
    13.     Case "FILHDR"       'Header, FILHDR & next 4 Cell Values
    14.       strBuild = rng2.Value & ","
    15.         For intCtr = 1 To 4
    16.           strBuild = strBuild & Cells(rng2.Row, rng2.Column + intCtr) & ","
    17.         Next
    18.       Print #1, Left$(strBuild, Len(strBuild) - 1)
    19.         strBuild = ""
    20.     Case "PAYHDR", "PAYDESC", "FILTRL"      'rng2 & next Cell Value
    21.       strBuild = rng2.Value & "," & Cells(rng2.Row, rng2.Column + 1)
    22.         Print #1, strBuild
    23.           strBuild = ""
    24.     Case "PYMT", "PAYN"     'rng2 & next 2 Cell Values
    25.       strBuild = rng2.Value & "," & Cells(rng2.Row, rng2.Column + 1) & _
    26.                  "," & Cells(rng2.Row, rng2.Column + 2)
    27.         Print #1, strBuild
    28.           strBuild = ""
    29.     Case "PAYADD"
    30.       strBuild = rng2.Value & ","       'PAYADD & next 3 Cell Values
    31.         For intCtr = 1 To 3
    32.           strBuild = strBuild & Cells(rng2.Row, rng2.Column + intCtr) & ","
    33.         Next
    34.       Print #1, Left$(strBuild, Len(strBuild) - 1)
    35.         strBuild = ""
    36.     Case Else       'do nothing
    37.   End Select
    38. Next
    39.  
    40. Close #1
    41.  
    42. MsgBox "Export.txt can now be viewed in " & ActiveWorkbook.Path & "\", _
    43.         vbInformation, "Export Complete"
    44. End Sub
    45.  
  9. Contents of Export.txt with your Original Data plus my own set of Dummy Data.
    Expand|Select|Wrap|Line Numbers
    1. FILHDR,OWN,UUPVTLTD,2/6/2014,500
    2. PAYHDR,UPS
    3. PYMT,2/6/2014,200
    4. PAYN,DJ SMITH,123
    5. PAYADD,12 APT 2,EAST LANE,NEW YORK
    6. PAYHDR,UPS
    7. PYMT,2/6/2014,300
    8. PAYN,MJ DAVID,456
    9. PAYADD,15 LAKE PT,DRIVE LANE,MIAMI
    10. PAYDESC,PAY MONEY NOW
    11. PAYHDR,UPS
    12. PYMT,2/10/2014,175
    13. PAYN,RD BARNES,888
    14. PAYADD,#16,TYROS AVE.,PHILADELPHIA
    15. PAYHDR,UPS
    16. PYMT,2/11/2014,400
    17. PAYN,A FLINTSTONE,233
    18. PAYADD,3333 - APT 5B,3333 LAKESHORE,HOUSTON
    19. PAYDESC,PAY MONEY NOW
    20. FILTRL,5
    21.  
  10. Just view the Attachment! (LOL).
Attached Files
File Type: zip Test.zip (16.1 KB, 77 views)
Feb 11 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.