Import Specifications

[Guest]

What are some ways to create an import specification for fixed-width text
files, beside typing in the length of each field in the advanced dialog box?

I can make a table in access that includes all the necessary info, but how
do I use that as my specs? Or perhaps is there a way to use the structure of
an existing table as the specs for an import?

Thank you in advance for any help.

 

[John Nurick]

Working under program control it's generally easier, IMHO, to create a
schema.ini file rather than of an import specification. This article
shows how to create a schema.ini to match an existing table:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512

Schema.ini must be placed in the same folder as the file you're
importing, and must have a section headed with the exact filename.
Documentation is towards the end of the Help topic "Initializing the
Text Data Source Driver" (under Microsoft Jet SQL Reference). These
links may also be useful:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090
http://www.devx.com/tips/Tip/12566

If you want to create actual import specifications it's possible: write
code or execute queries to create the necessary records in the hidden
system tables MSysIMEXColumns and MSysIMEXSpecs.

 

[david epsom dot com dot au]

shows how to create a schema.ini to match an existing table:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512

or, you can use a Make Table query to write schema.ini

SELECT * INTO [text;database=q:\].tblau.txt FROM tblAu;

Actually, .[tblau#txt] would be more strictly correct,
but it seems that strictness is not enforced.

(david)