Load file layout to import a table

[eerich]

Hello,

I have a flat file layout that was provided to me with about 135 fields. I am interested in loading the data from a text file and mapping it to the file layout. It is not a delimited file, but rather fixed length.

My question is whether there is a way to take the file layout and import it into the table specs without manually keying it in so that I can then apply it the specs to the file I need to import?

Any help is appreciated.

 

[genomon]

Open the text file with the FileSystem object. You can then parse it out using Mid() & InStr() functions. There are a ton of FAQs both in Tek-Tips and the web to help you.

"Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes."

 

[JerryKlmns]

eerich

System tables, like MSysIMEXSpecs & MSysIMEXColumns are read-only. Unless you create a query on them and play around with the records. A record for MSysIMEXSpecs table is piece of cake. The child records for MSysIMEXColumns is a bit difficult but doable if you understand the DataType values. So copy the structure in an Excel sheet, and start building the fields.

These are not all of them, but I had them handy
DataTypeValue DataType
4=Long Integer
5=Currency
7=Double
8=Date
10=Text

Then copy/paste append form excel to the query of MSysIMEXColumns.