Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts
    When I import a contact listing from Excel into an access table the zip codes drop the leading zeros in my table. The format in XL is Special/zip codes, which shows the leading zeros. How do I import a true 5-digit zip code?
    HH

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Two options come to mind.

    1) Change the formatting for the Excel sheet to text, unless the zips are being entered without the leading zeroes

    2) Instead of importing the file, link the file and then use a "Make table " query to make your table. The zips can be changed with the formula of Right("00000" & [ZipCode],5) to add the leading zero zips.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='Howard Hillman' post='778913' date='09-Jun-2009 13:31']When I import a contact listing from Excel into an access table the zip codes drop the leading zeros in my table. The format in XL is Special/zip codes, which shows the leading zeros. How do I import a true 5-digit zip code?
    HH[/quote]

    Access decides on the data type by looking at the first few rows of the Excel file. When I want to force a field to be text, when Access wants to make it a number field, I put in a dummy row of data at the top of the Excel file, that has text values for the relevant field. e.g. abc for Zipcode.
    Once the data has been imported, this record can be deleted.
    Regards
    John



  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='778914' date='09-Jun-2009 03:11']Two options come to mind.

    1) Change the formatting for the Excel sheet to text, unless the zips are being entered without the leading zeroes

    2) Instead of importing the file, link the file and then use a "Make table " query to make your table. The zips can be changed with the formula of Right("00000" & [ZipCode],5) to add the leading zero zips.[/quote]

    Thanks for the solution, (2). I had a table ready to go, so I created a query with a field Newzip:Right("00000" & [ZipCode],5). Worked fine. Now I'll change the query to an Update Query and fix the table.

    As for (1), I tried that before my post and it didn't work. Probably because Access still thinks it's a number.
    Thanks,
    HH

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='778915' date='09-Jun-2009 03:37']Access decides on the data type by looking at the first few rows of the Excel file. When I want to force a field to be text, when Access wants to make it a number field, I put in a dummy row of data at the top of the Excel file, that has text values for the relevant field. e.g. abc for Zipcode.
    Once the data has been imported, this record can be deleted.[/quote]
    I'll try your solution next time. This time it was easier to use mbarron's. (See previous post.)
    Thanks for your help.
    HH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •