DoCmd.TransferText how does Access determine default field types.

[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

 

[John Nurick]

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

 

[Guest]

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

 

[John Nurick]

thanks- I tried adding non-numeric characters in the first line- no good-

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

More likely, write code that reads the file before importing it and
creates a table to suit it.

 

[John Nurick]

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.

 

[Guest]

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

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

 

[Guest]

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

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

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

 

[John Nurick]

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

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

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