Importing a text file with a schema.ini file

[Conan Kelly]

Hello all,

How do I import a text file using a schema.ini file (either working directly
in Access or in VBA).

The help files say you can use a saved specification file or a schema.ini
file, but it doesn't specifically tell you how to use a schema.ini file or
give examples.

using VBA, I've tried:

DoCmd.TransferText , _
, _
"acct_retained_bal_122006_revised", _
"X:\American
Savings\2006_12_Data\acct_retained_bal_122006_revised.txt"

AND

DoCmd.TransferText , _
"X:\American Savings\2006_12_Data\schema.ini", _
"acct_retained_bal_122006_revised", _
"X:\American
Savings\2006_12_Data\acct_retained_bal_122006_revised.txt"

Using the second version, I received an error message:
http://home.att.net/~ctbarbarin/import_spec_error.jpg

Is the SpecificationName argument (2nd argument) of the TransferText method
only used for Saved Specifications within the DB? If so, how do I use the
schema.ini file?

Thanks for any help anyone can provide,

Conan Kelly

 

['69 Camaro]

Hi, Conan.

Is the SpecificationName argument (2nd argument) of the TransferText
method only used for Saved Specifications within the DB?
Yes.

how do I use the schema.ini file?

Place it in the same directory as the text file you wish to import. Jet
will read it, but only if it is named Schema.ini, and it's in the correct
directory, and has a section with the name of the text file enclosed in
brackets, i.e., [ImportData.txt]

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Conan Kelly]

69 Camaro (<--- sweet ride!),

Thanks for the feed back.

Place it in the same directory as the text file you wish to import. Jet
will read it, but only if it is named Schema.ini, and it's in the correct
directory, and has a section with the name of the text file enclosed in
brackets, i.e., [ImportData.txt]

It is in the same directory as the file I'm trying to import. is
"Schema.ini" and "schema.ini" 2 different things (case sensitive)? (doesn't
really matter, I've tried it both ways)

I also have the exact file name in brackets on the first line of the schema
file (copied & pasted from windows explorer).

It is a tab delimited file, so I have "Format=TabDelimited" on the third
line (well, 4th line when you count the text file section name).

Every time I import this (using VBA or directly from Access), it all comes
in to one column of memo data type.

One thing though, columns 4-61 need to be float data types. But the client
that provided the text file put a . (decimal/period/dot) as a place holder
for NULL's in each of these columns. Do you think that could be throwing my
import off so it won't use the schema file?

Thanks again for all of your help,

Conan

Hi, Conan.

Is the SpecificationName argument (2nd argument) of the TransferText
method only used for Saved Specifications within the DB?
Yes.

how do I use the schema.ini file?

Place it in the same directory as the text file you wish to import. Jet
will read it, but only if it is named Schema.ini, and it's in the correct
directory, and has a section with the name of the text file enclosed in
brackets, i.e., [ImportData.txt]

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Hello all,

How do I import a text file using a schema.ini file (either working
directly in Access or in VBA).

The help files say you can use a saved specification file or a schema.ini
file, but it doesn't specifically tell you how to use a schema.ini file
or give examples.

using VBA, I've tried:

DoCmd.TransferText , _
, _
"acct_retained_bal_122006_revised", _
"X:\American
Savings\2006_12_Data\acct_retained_bal_122006_revised.txt"

AND

DoCmd.TransferText , _
"X:\American Savings\2006_12_Data\schema.ini", _
"acct_retained_bal_122006_revised", _
"X:\American
Savings\2006_12_Data\acct_retained_bal_122006_revised.txt"

Using the second version, I received an error message:
http://home.att.net/~ctbarbarin/import_spec_error.jpg

Is the SpecificationName argument (2nd argument) of the TransferText
method only used for Saved Specifications within the DB? If so, how do I
use the schema.ini file?

Thanks for any help anyone can provide,

Conan Kelly

 

[Conan Kelly]

Oh yeah! I also meant to ask...If I have my schema file correct, in the
same dir as the text file, and with the text file's name correctly in
brackets within the shema file, then would my first version of my VBA code
correctly import the text file using the schema file (the version leaving
the 2nd argument completely blank)?

thanks again for all of the help,

Conan

Hi, Conan.

Is the SpecificationName argument (2nd argument) of the TransferText
method only used for Saved Specifications within the DB?
Yes.

how do I use the schema.ini file?

Place it in the same directory as the text file you wish to import. Jet
will read it, but only if it is named Schema.ini, and it's in the correct
directory, and has a section with the name of the text file enclosed in
brackets, i.e., [ImportData.txt]

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Hello all,

How do I import a text file using a schema.ini file (either working
directly in Access or in VBA).

The help files say you can use a saved specification file or a schema.ini
file, but it doesn't specifically tell you how to use a schema.ini file
or give examples.

using VBA, I've tried:

DoCmd.TransferText , _
, _
"acct_retained_bal_122006_revised", _
"X:\American
Savings\2006_12_Data\acct_retained_bal_122006_revised.txt"

AND

DoCmd.TransferText , _
"X:\American Savings\2006_12_Data\schema.ini", _
"acct_retained_bal_122006_revised", _
"X:\American
Savings\2006_12_Data\acct_retained_bal_122006_revised.txt"

Using the second version, I received an error message:
http://home.att.net/~ctbarbarin/import_spec_error.jpg

Is the SpecificationName argument (2nd argument) of the TransferText
method only used for Saved Specifications within the DB? If so, how do I
use the schema.ini file?

Thanks for any help anyone can provide,

Conan Kelly

 

['69 Camaro]

Hi, Conan.

is "Schema.ini" and "schema.ini" 2 different things (case sensitive)?

Windows NT 3.5 and 4.0 occasionally considered case sensitivity, but unless
you're using those versions of Windows, they're both the same file name.

I also have the exact file name in brackets on the first line of the
schema file (copied & pasted from windows explorer).
Good.

It is a tab delimited file, so I have "Format=TabDelimited" on the third
line (well, 4th line when you count the text file section name).
Good.

Every time I import this (using VBA or directly from Access), it all comes
in to one column of memo data type.

I never have that problem. I use SQL instead of VBA, though.

One thing though, columns 4-61 need to be float data types. But the
client that provided the text file put a . (decimal/period/dot) as a place
holder for NULL's in each of these columns. Do you think that could be
throwing my import off so it won't use the schema file?

I don't believe so. I just tested an import using a query and a Schema.ini
file in Access 2003 with a dot instead of a float value for a couple of
records in a text file, and they imported fine. My tests had some problems
when the Schema.ini file used ColNameHeader=False and there were headers in
the text file, so that needs to match.

In your case I'd recommend writing a query, instead of using TransferText.
I tested with TransferText and tab delimited, and it imported the file as a
single Text column.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

69 Camaro (<--- sweet ride!),

Thanks for the feed back.

Place it in the same directory as the text file you wish to import. Jet
will read it, but only if it is named Schema.ini, and it's in the correct
directory, and has a section with the name of the text file enclosed in
brackets, i.e., [ImportData.txt]

It is in the same directory as the file I'm trying to import. is
"Schema.ini" and "schema.ini" 2 different things (case sensitive)?
(doesn't really matter, I've tried it both ways)

I also have the exact file name in brackets on the first line of the
schema file (copied & pasted from windows explorer).

It is a tab delimited file, so I have "Format=TabDelimited" on the third
line (well, 4th line when you count the text file section name).

Every time I import this (using VBA or directly from Access), it all comes
in to one column of memo data type.

One thing though, columns 4-61 need to be float data types. But the
client that provided the text file put a . (decimal/period/dot) as a place
holder for NULL's in each of these columns. Do you think that could be
throwing my import off so it won't use the schema file?

Thanks again for all of your help,

Conan

Hi, Conan.

Is the SpecificationName argument (2nd argument) of the TransferText
method only used for Saved Specifications within the DB?
Yes.

how do I use the schema.ini file?

Place it in the same directory as the text file you wish to import. Jet
will read it, but only if it is named Schema.ini, and it's in the correct
directory, and has a section with the name of the text file enclosed in
brackets, i.e., [ImportData.txt]

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Hello all,

How do I import a text file using a schema.ini file (either working
directly in Access or in VBA).

The help files say you can use a saved specification file or a
schema.ini file, but it doesn't specifically tell you how to use a
schema.ini file or give examples.

using VBA, I've tried:

DoCmd.TransferText , _
, _
"acct_retained_bal_122006_revised", _
"X:\American
Savings\2006_12_Data\acct_retained_bal_122006_revised.txt"

AND

DoCmd.TransferText , _
"X:\American Savings\2006_12_Data\schema.ini", _
"acct_retained_bal_122006_revised", _
"X:\American
Savings\2006_12_Data\acct_retained_bal_122006_revised.txt"

Using the second version, I received an error message:
http://home.att.net/~ctbarbarin/import_spec_error.jpg

Is the SpecificationName argument (2nd argument) of the TransferText
method only used for Saved Specifications within the DB? If so, how do
I use the schema.ini file?

Thanks for any help anyone can provide,

Conan Kelly

 

['69 Camaro]

Hi, Conan.

If I have my schema file correct, in the same dir as the text file, and
with the text file's name correctly in brackets within the shema file,
then would my first version of my VBA code correctly import the text file
using the schema file (the version leaving the 2nd argument completely
blank)?

Your first version will import the data into a single column named F1.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Oh yeah! I also meant to ask...If I have my schema file correct, in the
same dir as the text file, and with the text file's name correctly in
brackets within the shema file, then would my first version of my VBA code
correctly import the text file using the schema file (the version leaving
the 2nd argument completely blank)?

thanks again for all of the help,

Conan

Hi, Conan.

Is the SpecificationName argument (2nd argument) of the TransferText
method only used for Saved Specifications within the DB?
Yes.

how do I use the schema.ini file?

Place it in the same directory as the text file you wish to import. Jet
will read it, but only if it is named Schema.ini, and it's in the correct
directory, and has a section with the name of the text file enclosed in
brackets, i.e., [ImportData.txt]

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Hello all,

How do I import a text file using a schema.ini file (either working
directly in Access or in VBA).

The help files say you can use a saved specification file or a
schema.ini file, but it doesn't specifically tell you how to use a
schema.ini file or give examples.

using VBA, I've tried:

DoCmd.TransferText , _
, _
"acct_retained_bal_122006_revised", _
"X:\American
Savings\2006_12_Data\acct_retained_bal_122006_revised.txt"

AND

DoCmd.TransferText , _
"X:\American Savings\2006_12_Data\schema.ini", _
"acct_retained_bal_122006_revised", _
"X:\American
Savings\2006_12_Data\acct_retained_bal_122006_revised.txt"

Using the second version, I received an error message:
http://home.att.net/~ctbarbarin/import_spec_error.jpg

Is the SpecificationName argument (2nd argument) of the TransferText
method only used for Saved Specifications within the DB? If so, how do
I use the schema.ini file?

Thanks for any help anyone can provide,

Conan Kelly

 

[Conan Kelly]

OK! Well that is my problem then.

But using File>Get External Data>Import... does not use the Schema.ini file
either. It just uses the default column settings.

How would I go about importing using the Schema.ini file? You mentioned SQL
script. Can you provide a sample please? Can it be done with VBA?

Thanks again for all of your help,

Conan

Hi, Conan.

If I have my schema file correct, in the same dir as the text file, and
with the text file's name correctly in brackets within the shema file,
then would my first version of my VBA code correctly import the text file
using the schema file (the version leaving the 2nd argument completely
blank)?

Your first version will import the data into a single column named F1.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Oh yeah! I also meant to ask...If I have my schema file correct, in the
same dir as the text file, and with the text file's name correctly in
brackets within the shema file, then would my first version of my VBA
code correctly import the text file using the schema file (the version
leaving the 2nd argument completely blank)?

thanks again for all of the help,

Conan

Hi, Conan.

Is the SpecificationName argument (2nd argument) of the TransferText
method only used for Saved Specifications within the DB?

Yes.

how do I use the schema.ini file?

Place it in the same directory as the text file you wish to import. Jet
will read it, but only if it is named Schema.ini, and it's in the
correct directory, and has a section with the name of the text file
enclosed in brackets, i.e., [ImportData.txt]

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


Hello all,

How do I import a text file using a schema.ini file (either working
directly in Access or in VBA).

The help files say you can use a saved specification file or a
schema.ini file, but it doesn't specifically tell you how to use a
schema.ini file or give examples.

using VBA, I've tried:

DoCmd.TransferText , _
, _
"acct_retained_bal_122006_revised", _
"X:\American
Savings\2006_12_Data\acct_retained_bal_122006_revised.txt"

AND

DoCmd.TransferText , _
"X:\American Savings\2006_12_Data\schema.ini", _
"acct_retained_bal_122006_revised", _
"X:\American
Savings\2006_12_Data\acct_retained_bal_122006_revised.txt"

Using the second version, I received an error message:
http://home.att.net/~ctbarbarin/import_spec_error.jpg

Is the SpecificationName argument (2nd argument) of the TransferText
method only used for Saved Specifications within the DB? If so, how do
I use the schema.ini file?

Thanks for any help anyone can provide,

Conan Kelly