[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-14
4
Medium Priority
?
939 Views
Last Modified: 2013-11-29
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
Comment
Question by:FredFive
3 Comments
 
LVL 4

Accepted Solution

by:
OdeMonkey earned 252 total points
ID: 22964717
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
 

Author Comment

by:FredFive
ID: 22975761
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 248 total points
ID: 23417957
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question