Data being lost when imported from Excel to Access tables.

Discussion in 'Microsoft Access Database Table Design' started by Guest, Mar 17, 2004.

  1. Guest

    Guest Guest

    Hello,

    I attempted posting this in Importing, Exporting, Linking.
    But this may be a question more suited for this thread.

    My problem is that when I attempt to import data from an
    Excel spread sheet into an Access 2003 table. The first
    zeros that I have in a zip code field are not getting
    carried over to the access table.

    Now I would like to correct this in one shot. I tried
    setting up a custom imput mask such as \00000\-00000

    This added a zero to the front of all the zip codes.
    Not all of my zip code entries start with zero.

    I just want to copy over what is actually in my excel
    spread sheet.

    And perhaps latter automatically add all zeros for the
    last five digits.

    For right now how ever. All that there is are five
    digits.

    Some start with zero. Some do not.

    I would really appreaciate some help on this. I keep
    coming back to this issue over the past couple of weeks.

    Currently I am no closer to figuring this out from my
    refrence manual.

    Making changes on the spread sheet side realy are not an
    option. This is something if possible that I need to
    correct with in Access.

    Thank you,

    Casey,
     
    Guest, Mar 17, 2004
    #1
    1. Advertisements

  2. Guest

    John Vinson Guest

    On Wed, 17 Mar 2004 13:25:49 -0800,
    <> wrote:

    >My problem is that when I attempt to import data from an
    >Excel spread sheet into an Access 2003 table. The first
    >zeros that I have in a zip code field are not getting
    >carried over to the access table.


    Access has to guess the appropriate datatype when it imports data from
    Excel. Since zipcodes *appear* to be numeric data, it will guess that
    the field is of Number type and therefore truncate leading zeros.

    Either create the table empty in Access, with a Text datatype for the
    zip code, and use File... Get External Data... Link to connect to
    Excel in order to run an Append query; or put a "dummy" row at the top
    of the spreadsheet with a text value in the zipcode field (such as
    "Zip" or "Text!"). You can then delete the dummy record.

    John W. Vinson[MVP]
    Come for live chats every Tuesday and Thursday
    http://go.compuserve.com/msdevapps?loc=us&access=public
     
    John Vinson, Mar 17, 2004
    #2
    1. Advertisements

  3. Guest

    Casey Guest

    Hello John,

    When I imported the data from Excel I did it as you stated
    in the first part of your responce. Still the zero's are
    missing. The other option with editing the .xls files is
    something i'm not going to be able to do. We have a
    process that opens the excel file as it is being created
    and then imports the data to the Access database. I cannot
    interupt the process this is something that is high volume.

    Casey,

    And Access still trunkated

    >-----Original Message-----
    >On Wed, 17 Mar 2004 13:25:49 -0800,
    ><> wrote:
    >
    >>My problem is that when I attempt to import data from an
    >>Excel spread sheet into an Access 2003 table. The first
    >>zeros that I have in a zip code field are not getting
    >>carried over to the access table.

    >
    >Access has to guess the appropriate datatype when it

    imports data from
    >Excel. Since zipcodes *appear* to be numeric data, it

    will guess that
    >the field is of Number type and therefore truncate

    leading zeros.
    >
    >Either create the table empty in Access, with a Text

    datatype for the
    >zip code, and use File... Get External Data... Link to

    connect to
    >Excel in order to run an Append query; or put a "dummy"

    row at the top
    >of the spreadsheet with a text value in the zipcode field

    (such as
    >"Zip" or "Text!"). You can then delete the dummy record.
    >
    > John W. Vinson[MVP]
    > Come for live chats every Tuesday and Thursday
    >http://go.compuserve.com/msdevapps?loc=us&access=public
    >.
    >
     
    Casey, Mar 18, 2004
    #3
  4. Guest

    John Vinson Guest

    On Wed, 17 Mar 2004 17:21:02 -0800, "Casey"
    <> wrote:

    >
    >Hello John,
    >
    >When I imported the data from Excel I did it as you stated
    >in the first part of your responce. Still the zero's are
    >missing. The other option with editing the .xls files is
    >something i'm not going to be able to do. We have a
    >process that opens the excel file as it is being created
    >and then imports the data to the Access database. I cannot
    >interupt the process this is something that is high volume.
    >
    >Casey,


    If the Access Zip field is a Text field, run an UPDATE query updating
    the zip to

    Right("00000" & [Zip], 5)

    If Zip is numeric (not a good idea but if you're importing you may be
    stuck with it), set the Format property of the field to

    "00000"

    This won't store leading zeros but it will display them.

    It's vexing but if you can't edit the spreadsheets, that's about the
    only ways to do it!

    John W. Vinson[MVP]
    Come for live chats every Tuesday and Thursday
    http://go.compuserve.com/msdevapps?loc=us&access=public
     
    John Vinson, Mar 18, 2004
    #4
  5. Thank you John,

    This information has been helpfull.

    Casey,


    >-----Original Message-----
    >On Wed, 17 Mar 2004 17:21:02 -0800, "Casey"
    ><> wrote:
    >
    >>
    >>Hello John,
    >>
    >>When I imported the data from Excel I did it as you

    stated
    >>in the first part of your responce. Still the zero's

    are
    >>missing. The other option with editing the .xls files

    is
    >>something i'm not going to be able to do. We have a
    >>process that opens the excel file as it is being created
    >>and then imports the data to the Access database. I

    cannot
    >>interupt the process this is something that is high

    volume.
    >>
    >>Casey,

    >
    >If the Access Zip field is a Text field, run an UPDATE

    query updating
    >the zip to
    >
    >Right("00000" & [Zip], 5)
    >
    >If Zip is numeric (not a good idea but if you're

    importing you may be
    >stuck with it), set the Format property of the field to
    >
    >"00000"
    >
    >This won't store leading zeros but it will display them.
    >
    >It's vexing but if you can't edit the spreadsheets,

    that's about the
    >only ways to do it!
    >
    > John W. Vinson[MVP]
    > Come for live chats every Tuesday and Thursday
    >http://go.compuserve.com/msdevapps?loc=us&access=public
    >
    >.
    >
     
    Thank you John, Mar 18, 2004
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Trisha

    Imported Data Normalization

    Trisha, Sep 12, 2003, in forum: Microsoft Access Database Table Design
    Replies:
    7
    Views:
    159
    Trisha
    Sep 15, 2003
  2. Paul James

    an imported text field from Excel displays as an exponential number in Access

    Paul James, Feb 26, 2004, in forum: Microsoft Access Database Table Design
    Replies:
    5
    Views:
    442
    Paul James
    Feb 27, 2004
  3. Andrew

    Date interpretation for imported data

    Andrew, Mar 16, 2004, in forum: Microsoft Access Database Table Design
    Replies:
    0
    Views:
    137
    Andrew
    Mar 16, 2004
  4. michaelwoodard

    Linking Access 97 tables to Access 2002 tables

    michaelwoodard, Aug 12, 2004, in forum: Microsoft Access Database Table Design
    Replies:
    2
    Views:
    238
    M.L. Sco Scofield
    Aug 13, 2004
  5. field properties on imported tables

    , Mar 21, 2006, in forum: Microsoft Access Database Table Design
    Replies:
    7
    Views:
    157
Loading...

Share This Page