fixed width file import

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hallo. welcome to all :
i've got a question concerning a fixed width file import into the table
file contains three types of lines. line A - header, line C - data
line E - footer. i need to import all the C lines and one piece of data from
the A line into the additional column for each row of the table. as far as i know
it cannot be done by VBA TransferText, by importing whole file - it should be done
by line after line loop processing. i'm kindly asking for some example or howto

greet
b
 
----- beeing wrote: ----

hallo. welcome to all :
i've got a question concerning a fixed width file import into the table
file contains three types of lines. line A - header, line C - data
line E - footer. i need to import all the C lines and one piece of data from
the A line into the additional column for each row of the table. as far as i know
it cannot be done by VBA TransferText, by importing whole file - it should be done
by line after line loop processing. i'm kindly asking for some example or howto

greet


H

Hopefully there is some way to recognise each line type in the file (eg first char is "A", "C" or "E"?
The way I've accompolished this in the past is to read in the lines from the file using a TextStream object, and use a query recordset based on two tables as a 'translation map'. The translation tables are a 'line type' table on the one side and 'field type' table on the many side. The most 3 important fields in the 'field type' table are two for the start byte and length of the 'fields' in the file (both integer), and one for the name of the destination field (in the table you want to put the data in)

The code to decipher the lines will then need to: get each line's first character (?); open the translation recordset, WHERE the line type = the first char, and the the destination recordset for adding records; loop thru field type records; extract field data using the Mid function on the line string; assign the resulting string to the corresponding destination field

If this isn't any help, sorry. I'm pretty sure I could dig out an actual working code example if you need it

Cheers, Ali
 
That is NOT a Fixed width file.
(The middle part is.)
If you got three files you could do it using TransferText on each of them.

Since you only have 1 file you have to assume the Header and fFooter records
will ALWAYS be the same structure.
Then you can code the import.

Sample code outline:

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
 
Back
Top