Import text file, multiple lines and varying delimiters/size to one record

[robert315]

Using MS 2007, I have a 200 text files exported each day from another application that has two different types of lines (see below). I would like to import each text file in to a database as a single record.



Text file example (text.txt):
R111 WC 8/21/2012 7:00 Doe, John doej 10110110
First Question? Y
Second Question? N
Third Question? Y
...
Seventeenth Question? 10

As you see, I have the first row with multiple fields, but the next rows I have a question and an answer.

I would like to have this data imported as shown in the attachment.
Example.zip

Most answers I see are for either multiple lines (same data and sizing). I am not sure how to handle several different lines with that vary in size and delimiters. Any help would be appreciated.

[June7]

If there is a consistent pattern, can be programmed.

Each file will be a single record?

All files have the same number of lines?

First line is always header information and has same structure?

All other lines have question mark?

Just a matter of reading each line and applying conditional statements to determine how to deal with it. Rough example:

'code to open text file
'code to open editable recordset of table and add new record
For i = 1 to 18
strLine = code to read line from text file
If InStr(strLine, "?")>0 Then
'code to set value of answer field in recordset - is question needed to be saved?
Else
'code to populate recordset fields with header information
End If
'code to move to next line of text file
Next

This is an exercise in logic and using the proper code structure to apply the logic. Write and test code. When you hit issue you can't resolve, post code for analysis.

[John_G]

You want to import into a database, but the attachment is an Excel file - which are you using - MS Access or MS Excel?

Complicating the issue is the fact that a) not all questions are answered and b) the answers are of different data types.

Can you post an example of a real data file, and tell us what you want to extract from it? If you are using a database, a description of its structure would help.

John

[robert315]

Each file will be a separate record.
There will be 3-4 different types of files, all with different line numbers...most will be the same line numbers, with only a dozen files different. I am confident I can import similar files by file name.
Header is always the same structure.
Only 1 line does not have question mark, it has a colon (.

[robert315]

John_G wrote:

You want to import into a database, but the attachment is an Excel file - which are you using - MS Access or MS Excel?

Attachment was an example of delineated output, not input.

[June7]

I gave you pseudocode example. Use it and other examples you have reviewed to write procedure specific to your data. Post code for analysis if encounter issue. Review link at bottom of my post for debug techniques.