Read/import a text file and split into multiple tables

[cmmrfrds]

I have a text file that has 7 different record types that will be loaded into a
separate table depending on the record type which is a value in the first 5
characters. Is there a way to conditionally import from a text file or to read a
text file in vb code and append a record to the appropriate table. I can't import
to one table since each record type has a different alinement of fields.

Does anybody have an example or the syntax to do this.

Thank you,
Jerry [sig][/sig]

 

[VB400]

Your best bet is to read each record and determine the record type then write it out to the appropriate table.

The general structure is:

- Open the text file
- read until EOF
- use Select Case mid$(recorddata, 1, 5) to parse each record
- within each case statement, write the record to the appropriate table

The exact syntax will depend on the structure of the text file (e.g. you may need to use the input# or get statement depending on the structure of the data). It will also depend on where you're writing the data.
[sig]<p> Tarek<br><a href= > </a><br>The more I learn, the more I need to learn![/sig]

 

[Ruairi]

Jerry,
Heres an example of what you need to do

Dim FileLine As String
Dim RecType As String
Dim LineNo As Long

Open &quot;C:\import.txt&quot; for input As #1
LineNo = 0

Do While Not EOF(1)

Line Input #1, FileLine

LineNo = LineNo + 1

RecType = Mid(FileLine, 1, 5)

Select Case RecType 'choose import based on first 5
Case &quot;XXXXX&quot;
Field1 = Mid(FileLine, 6, 20)
Field2 = Mid(FileLine, 27, 10)
Case &quot;YYYYY&quot;
Field1 = Mid(FileLine, 6, 11)
Field2 = Mid(FileLine, 18, 15)
Case Else
MsgBox &quot;Unrecognized record type in FileLine &quot; & LineNo
End Select
Loop

Close #1

Mid returns a portion of a string, it takes 3 arguments. The original string, the start position within that string, and the length of the substring to return. The first call i am making to mid returns the first 5 characters (your record identifier). I am putting that identifier into the RecType variable and using that to decide which type of import to do. I showed the select case statement for illustration. You need to change it so there is one 'Case' statement for each of the 5 different types of records. For example where i have 'Case &quot;XXXXX&quot;' You need to put the 5 characters that make up your first record identifier. Then add a Case for each type of record. Inside each case use Mid to extract the values for each field, then insert them into the correct table. Good luck
Ruairi [sig]<p>Ruairi<br><a href=mailto:ruairi@logsoftware.com>ruairi@logsoftware.com</a><br>[/sig]

 

[cmmrfrds]

Thank you, I appreciate the responses. I won't have any problem with the logic part, but I have never used the syntax for the &quot;Open&quot; and &quot;Line Input&quot; and my help file doesn't seem to have any information on this topic.

Ruairi, you have a very nice example which is easy to understand. I don't know if there will be a problem with defining the FileLine as Variant since the record will be fixed width of 1405 characters. I was planning on breaking the file into 255 character strings and then parsing the strings with the Mid. Or, should I be reading the file as a series of 255 byte strings?

Thank you again.
Jerry [sig][/sig]

 

[Ruairi]

using a variable length string to read input from a file with fixed length lines won't cause any problems. If you try reading the file 255 bytes at a time you would have to specifically look for an end of line character. I would stick with using Line Input and extracting what you need out of the entire line. This is especially true since you said the fields are fixed length. It makes it very easy when you know ahead of time exactly how many times you will need to call Mid and what parameters to pass it each time.
[sig]<p>Ruairi<br><a href=mailto:ruairi@logsoftware.com>ruairi@logsoftware.com</a><br>[/sig]

 

[grnfvr]

Jerry/Rauiri,

This one has troubled me lately....

Did you ever find out why the &quot;line input&quot; and &quot;open&quot; keywords aren't in any documentation.

Matt