VBA Text import with double lines for one record

[vtaurusv]

Hello, I'm familiar with importing basic data with one line for a specific record but have never dealt with records with multiple lines for one record.



I have this 3rd party txt file that I need to import into a table and then compare our records using the Social security number as the relationship and go from there, but I also need one item on the end of the second line.

Here is an example. I am trying to create a table (IMPORT) with 2 fields SSN and CODE, importing the Social security number (000-00-0000) and the last letter in the second line with can contain a single letter (B,D,E,N)

I've been reading on this subject and it was pointing towards VBA; not quite there yet with my limited knowledge. Any help would be greatly appreciate!


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
000-00-0001 JOHNSON SAMANTHA 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 E
000-00-0001 JOHNSON SAMANTHA 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 N

[JoeM]

Here are a few non-VBA options, off the top of my head. Some involve a little more work than others.


1. Edit the text file directly. In your example, the first "half" of every record appears to end with "01/01/1901 2". If EVERY half record ends like this, you could do a Search and Replace in a Text Editor replacing "01/01/1901 2" + the carriage return character with "01/01/1901 2" (without the carriage return character). This would make eliminate the carriage return after the first half line, combining each to their natural single row.


2. Import into Access, including an Autonumber field. Create two queries to build a linking field between the two queries, based on the Autonumber.

First, import all the records as a single long, text string (being sure to include an Autonumber field at import time. Then create the following two queries.

Query 1
We need to build Criteria to only select the odd number records. If our Autonumber field is named "ID", we can do it by creating a calculated field like this:
OddChk: [TableName]![ID] Mod 2
and then placing
=1
in the Criteria row of this field.

Then, we need to create our matching field. We can do it with another calculated field, like this:
MatchingField: ([TableName]![ID]+1)/2

Be sure to also include your single string field in this query.

Query 2
We need to build Criteria to only select the even number records. If our Autonumber field is named "ID", we can do it by creating a calculated field like this:
EvenChk: [TableName]![ID] Mod 2
and then placing
=0
in the Criteria row of this field.

Then, we need to create our matching field. We can do it with another calculated field, like this:
MatchingField: ([TableName]![ID])/2

And be sure to also include your single string field in this query.

Now, join these two queries on MatchingField and concatenate the two string portions.


3. Open in Excel, and combine them there, then import into Access.
You would use logic similar to what I used up above for the Access option. Instead of AutoNum, you can use ROW() to return the current row number.

[vtaurusv]

Very sorry, I should have not modified the dates. These dates are random, they consist of Date of birth, and other dates related to their account. Modified them to make it look cleaner when reviewing it.

[JoeM]

OK, then option #1 is probably out, but options #2 and #3 are still on the table.

[ssanfu]

Maybe Option #4??

Table named "Import"
Fields "SSN" & "Code"

1) Uses DAO
In the IDE (VBA Editor), there must be a reference set to "Microsoft DAO3.6 Object Library"

2) This assumes there is NO header line. (as per example data)

3) Change the line in BLUE to the path and name of your text file.

In a standard module, paste in the following code: (Name the module "modImportCode")

Public Sub TextImport()
   Dim i As Integer
   Dim RF As Integer  'RF read file
   Dim FileName As String
   Dim strInput As String
   Dim sSSN As String
   Dim sCode As String
   Dim sSQL As String

   FileName = "C:\AccMDB\SSNImport.txt"
   RF = FreeFile
   Open FileName For Input As #RF


' 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 FileName For Input As #RF
   Do While Not EOF(RF)
      sSSN = Empty
      sCode = Empty
      strInput = Empty

      Line Input #RF, strInput
      sSSN = Left(Trim(strInput), 11)
      Line Input #RF, strInput
      sCode = Right(Trim(strInput), 1)

      sSQL = "INSERT INTO Import(SSN, Code)"
      sSQL = sSQL & " VALUES ('" & sSSN & "', '" & sCode & "');"
      CurrentDb.Execute sSQL, dbFailOnError
      '      Debug.Print sSQL
   Loop

   Close #RF
   MsgBox "Done"
End Sub

Execute the code. (While in the IDE, press the F5 key)

[JoeM]

I think I misunderstood the question. In the original post, I saw:

I've been reading on this subject and it was pointing towards VBA; not quite there yet with my limited knowledge. Any help would be greatly appreciate!

and I took that to mean that they were hoping to do that without the use of VBA (which is why my first reply said "Here are a few non-VBA options...").

But in looking at the title again, I don't think that is what they meant. It looks like they want to use VBA, but need help in doing so.
VBA is definitely the cleanest/fastest way to go...

Sorry I misinterpretted that. Looks like Steve posted some nice VBA code for you.

[vtaurusv]

Thank you very much both of you! I learned a lot by your posts.

I went with the VBA script and worked with those records I posted. I then went and tried to import a bigger batch and found the script did not like it. Basically this file can consist of odd and even records but also for multiple transactions for 1 person.

Example there could be 6 lines for one SSN and 5 lines for another record.

Is there a simple tweak to the VBA code to catch this?

[vtaurusv]

Hmmmmm think I did it? I removed this line.

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

[vtaurusv]

I put the 15 in there

[vtaurusv]

Did not work, that was a fail. lol

[ssanfu]

It all depends on the text file structure.

because you wanted the SSN from one line and the last letter from the next line, the text file HAS to have an even number of lines.

If i Mod 2 <> 0 Then

In this line of code, i is the count of lines in the text file. If the count is even, "i Mod 2" should have a remainder of 0, otherwise it is 1. (See MOD in help).

I then went and tried to import a bigger batch and found the script did not like it. Basically this file can consist of odd and even records but also for multiple transactions for 1 person.

In the bigger file, what is the structure??

Yes the code can be tweaked. One thing that might word is to read a line and check if the first 11 characters have the format of a SSN. If it does, save as SSN, else get the last char of the line. Would have to see the data.

[vtaurusv]

Hi Steve, Thank you for taking your time to help. The structure of the data is the same. Example, some records can have multiple lines. Each record basically represents a transaction. Some could consist of multiple lines maybe up to 4-10.

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
000-00-0001 JOHNSON SAMANTHA 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 E
000-00-0001 JOHNSON SAMANTHA 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 N
000-00-0001 JOHNSON SAMANTHA 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 N
000-00-0001 JOHNSON SAMANTHA 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 E

[ssanfu]

Still don't understand the problem.

The text file MUST:
- Not have a header line(s)
- Not have any blank lines

- Have an even number of lines
- Have "Paired" lines - the 1st line of a pair has the SSN, the 2nd line has a letter as the last character.


Did the large file not read to the end?

[vtaurusv]

Hmmmm, took a look again at the file. There is no header and the first line begins with the SSN.

When I run the VBA script I get the message

Odd number of lines in text file
Must be an even number of lines

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

[vtaurusv]

Steve it worked!!! Thank you!!!! It's fault, there are thousands of records and I found about 2 issues that the script did not like that I though I could see earlier.
You're awesome!