TransferSpreadsheet is messing up some of my data

[irethedo]

I am converting an excel 2007 spreadsheet into a table but I am seeing some of my part numbers getting messed up.

My code contains the following line:

DoCmd.TransferSpreadsheet acImport, 9, "NewData_tbl", In_File, True

and the spreadsheet contains the following:

[pre]Part# Desc Line
015968 StarTech PCIe Gigabit NIC 20
015968 StarTech PCIe Gigabit NIC 35
[/pre]
and it is dropping off the leading '0' on one number as shown :

[pre]Part# Desc Line
15968 StarTech PCIe Gigabit NIC 20
015968 StarTech PCIe Gigabit NIC 35[/pre]

I have tried the following SpreadsheetType options with no change in result:
[pre]acSpreadsheetTypeExcel7 5 Microsoft Excel 95 format
acSpreadsheetTypeExcel8 8 Microsoft Excel 97 format
acSpreadsheetTypeExcel9 8 Microsoft Excel 2000 format
acSpreadsheetTypeExcel12 9 Microsoft Excel 2010 format
acSpreadsheetTypeExcel12Xml 10 Microsoft Excel 2010 XML format[/pre]

Additional info:
The part number field in the table that I am creating (NewData_tbl) with the DoCmd.TransferSpreadsheet is a text field
with the following gernal parameters:

Field Size 255
Format @
Required No
Allow Zero length Yes
Indexed No
Unicode Compression No
IME Mode No Control
IME Sentence Mode None
Text Align General

Is this a bug or am I setting this up incorrectly?

BTW: I am using Microsoft Access Version 14.0.7181.5000 from the Microsoft Office Professional Plus 2010

Thanks

 

[SkipVought]

Hi,

The Part# in your workbook is a formatted NUMBER. It should rather be a STRING of numeric characters.

Faq68-6659.

So, to avoid problems you must CONVERT all (ALL) the NUMBERS in this column into TEXT, 1) in order to REALLY have leading zeros and 2) in order that each data value be of the same data type, that is TEXT. The easiest way to accomplish this is to use the TEXT() function...
[tt]
=TEXT(A2,"000000")
[/tt]
...in an empty column, COPY that column and PASTE SPECIAL--VALUES in your Part# column. Then DELETE the column with the formula.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Or you can simply issue an Update statement after the transfer:
[tt]
UPDATE NewData_tbl
SET [Part#] = Format([Part#], "000000")
[/tt]


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Darrylles]

Or, you can follow 'best-practice', and not define 'numbers' that you will NEVER perform maths on - as string.
(Even if you ARE creating the number via mathematical incrementation - convert it to string and store it as string).

ATB,

Darrylle