Importing selected data from a fixed length text file.

[Juster1954]

I have a text file that contains three types of fixed length records, each type containing diffrent field information and sizes. The first field has a type indicator (i.e. FH=File Header, FC=Trans Type & FD=Trans Detail). I just want to import the records where the first 2 characters of the record are equal to FH. Is there any easy way to select records from a text file on importation??? Any help would be much appreciated.

 

[Joe Fallon]

Not using the wizard!
That is just for a single "table" of data.

You will have to use code to open the file and read one row at time.
Then based on that row you add the data to the correct recordset.
You may need 2 or 3 recordsets depending on what you want to do.

Here is the general idea:

Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file and assign it to a String
variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub


--
Joe Fallon
Access MVP

I have a text file that contains three types of fixed length records, each

type containing diffrent field information and sizes. The first field has a
type indicator (i.e. FH=File Header, FC=Trans Type & FD=Trans Detail). I
just want to import the records where the first 2 characters of the record
are equal to FH. Is there any easy way to select records from a text file
on importation??? Any help would be much appreciated.

 

[John Nurick]

Another approach would be to obtain a "grep" utility, e.g. by
downloading the Gnu utilities from http://unxutils.sourceforge.net/

Then use a command like this at a command prompt to run grep and copy
just the "FH" records into another file:

grep "^FH" "D:\Folder\My File.txt" > "D:\Folder\FH Only.txt"

The new file will contain jsut the one type of record and can therefore
be imported by Access's text import wizard.

I have a text file that contains three types of fixed length records,

each type containing diffrent field information and sizes. The first
field has a type indicator (i.e. FH=File Header, FC=Trans Type &
FD=Trans Detail). I just want to import the records where the first 2
characters of the record are equal to FH. Is there any easy way to
select records from a text file on importation??? Any help would be
much appreciated.