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

Posted on 2008-11-14
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.

Excel data = 00055239
Import to Access = 55239
Question by:FredFive
    LVL 4

    Accepted Solution

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

    Author Comment

    The Excel data does not have appostrophes.  Both Excel and Access fields are defined as text.

    Excel data = 00055239
    Import to Access = 55239
    LVL 74

    Assisted Solution

    by:Jeffrey Coachman

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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now