Import using multiple specs depending on field value

[samyers]

I don't even know if this is possible in Access, but I'll throw it out there.

I have a text file containing an extract from a large mainframe db. Each record contains one 400 character field, and represents one of 3 types of data:

CUSSMITH99999123MAINSTNEWYORKNY10011 is a customer
SAL9999920070717PARTNO15EA6009000SHP is a sale
INVPARTNO600EASUPPLIERXREFFILLER9999 is an inventory record relating to the sale

Each type of data needs a different spec to parse it properly. I want all the CUS records to use spec Customer and go into tblCustomers; all the SAL records to use spec Sales and go into tblSales, etc. I have specs created for each type of record. So far, the only way I have found to import and parse this data is to do it by hand, once for each type of record, then go into each table and delete teh records that don't belong.

What I would like to do is step through the text file, read the first 3 chars, and use that value to determine which spec and output file to use.

Can this be done?

Thanks

 

[Remou]

Why not read in the file using the FileSystemObject and write out three files, which can be imported?

Set fs = CreateObject("Scripting.FileSystemObject")
Set o1 = fs.CreateTextFile("cus.txt")
Set o2 = fs.CreateTextFile("sal.txt")
Set o3 = fs.CreateTextFile("inv.txt")
 
Set f = fs.OpenTextFile("import.txt")

Do While f.AtEndOfStream <> True
  a = f.Readline
  Select Case Left(a,3)
    Case "CUS"
      o1.write a
    Case "SAL"
      o2.write a
    Case "INV"
      o3.write a
    End Select
Loop
o1.Close
o2.Close
o3.Close
'Then import

 

[samyers]

Great idea - I hadn't thought of that! Thanks!