TransferSpreadsheet: bad data type conversion

[Guest]

Access 2003 is doing something odd when I use TransferSpreadsheet. A field
called [Order number], when it which contains only numerals is being
converted to numbers in scientific notation.

The spreadsheet has had the NumberFormat of the cells set to Text. The
resulting field in the imported table is nvarchar 255. How do I get Access to
behave itself?

 

[Guest]

I should have offered an example:

the datum '5000125524' in the spreadsheet has NumberFormat = Text;

after import to an Access table (using DoCmd.TransferSpreadsheet), it has
become '5.00013e+009'.

So, it seems that it is being converted from Excel Text to floating point
and then to Access nvarchar. I need to suppress the intermediate conversion,
somewhow.

 

[John W. Vinson]

I should have offered an example:

the datum '5000125524' in the spreadsheet has NumberFormat = Text;

after import to an Access table (using DoCmd.TransferSpreadsheet), it has
become '5.00013e+009'.

So, it seems that it is being converted from Excel Text to floating point
and then to Access nvarchar. I need to suppress the intermediate conversion,
somewhow.

The problem is that Access has "strong data types" - a field is defined from
the beginning as being a Text field, a Number field, or something else.

Excel does not have strong datatypes. The format of a cell in a spreadsheet is
*just* a display setting: the cell is not stored specifically as one datatype
or another. When data is imported from Excel into Access, the program must
make a best guess at the desired datatype, based on the values of the first
few rows of the spreadsheet. The datum '5000125524' does indeed make sense as
a number, five billion odd; it's being stored as a Double Float number since
it's too big to fit in a Long Integer; the scientific notation is not part of
how the number is stored, but rather a display option which could be easily
changed.

One solution is to put a dummy row at the top of the spreadsheet containing an
unambiguously text value. If this column had a row at the beginning with
'TENBYTETXT' in it, Access would (I hope!) not try to convert to number.

John W. Vinson [MVP]

 

[Guest]

Thanks, John.

Is there no analog to the schema.ini specification used with the
TransferText action?

 

[John W. Vinson]

Thanks, John.

Is there no analog to the schema.ini specification used with the
TransferText action?

Not that I know of - which does not mean there isn't!

John W. Vinson [MVP]