Import .txt file where some data is split over two lines

[gwatch]

I have a text file that has some rows split over 2 lines (this is result of a macro to save email body as text file doing this - which I am unable to precent despite investigating the boards)

I have a text file with 8 comma delimited columns

The data is wrapped in "

in my text file some rows are split accross 2 rows (ie a line break in column 1)

sample text file below:

"SMITH","","0","0","0","0","0","0"
"JONES","22,969,609","4","14,504,054","4","0","0","0"
"BROWN","314,202,930","1,421","1,637,671,478","1,421","0","0","0"
"FRANKS","23,225,674","1,486","246,170,340","1,486","0","0","0"
"THOMSON
Standard","","281","33,913,687","281","0","0","0"
"WILLIAMS Account - special -
EM","","0","0","0","0","0","0"​

I would like to import the text file and concatenate the offending lines (i.e. lines that do not start with ")

I have seen several similar-ish posts and managed to do the adapt the replies - but I am stuck with this code that does not really acheive what I need

I can import the lines beginning with "
I can import the line starting with Standard - but not the next line - OR other liens in the file split accross 2 rows

The code I have is:

Sub import_lines()
 
  Dim intFH As Integer
  Dim sRec As String
  Dim iRow As Long
  
  Columns("A:B").ClearContents
  Close
  intFH = FreeFile()
  iRow = 0
  Open "c:\sample.txt" For Input As #intFH
  Do Until EOF(intFH)
    Line Input #intFH, sRec
    Select Case Left(sRec, 1)
      Case Chr(34)
        iRow = iRow + 1
        Cells(iRow, 1) = sRec
      Case Else
        Cells(iRow, 2) = sRec
    End Select
  Loop
 Close
 
End Sub

My aim is to merge the split lines - and then convert text to columns using the comma as the delimiter


Any help - much appreciated


tks

 

[mjbeam]

Sub import_lines()
 
  Dim intFH As Integer
  Dim sRec As String
  Dim iRow As Long
  
  Columns("A:B").ClearContents
  Close
  intFH = FreeFile()
  iRow = 0
  Open "c:\sample.txt" For Input As #intFH
  Do Until EOF(intFH)
    Line Input #intFH, sRec
    Select Case Left(sRec, 1)
      Case Chr(34)
        iRow = iRow + 1
        Cells(iRow, 1) = sRec
      Case Else
        Cells(iRow, 1) = Cells(iRow, 1) & sRec
    End Select
  Loop
 Close
 
 Columns("A:A").Select
 Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True
 
End Sub

 

[gwatch]

Thank you mjbeam!