importing csv files with transfertext questions

  • Thread starter Thread starter Erwin Kalvelagen
  • Start date Start date
E

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
 
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.
 
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



John said:
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
----------------------------------------------------------------
Click to expand...
Click to expand...
 
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?
Click to expand...

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).
Click to expand...

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



John said:
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
----------------------------------------------------------------
Click to expand...
Click to expand...
Click to expand...
 
Back
Top