How to import a flat file (not delimited)

[Matt S]

I need to create a script to import a file into my database, that is not tab delimeted (a flat file). Its records are defined with a return, however each field is a designated number of characters long and not separated.

Eg:

field 1 = 9 characters

field 2 = 30 characters

field 3 = 10 characters etc.

There are no tabs, commas or semicolons separating the fields.

Thanks for any help!! Much appreciated.

[comment]

Is this a one-time conversion, or do you have to do this periodically?

[Matt S]

Quite regularly (several times a day!) so it would be great to script it and be able to click a button and voila!!

[comment]

Import into a temp table (with the entire record mapped to a single field). In this table, define calculation fields to parse out field data, e.g.

cField1 = Left ( ImportedField ; 9 )

cField2 = Middle ( ImportedField ; 10 ; 30 )

and so on. Now import the temp records into your real table, mapping the calc fields to your real fields.

You could also import directly into your real table, and use auto-entered calculations in your real fields - but that "dirties up" your table with redundant data and can interfere with normal operations (for instance, if you need another auto-entered calculation when a new record is created manually).