INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

TransferSpreadsheet is messing up some of my data

TransferSpreadsheet is messing up some of my data

(OP)
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:

CODE -->

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

and the spreadsheet contains the following:

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

Part#	Desc				Line
15968	StarTech PCIe Gigabit NIC	20
015968	StarTech PCIe Gigabit NIC	35 


I have tried the following SpreadsheetType options with no change in result:

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 

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

RE: TransferSpreadsheet is messing up some of my data

Hi,

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

FAQ68-6659: When is a NUMBER not a NUMBER?.

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

=TEXT(A2,"000000")

...in an empty column, COPY that column and PASTE SPECIAL--VALUES in your Part# column. Then DELETE the column with the formula.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: TransferSpreadsheet is messing up some of my data

Or you can simply issue an Update statement after the transfer:

UPDATE NewData_tbl
SET [Part#] = Format([Part#], "000000")

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: TransferSpreadsheet is messing up some of my data

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close