importing csv files with transfertext questions

[Erwin Kalvelagen]

Hi:

I am struggling a bit with transfertext. I have a small tool that should
import relative large amounts of data into Access. I currently use
the following algorithm:

1. write a csv file
format:
i1,i1,-6.56505736000000E+0001
i1,i2, 6.86533416000000E+0001
i1,i3, 1.00750712000000E+0001
....
if a string contains a blank I quote it as:
"a a","b b", 1.00750712000000E+0001
(strings are to up to 31 chars, last field is double
precision).
2. Read the file using:
AccessApplication.DoCmd.TransferText(acImportDelim,'',table,fln,false);
3. Rename fields to something more meaningful.


This works like a charm here in the US. However, I get into
troubles when the tool is used in other countries. I have
heard in Germany a table with one field is created.
When I change my language setting to German I get different
results: I get a table with three fields but the floating
point number is not read correctly. A user in Belgium said
it worked correctly on one machine but incorrectly on
another machine, and playing with language settings did
not help.

So here are my questions:
1. Should I write a CSV file using the windows locale
settings for decimal point etc? What should be the
field separator and the double quote? I am worried
about different behavior in Access even with the same
Windows locale -- can it be that Access uses a default
file format specification that may differ from the
global Windows settings? Can I interrogate that default
import format specification?

2. Or should I use an import specification? How do
I do that: just write a schema.ini in the directory
of the csv file? I am worried about:
http://support.microsoft.com/default.aspx?scid=kb;en-us;241477

3. Are there any better fast methods to get large data from memory
into Access?


Thanks, Erwin

 

[John Nurick]

Hi Erwin,

I'd try the following:

1) write a tab separated file rather than CSV. That avoids any problems with
comma vs semi-colon as list separators, and also saves the trouble of
quoting the text fields. (If there may be tab characters in the data, then
use another separator).

2) create a schema.ini file in the same folder, specifying among other
things:

Format=TabDelimited
DecimalSymbol=.

Schema.ini is documented in the Access help article "Initializing the Text
and HTML Data Source Driver" (or similar name).

3) import using either TransferText or by building and executing a SQL
statement using the
FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\My
documents;].[Contacts#txt];
syntax. Regardless of the KB241477 issue, the latter has the advantage that
you can alias the field names at the same time.

 

[Erwin Kalvelagen]

Thanks for your input. Indeed the Text Driver seems to work
fine with a generated schema.ini. So at least I have a reasonable
workaround.

Wrt to TransferText, I could not get TransferText to read
the schema.ini file. I suspect this is related to KB241477.
So the following questions remain:

1. How should a CSV file look for different language settings,
so I can use the default format specification? E.g. is the comma
the same as the list separator symbol?

2. Can I generate format specifications for TransferText
programmatically? (I.e. not using the import wizard, but
from a program).

Thanks, Erwin

Hi Erwin,

I'd try the following:

1) write a tab separated file rather than CSV. That avoids any problems with
comma vs semi-colon as list separators, and also saves the trouble of
quoting the text fields. (If there may be tab characters in the data, then
use another separator).

2) create a schema.ini file in the same folder, specifying among other
things:

Format=TabDelimited
DecimalSymbol=.

Schema.ini is documented in the Access help article "Initializing the Text
and HTML Data Source Driver" (or similar name).

3) import using either TransferText or by building and executing a SQL
statement using the
FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\My
documents;].[Contacts#txt];
syntax. Regardless of the KB241477 issue, the latter has the advantage that
you can alias the field names at the same time.

Hi:

I am struggling a bit with transfertext. I have a small tool that should
import relative large amounts of data into Access. I currently use
the following algorithm:

1. write a csv file
format:
i1,i1,-6.56505736000000E+0001
i1,i2, 6.86533416000000E+0001
i1,i3, 1.00750712000000E+0001
....
if a string contains a blank I quote it as:
"a a","b b", 1.00750712000000E+0001
(strings are to up to 31 chars, last field is double
precision).
2. Read the file using:

AccessApplication.DoCmd.TransferText(acImportDelim,'',table,fln,false);
3. Rename fields to something more meaningful.


This works like a charm here in the US. However, I get into
troubles when the tool is used in other countries. I have
heard in Germany a table with one field is created.
When I change my language setting to German I get different
results: I get a table with three fields but the floating
point number is not read correctly. A user in Belgium said
it worked correctly on one machine but incorrectly on
another machine, and playing with language settings did
not help.

So here are my questions:
1. Should I write a CSV file using the windows locale
settings for decimal point etc? What should be the
field separator and the double quote? I am worried
about different behavior in Access even with the same
Windows locale -- can it be that Access uses a default
file format specification that may differ from the
global Windows settings? Can I interrogate that default
import format specification?

2. Or should I use an import specification? How do
I do that: just write a schema.ini in the directory
of the csv file? I am worried about:
http://support.microsoft.com/default.aspx?scid=kb;en-us;241477

3. Are there any better fast methods to get large data from memory
into Access?


Thanks, Erwin

----------------------------------------------------------------
Erwin Kalvelagen
GAMS Development Corp., http://www.gams.com
(e-mail address removed), http://www.gams.com/~erwin
----------------------------------------------------------------

 

[John Nurick]

Thanks for your input. Indeed the Text Driver seems to work
fine with a generated schema.ini. So at least I have a reasonable
workaround.

Wrt to TransferText, I could not get TransferText to read
the schema.ini file. I suspect this is related to KB241477.
So the following questions remain:

1. How should a CSV file look for different language settings,
so I can use the default format specification? E.g. is the comma
the same as the list separator symbol?

I believe (but haven't tested it properly) that if you use
Format=CSVDelimited
Jet will use the list separator from the Windows Control Panel Regional
settings, and that if you want to use a comma regardless of the regional
settings you should use
Format=Delimited(,)

2. Can I generate format specifications for TransferText
programmatically? (I.e. not using the import wizard, but
from a program).

The import and export specifications are stored in the database's hidden
system tables and therefore can be hacked, but as far as I know the
details are not documented.

Thanks, Erwin

Hi Erwin,

I'd try the following:

1) write a tab separated file rather than CSV. That avoids any problems with
comma vs semi-colon as list separators, and also saves the trouble of
quoting the text fields. (If there may be tab characters in the data, then
use another separator).

2) create a schema.ini file in the same folder, specifying among other
things:

Format=TabDelimited
DecimalSymbol=.

Schema.ini is documented in the Access help article "Initializing the Text
and HTML Data Source Driver" (or similar name).

3) import using either TransferText or by building and executing a SQL
statement using the
FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\My
documents;].[Contacts#txt];
syntax. Regardless of the KB241477 issue, the latter has the advantage that
you can alias the field names at the same time.

Hi:

I am struggling a bit with transfertext. I have a small tool that should
import relative large amounts of data into Access. I currently use
the following algorithm:

1. write a csv file
format:
i1,i1,-6.56505736000000E+0001
i1,i2, 6.86533416000000E+0001
i1,i3, 1.00750712000000E+0001
....
if a string contains a blank I quote it as:
"a a","b b", 1.00750712000000E+0001
(strings are to up to 31 chars, last field is double
precision).
2. Read the file using:

AccessApplication.DoCmd.TransferText(acImportDelim,'',table,fln,false);
3. Rename fields to something more meaningful.


This works like a charm here in the US. However, I get into
troubles when the tool is used in other countries. I have
heard in Germany a table with one field is created.
When I change my language setting to German I get different
results: I get a table with three fields but the floating
point number is not read correctly. A user in Belgium said
it worked correctly on one machine but incorrectly on
another machine, and playing with language settings did
not help.

So here are my questions:
1. Should I write a CSV file using the windows locale
settings for decimal point etc? What should be the
field separator and the double quote? I am worried
about different behavior in Access even with the same
Windows locale -- can it be that Access uses a default
file format specification that may differ from the
global Windows settings? Can I interrogate that default
import format specification?

2. Or should I use an import specification? How do
I do that: just write a schema.ini in the directory
of the csv file? I am worried about:
http://support.microsoft.com/default.aspx?scid=kb;en-us;241477

3. Are there any better fast methods to get large data from memory
into Access?


Thanks, Erwin

----------------------------------------------------------------
Erwin Kalvelagen
GAMS Development Corp., http://www.gams.com
(e-mail address removed), http://www.gams.com/~erwin
----------------------------------------------------------------