Parsing Text into an Access table

[SBinVA]

Here's the challenge:

I have a request to be able to import a Standard Interchange Format
(.sif) file into an Access database on demand. The file layout is
standard ASCII text and is fairly simple, but I can't seem to wrap my
head around this one since it's not a normal request of me.

The file looks like this when opened in a text viewer:

PN=xxxx
PD=yyyy
P%=zzzz
PN=aaaa
PD=bbbb
P%=cccc

Then the cycle repeats. (I left out the majority of the "fields" to
save space since there are about 27.)

I need to pull this into a table somewhat like this:

PN PD P%
xxxx yyyy zzzz
aaaa bbbb cccc

All of the "column" identifiers are 2 letters followed by the equal
sign and are consistent from file to file

I'm looking to write some code that I can use to parse the file after
the user has designated it's location and read it into a temp table in
the database or even a recordset or array. After that's done I'll loop
through the records and extract the data I need and write it to the
necessary tables.

I'm assuming the code will need to read the file then find the first
instance of "PN=" and write whatever is between "PN=" and the end of
line code to column 1 of the temp table / recordset / array and then do
the same for the next identifier and so on until it reaches "PN=" the
next time. At this point it should go to the next record and begin
writing the values to the next record and continuing until it reaches
EOF.

The logic seems pretty sound but I'm just not sure about how to
accomplish this in VBA. Any ideas?

 

[John Nurick]

Is there anything in the file (apart from the recurrence of the first
field name) that indicates where one record ends and the next ends? For
instance, is there a blank line between records?

Will you know in advance how many fields there are, and what names (so
you can create the temp table in advance)? Or will your code have to
parse that information out of the text file?

Setting that sort of thing aside, you could do it like this (assuming
the field names are known in advance and the table has been created).
NB: this is not intended to be runnable code:

Dim rsR As DAO.Recordset
Dim lngFN As Long
Dim strLine As String
Dim varParsedLine As Variant

lngFN = FreeFile()
Open "C:\folder\file.txt" For Input As #lngFN

Set rsR = CurrentDB.OpenRecordset("MyTable")

Do Until Eof(lngFN)
rsR.AddNew 'create new record
Do
Line Input #lngFN, strLine

'get field name and value into elements of an array
varParsedLine = Split(strLine, "=", 2)

'assign the value to the field in the new record
rsr.Fields(varParsedline(0)).Value = varParsedLine(1)

Loop Until End of Record 'however that is tested

'we now have a complete new record
rsr.Update 'append it to table

Loop 'and start on next record

Close #lngFN
rsR.Close

 

[SBinVA]

Thank you so much.... this worked great! Once I got into it I realized
the file wasn't as consistent as I initially thought, so I had to make
some changes, but your response was a great help.

 

[John Nurick]

Thanks for the feedback. It's great when someone takes an idea and works
the rest out!