• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 948
  • Last Modified:

Importing data from Excel 2007 to Access 2007 - dropping leading zeros in text field

I'm trying to import Excel 2007 data into Access 2007 - one of the fields requires leading zeros.  The import data shows the leading zeros in a text field.  Access is also defined as a text field and is dropping the leading zeros.  This process worked fine with Office 2003 software.

I have tried to format the text field in Access.  I can type in the leading zeros or do a replace which saves the leading zeros, however, Acess deletes the leading zeros when imported.

Example:
Excel data = 00055239
Import to Access = 55239
0
FredFive
Asked:
FredFive
2 Solutions
 
OdeMonkeyCommented:
I have had this problem going from Access to Excel, but not the other way.  Is your data in Excel preceeded by an "'" (apostrophe)?  In the import specification, is the data column defined as text?

You could fix this with an update query after the import with this function:  field1=right("0000000" & field1,8).  This would create the string "000000055239", then keep only the rightmost 8 characters leaving you with "00055239".
0
 
FredFiveAuthor Commented:
The Excel data does not have appostrophes.  Both Excel and Access fields are defined as text.

Example:
Excel data = 00055239
Import to Access = 55239
0
 
Jeffrey CoachmanMIS LiasonCommented:
FredFive,

You never stated how you areactually importing these files.

This works fine for me with no issuse at all.
I am simply using the Import wizard in Access 2007.

I am using *Native* Access 2007 and Excel 2007 files here (I had to change the extensions to post them here.)

JeffCoachman

Access--EEQ-23906504-2007Leading.zip
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now