Export csv file from excel with line breaks

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • didajosh
    New Member
    • Aug 2008
    • 47
    #1

    Export csv file from excel with line breaks

    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..!!
    Tags: None
    • ADezii
      Recognized Expert Expert
      • Apr 2006
      • 8834
      #2
      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:
        Code:
        Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range("A2:G3")   'Exclude Field Header

Open ActiveWorkbook.Path & "\Export.txt" For Output As #1

For Each rng2 In rng1
  Print #1, rng2.Value
Next

Close #1
      2. Contents of Export.txt:
        Code:
        DJ
12 APT 2
EAST LANE
NEW YORK
NY
 45658 
USA
MJ
13 PARK MAR
NORTH LANE
NEW YORK
NY
 45658 
USA

        Comment

        • didajosh
          New Member
          • Aug 2008
          • 47
          #3
          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.

            Comment

            • didajosh
              New Member
              • Aug 2008
              • 47
              #4
              OK this I got this far:

              Code:
              Dim rng1 As Range
Dim rng2 As Range

'Finding last cell

  Dim lRealLastRow As Long
  Dim lRealLastColumn As Long
  Range("A1").Select
  On Error Resume Next
  lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, _
                                               xlPrevious).Row
  lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , _
                                    xlByColumns, xlPrevious).Column
 'Finding last cell
  
Set rng1 = Range("A1", Cells(lRealLastRow, lRealLastColumn))   
 
Open ActiveWorkbook.Path & "\Refund.txt" For Output As #1
 
For Each rng2 In rng1
  Print #1, rng2.Value
Next
 
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.

                Comment

                • ADezii
                  Recognized Expert Expert
                  • Apr 2006
                  • 8834
                  #5
                  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.
                    Code:
                    Sub Button1_Click()
Dim rng1 As Range
Dim rng2 As Range
Dim strBuild
Dim intCtr As Integer

Set rng1 = Range("A1:L12") 

Open ActiveWorkbook.Path & "\Export.txt" For Output As #1

For Each rng2 In rng1
  Select Case rng2.Value
    Case "FILHDR"       'Header, FILHDR & next 4 Cell Values
      strBuild = rng2.Value & ","
        For intCtr = 1 To 4
          strBuild = strBuild & Cells(rng2.Row, rng2.Column + intCtr) & ","
        Next
      Print #1, Left$(strBuild, Len(strBuild) - 1)
        strBuild = ""
    Case "PAYHDR", "PAYDESC", "FILTRL"      'rng2 & next Cell Value
      strBuild = rng2.Value & "," & Cells(rng2.Row, rng2.Column + 1)
        Print #1, strBuild
          strBuild = ""
    Case "PYMT", "PAYN"     'rng2 & next 2 Cell Values
      strBuild = rng2.Value & "," & Cells(rng2.Row, rng2.Column + 1) & _
                 "," & Cells(rng2.Row, rng2.Column + 2)
        Print #1, strBuild
          strBuild = ""
    Case "PAYADD"
      strBuild = rng2.Value & ","       'PAYADD & next 3 Cell Values
        For intCtr = 1 To 3
          strBuild = strBuild & Cells(rng2.Row, rng2.Column + intCtr) & ","
        Next
      Print #1, Left$(strBuild, Len(strBuild) - 1)
        strBuild = ""
    Case Else       'do nothing
  End Select
Next

Close #1

MsgBox "Export.txt can now be viewed in " & ActiveWorkbook.Path & "\", _
        vbInformation, "Export Complete"
End Sub
                  9. Contents of Export.txt with your Original Data plus my own set of Dummy Data.
                    Code:
                    FILHDR,OWN,UUPVTLTD,2/6/2014,500
PAYHDR,UPS
PYMT,2/6/2014,200
PAYN,DJ SMITH,123
PAYADD,12 APT 2,EAST LANE,NEW YORK
PAYHDR,UPS
PYMT,2/6/2014,300
PAYN,MJ DAVID,456
PAYADD,15 LAKE PT,DRIVE LANE,MIAMI
PAYDESC,PAY MONEY NOW
PAYHDR,UPS
PYMT,2/10/2014,175
PAYN,RD BARNES,888
PAYADD,#16,TYROS AVE.,PHILADELPHIA
PAYHDR,UPS
PYMT,2/11/2014,400
PAYN,A FLINTSTONE,233
PAYADD,3333 - APT 5B,3333 LAKESHORE,HOUSTON
PAYDESC,PAY MONEY NOW
FILTRL,5
                  10. Just view the Attachment! (LOL).
                  Attached Files

                    Comment

                    Previous template Next
                    Working...