Import from .txt files

[aojonesoa]

I automated the import of text, then found out that the field names from the text files I'm importing from do not always have the columns in the same order. To make matters worse, not all the columns are there all the time.

At this point, I've decided the best thing to do is to import the header with the rest of the data in the first record, then use this record to determine which columns are which, then somehow move the data (without the header) to another table to the appropriate field.

Does anyone know how to do this?

Thanks a million!

 

[sha76]

Are you appending to an existing table? If so then like you say import into a new table, but keep the header as field names, so you can use an append query to put them where you want them. If not then I'd think about what you're doing with the data & if you need to move them at all?

Sharon

 

[aojonesoa]

At this point I could choose to append to an existing table or to a new one. I have been trying a new one lately where all the fields are named "Field1, Field2, etc", but even then, how do I handle the data when its in this table. Ultimately I have to get the data in known field names so I can use it.

How would you use an append query to put them where you want them if the first record contains the field names and the rest is the data?

 

[sha76]

Sorry, of course it's only importing spreadsheets that lets you pick up the headers. That may be one way of doing it - to put it in a spreadsheet first & import from that, although it hardly a neat solution. Not sure what it's possible to do if you import directly through code, may be an option.

Sharon

 

[sha76]

Sorry, of course it's only importing spreadsheets that lets you pick up the headers. That may be one way of doing it - to put it in a spreadsheet first & import from that, although it hardly a neat solution. Not sure what it's possible to do if you import directly through code, may be an option.

Sharon

 

[aojonesoa]

That is a good idea though, I'll try to import into and Excel file first, I'll let you know how it goes.

Thanks,

 

[lameid]

Stay away from Excel!

My recommendation for people who want to import from Excel, export to text and then import. Excel imports are not forgiving about using correct datatypes.

Try linking the text marking the check box that says first row contains field names. Save the import specification namming it something useful and note the name of the import specification. You can use the transfertext to put the data in a table. With any luck, that should work regardless of the fields sent, although you must have all possible fields in your table. If you have problems you could always just link the text and then append all the fields in a query (using tablename.*). Then if you keep using the same table name, the query will always work (unless a new field is used).