How to import an ascii file with a mix of layouts???

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

Guest

I receive an ascii file that has multiple fixed width layouts in it. At the
moment I am manually copying and pasting into other ascii files so the data
can be imported to an Access database. Can anyone suggest how I might
automate this process?? Here is an example of the flat file I receive: (Its a
wide file so word wrap makes this looks very bizzare. Each individual row in
this example starts with 040420051055, the next three characters indicate the
table the record belongs to- BIL or ITM in this example.)

040420051055BIL14530032541 04042005BEN 0.00 0.00 176.35 0.00001
F04042005B00100110:4132541 0.00
040420051055BIL14530129400 04042005BAN 0.00 0.00 93.30 0.00001
F04042005B00100110:4329400 0.00
040420051055BIL10230029399 04042005EEN 0.00 0.00 28.80 0.00001
F04042005B00100110:4429399 0.00
040420051055BIL10230129398 04042005EEN 0.00 0.00 79.20 0.00001
F04042005B00100110:4529398 0.00
040420051055ITM042649041136 12.000 4.800 0.0000145300
000001F 0.00 1 57.60FBB001001IS
040420051055ITM793893115196 6.000 2.750 0.0000145300
000001F 0.00 2 16.50FBB001001IS
040420051055ITM042649031410 12.000 6.000 0.0000145300
000001F 0.00 3 72.00FBB001001IS
040420051055ITM793893115196 11.000 2.750 0.0000145300
000001F 0.00 4 30.25FBB001001IS
040420051055ITM793893115196 6.000 2.750 0.0000145301
000001F 0.00 1 16.50FBB001001IS
040420051055ITM042649041136 16.000 4.800 0.0000145301
000001F 0.00 2 76.80FBB001001IS
040420051055ITM042649041136 6.000 4.800 0.0000102300
000001F 0.00 1 28.80FEB001001IS
040420051055ITM042649031410 10.000 6.000 0.0000102301
000001F 0.00 1 60.00FEB001001IS
040420051055ITM042649041136 4.000 4.800 0.0000102301
000001F 0.00 2 19.20FEB001001IS
040420051055BIL17920032541 04042005RBN 0.00 0.00 50.00 0.00001
F04042005B00100110:46001019 0.00

Any advise would be helpful. Thanks JMR
 
Can you import the text file into a table, and put each full line of text
into a memo field in the table (one record for each line), and then use
append queries to parse the data the way you need to use it?
 
Thanks for the quick response Ken. I think what I will do is to set up an
import spec for each record type (i.e. BIL or ITM), import the data into
transfer tables, run delete queries to remove the rows that do not meet the
spec and finally append the data to the appropriate tables. The text file is
from a handheld scanner and represents a one to many relationship between
Bills and Items.
 
PMFJI,

I'd think about writing code to split the file into two (or more) temp
files each containing only one type of record. Somthing like this
totally untested air code:

Dim lngInFile As Long, lngOutBIL As Long, lngOutITM As Long
Dim strRec

lngInFile = FreeFile()
Open foo For Input As #lngInfile
lngOutBIL = FreeFile()
Open BIL For Output As #lngOutBIL
lngOutITM = FreeFile()
Open ITM For Output As #lngOutITM

Do Until EOF(lngInFile)
Line Input strRec, #lngInfile
If strRec Like "something" Then
Print #lngOutBil, strRec
Else
Print #lngOutITM, strRec
End If
Loop

Close #lngInFile
Close #lngOutBIL
Close #lngOutITM

then import the two files using appropriate specs.
 
Back
Top