DoCmd.TransferText how does Access determine default field types.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Im using "DoCmd.TransferText acImportDelim, , "testimport", FileName" - Some
fields generally contain numeric data but somtimes these numbers will also
contain a letter as the first character i.e.(Q457.778). The created table
"testimport" will assign field type "numeric" but if there was a letter in
that field no data is imported.
I can't use an import spec because many different types of .csv files
use this import.
Thanks
 
Basically, if a field contains only numeric values in the first x lines
of the text file, where x may well be 25, the Jet engine will create a
numeric field in the table it is importing to. If there is a value that
cannot be interpreted as a number, Jet will create a text field.

So one solution is to ensure that one line at the top of the text file
contains dummy data including non-numeric values where you want text
fields.

For more information, start with the Access help topic "Initializing the
Text Data Source Driver".
 
thanks- I tried adding non-numeric characters in the first line- no good-
I'll check the the "Text Data Source Driver" reference
I may have to open the file outside of access and rewrite it before it gets
imported
??
 
thanks- I tried adding non-numeric characters in the first line- no good-
Click to expand...

My fault - that works when importing from Excel, but the text file
driver works slightly differently.
I'll check the the "Text Data Source Driver" reference
I may have to open the file outside of access and rewrite it before it gets
imported
Click to expand...

More likely, write code that reads the file before importing it and
creates a table to suit it.
 
Here's something to try instead of mucking about with the contents of
the files.

Before importing each file, have your code create a minimal schema.ini
in the same folder, containing just

[XXX.XXX]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=0
CharacterSet=ANSI

where XXX.XXX is the name of the file. Then call TransferText as usual.
The MaxScanRows setting should make Jet read the entire file to decide
on field types.
 
Thanks
This looks like the best solution. I had tried editing registry settings
for my mstext35.dll driver ,MaxScanRows=25 => MaxScanRows=0 which does work.
But setting up a schema.ini file should be nicer.

Thanks again;
Jon Walsh

John Nurick said:
Here's something to try instead of mucking about with the contents of
the files.

Before importing each file, have your code create a minimal schema.ini
in the same folder, containing just

[XXX.XXX]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=0
CharacterSet=ANSI

where XXX.XXX is the name of the file. Then call TransferText as usual.
The MaxScanRows setting should make Jet read the entire file to decide
on field types.

thanks- I tried adding non-numeric characters in the first line- no good-
I'll check the the "Text Data Source Driver" reference
I may have to open the file outside of access and rewrite it before it gets
imported
??
Click to expand...
Click to expand...
 
Strange -
making the change in the registry works but using Schema.ini
doesn't ??
this is the schema.ini:
[z032505A.txt]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=0
CharacterSet=ANSI

jwalsh said:
Thanks
This looks like the best solution. I had tried editing registry settings
for my mstext35.dll driver ,MaxScanRows=25 => MaxScanRows=0 which does work.
But setting up a schema.ini file should be nicer.

Thanks again;
Jon Walsh

John Nurick said:
Here's something to try instead of mucking about with the contents of
the files.

Before importing each file, have your code create a minimal schema.ini
in the same folder, containing just

[XXX.XXX]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=0
CharacterSet=ANSI

where XXX.XXX is the name of the file. Then call TransferText as usual.
The MaxScanRows setting should make Jet read the entire file to decide
on field types.

thanks- I tried adding non-numeric characters in the first line- no good-
I'll check the the "Text Data Source Driver" reference
I may have to open the file outside of access and rewrite it before it gets
imported
??
:


Basically, if a field contains only numeric values in the first x lines
of the text file, where x may well be 25, the Jet engine will create a
numeric field in the table it is importing to. If there is a value that
cannot be interpreted as a number, Jet will create a text field.

So one solution is to ensure that one line at the top of the text file
contains dummy data including non-numeric values where you want text
fields.

For more information, start with the Access help topic "Initializing the
Text Data Source Driver".

On Tue, 29 Mar 2005 10:37:09 -0800, jwalsh

Im using "DoCmd.TransferText acImportDelim, , "testimport", FileName" - Some
fields generally contain numeric data but somtimes these numbers will also
contain a letter as the first character i.e.(Q457.778). The created table
"testimport" will assign field type "numeric" but if there was a letter in
that field no data is imported.
I can't use an import spec because many different types of .csv files
use this import.
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Strange. schema.ini worked when I tried it in a test database.
Conceivably there's a timing issue: is it possible that a delayed write
means that the TransferText call is coming before schema.ini has
actually been written?

Strange -
making the change in the registry works but using Schema.ini
doesn't ??
this is the schema.ini:
[z032505A.txt]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=0
CharacterSet=ANSI

jwalsh said:
Thanks
This looks like the best solution. I had tried editing registry settings
for my mstext35.dll driver ,MaxScanRows=25 => MaxScanRows=0 which does work.
But setting up a schema.ini file should be nicer.

Thanks again;
Jon Walsh

John Nurick said:
Here's something to try instead of mucking about with the contents of
the files.

Before importing each file, have your code create a minimal schema.ini
in the same folder, containing just

[XXX.XXX]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=0
CharacterSet=ANSI

where XXX.XXX is the name of the file. Then call TransferText as usual.
The MaxScanRows setting should make Jet read the entire file to decide
on field types.

On Tue, 29 Mar 2005 14:49:01 -0800, jwalsh

thanks- I tried adding non-numeric characters in the first line- no good-
I'll check the the "Text Data Source Driver" reference
I may have to open the file outside of access and rewrite it before it gets
imported
??
:


Basically, if a field contains only numeric values in the first x lines
of the text file, where x may well be 25, the Jet engine will create a
numeric field in the table it is importing to. If there is a value that
cannot be interpreted as a number, Jet will create a text field.

So one solution is to ensure that one line at the top of the text file
contains dummy data including non-numeric values where you want text
fields.

For more information, start with the Access help topic "Initializing the
Text Data Source Driver".

On Tue, 29 Mar 2005 10:37:09 -0800, jwalsh

Im using "DoCmd.TransferText acImportDelim, , "testimport", FileName" - Some
fields generally contain numeric data but somtimes these numbers will also
contain a letter as the first character i.e.(Q457.778). The created table
"testimport" will assign field type "numeric" but if there was a letter in
that field no data is imported.
I can't use an import spec because many different types of .csv files
use this import.
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top