Import Specifications

[keith]

Hello,
I need to use a txt file in a flat, ascii format.
There are hundreds variables with names, datatype, start
positions, and width specified in an access table or an
excel sheet. It's too much to retype everything into the
Access form that describes the import specification.

How can I import the input specification into the place in
Access where the import specification is stored? An
answer using VBA is ok, if that is the only way.

Thanks
Keith

 

[John Nurick]

Hi Keith,

IMO it's easier to do this by creating a SCHEMA.INI file containing the
field specifications, inthe same folder as the text file. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp
for the basic documentation, or search MSDN for "schema.ini" for more
information.

You can build the bulk of SCHEMA.INI by creating a query that returns a
row for each field, e.g.
Col1=CustomerNumber Text Width 10
Col2=CustomerName Text Width 30
then export this to a text file and add the other information manually.

However, you mention "hundreds" of fields. Remember that an Access table
can't have more than 255 fields, and that a record can't contain more
than about 2000 characters (excluding the contents of memo fields).

If the file you're importing has more fields or characters than that (I
once received a questionnaire survey data file with more than 1700
fields) you'll need to re-structure the text file first. I have a script
that can help with that if you need it.

 

[keith]

Hi John,

Thanks very much. I'll look up the references you
suggestied and study your comments. I have not received
the files yet, but documentation I've received suggests
one of them has a record length greater than 4,000. Guess
I have some trimming to do, but That will be fairly simple
using Visual Basic.

I appreciate your comments.

Have a great day,

Keith

-----Original Message-----
Hi Keith,

IMO it's easier to do this by creating a SCHEMA.INI file containing the
field specifications, inthe same folder as the text file. See

http://msdn.microsoft.com/library/default.asp?

url=/library/en-us/odbc/htm/odbcjetsdk_98.asp
for the basic documentation, or search MSDN for "schema.ini" for more
information.

You can build the bulk of SCHEMA.INI by creating a query that returns a
row for each field, e.g.
Col1=CustomerNumber Text Width 10
Col2=CustomerName Text Width 30
then export this to a text file and add the other information manually.

However, you mention "hundreds" of fields. Remember that an Access table
can't have more than 255 fields, and that a record can't contain more
than about 2000 characters (excluding the contents of memo fields).

If the file you're importing has more fields or characters than that (I
once received a questionnaire survey data file with more than 1700
fields) you'll need to re-structure the text file first. I have a script
that can help with that if you need it.

Hello,
I need to use a txt file in a flat, ascii format.
There are hundreds variables with names, datatype, start
positions, and width specified in an access table or an
excel sheet. It's too much to retype everything into the
Access form that describes the import specification.

How can I import the input specification into the place in
Access where the import specification is stored? An
answer using VBA is ok, if that is the only way.

Thanks
Keith

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

Hi Keith,

As far as I can remember the length of the lines in the fixed-width text
file doesn't matter (within reason), only the actual data they contain.
E.g. if you have a 4000-character wide file containing 16 250 character
text fields, that will import OK provided the total of all the fields in
a single record never exceeds 2000.

Hi John,

Thanks very much. I'll look up the references you
suggestied and study your comments. I have not received
the files yet, but documentation I've received suggests
one of them has a record length greater than 4,000. Guess
I have some trimming to do, but That will be fairly simple
using Visual Basic.

I appreciate your comments.

Have a great day,

Keith

-----Original Message-----
Hi Keith,

IMO it's easier to do this by creating a SCHEMA.INI file containing the
field specifications, inthe same folder as the text file. See

http://msdn.microsoft.com/library/default.asp?

url=/library/en-us/odbc/htm/odbcjetsdk_98.asp
for the basic documentation, or search MSDN for "schema.ini" for more
information.

You can build the bulk of SCHEMA.INI by creating a query that returns a
row for each field, e.g.
Col1=CustomerNumber Text Width 10
Col2=CustomerName Text Width 30
then export this to a text file and add the other information manually.

However, you mention "hundreds" of fields. Remember that an Access table
can't have more than 255 fields, and that a record can't contain more
than about 2000 characters (excluding the contents of memo fields).

If the file you're importing has more fields or characters than that (I
once received a questionnaire survey data file with more than 1700
fields) you'll need to re-structure the text file first. I have a script
that can help with that if you need it.

Hello,
I need to use a txt file in a flat, ascii format.
There are hundreds variables with names, datatype, start
positions, and width specified in an access table or an
excel sheet. It's too much to retype everything into the
Access form that describes the import specification.

How can I import the input specification into the place in
Access where the import specification is stored? An
answer using VBA is ok, if that is the only way.

Thanks
Keith

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.