VBA Text import with double lines for one record

[ssanfu]

Im thinking it has to do with having multiple lines with the same SSN?

No, it means there must be an even number (ie "Pairs") of lines in the text file.

Are there blank lines at the end if the text file?

The text file HAS to look like this (expanded to see the pairs of lines)

000-00-0000 SMITH JOHN C 01/01/1901 00000000 N 01/01/1901 01/01/1901 2
000000 AA AA 01/01/1901 00/00/0000 01/01/1901 $ 0,000 800 01/01/1901 G 01/01/1901 D

000-00-0000 SMITH JOHN C 01/01/1901 00000000 N 01/01/1901 01/01/1901 2
000000 AA AA 01/01/1901 00/00/0000 01/01/1901 $ 0,000 800 01/01/1901 G 01/01/1901 D

000-00-0000 SMITH JOHN C 01/01/1901 00000000 N 01/01/1901 01/01/1901 2
000000 AA AA 01/01/1901 00/00/0000 01/01/1901 $ 0,000 800 01/01/1901 G 01/01/1901 B

It cannot look like:

000-00-0000 SMITH JOHN C 01/01/1901 00000000 N 01/01/1901 01/01/1901 2
000000 AA AA 01/01/1901 00/00/0000 01/01/1901 $ 0,000 800 01/01/1901 G 01/01/1901 D

000-00-0000 SMITH JOHN C 01/01/1901 00000000 N 01/01/1901 01/01/1901 2

000-00-0000 SMITH JOHN C 01/01/1901 00000000 N 01/01/1901 01/01/1901 2
000000 AA AA 01/01/1901 00/00/0000 01/01/1901 $ 0,000 800 01/01/1901 G 01/01/1901 B

The 2nd line of a pair is missing.

[vtaurusv]

Hi Steve, Of course now they are requesting some parts of other data, so I tried to capture those parts and was unsuccessful. I would get the parts on the other line etc.

I am wondering how do I get everything on just 1 line? I will end up querying parts of the line as needed.

Example:

000-00-0000 SMITH JOHN C 01/01/1901 00000000 N 01/01/1901 01/01/1901 2 000000 AA AA 01/01/1901 00/00/0000 01/01/1901 $ 0,000 800 01/01/1901 G 01/01/1901 D

Thank you

[ssanfu]

Re-write the text file, concatenating the two lines.
Then use the import wizard to import the new file into a table. You can determine what data goes to which field.

This sub will concatenate the two lines. Paste this into a standard module. Change the file path & file names. (in BLUE)

Public Sub WriteToNewFile()
   Dim i As Integer
   Dim RF As Integer  'RF read file
   Dim WF As Integer  'RF write file
   Dim ReadFileName As String
   Dim WriteFileName As String
   Dim strInput As String
   Dim Line1 As String
   Dim Line2 As String
   Dim strNewLine As String

   'path & file to read from
   ReadFileName = "C:\AccMDB\SSNImport.txt"
   'path & file to write to
   WriteFileName = "C:\AccMDB\SSNOneLine.txt"

   'open read text file
   RF = FreeFile
   Open ReadFileName For Input As #RF

   'open wtire text file
   WF = FreeFile
   Open WriteFileName For Output As #WF


   ' since the example had an even number of lines, check to see if the text file has an even number of lines
   Do While Not EOF(RF)
      Line Input #RF, strInput
      i = i + 1
   Loop
   Close #RF

   If i Mod 2 <> 0 Then
      MsgBox "Odd number of lines in text file" & vbNewLine & vbNewLine & "Must be an even number of lines" & vbNewLine & vbNewLine & "Exiting!!"
      Exit Sub
   End If

   ' good to go
   Open ReadFileName For Input As #RF
   Do While Not EOF(RF)
      Line1 = Empty
      Line2 = Empty
      strNewLine = Empty

      Line Input #RF, strInput
      Line1 = strInput
      Line Input #RF, strInput
      Line2 = strInput

      'concatenate lines
      strNewLine = Line1 & " " & Line2

      'write the new line
      Print #WF, strNewLine
   Loop

   Close #RF
   Close #WF
   MsgBox "Done"
End Sub

An alternative is to use the previous code to parse the two lines into variables, then append to a table. More code to write and test, but will also work.

[vtaurusv]

Steve, This code worked flawlessly! Thanks again! Got VBA for Dummies on my Kindle. Got a lot of catching up

[ssanfu]

Wonderful.
Ready to mark this solved?

[vtaurusv]

Yes Solved