Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Excel spreadsheet (2000)

    I'm trying to import an Excel spreadsheet into an Access 2000 database. I keep receiving import error messages saying Type Conversion on the Zip Code field. Some of the numbers that it's choking on the standard 5-digit zip and some are the 5+4 zips. I've tried re-formatting the zip code column and then the entire spreadsheet with the same results. Please help . . .

    Thanks,
    Deb

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Excel spreadsheet (2000)

    What are they formatted to? I would not recommend a number format for this.
    Have you tried formatting the ZipCodes as text in both the Excel workbook and the Access table field?
    Does that help?

  3. #3
    New Lounger
    Join Date
    Aug 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Excel spreadsheet (2000)

    I tried formatting both the zip code column and then the entire spreadsheet in Excel as text. That still didn't work. If I change the data type in Access, what would recommend I do about the zip codes that didn't import correctly?

    Deb

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import Excel spreadsheet (2000)

    Delete all the records from the Access table and change that field to a text data type. Now import the Excel spreadsheet but have it append to the existing table. It should import all your records unless your spreadsheet has other problems.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Aug 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Excel spreadsheet (2000)

    Silly question but how do I change the data type on the table's field? I've checked Access help and it only talks about changing it on a form or report.

  6. #6
    Star Lounger
    Join Date
    Apr 2001
    Location
    Oklahoma City, Oklahoma, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Excel spreadsheet (2000)

    Go to the Table in Design mode. Change the Data Type to Text.

    Make sure the Field Size, in the big box below, is at least 10 to allow for the 5 zip, the "-" dash and the Plus 4.

    JAM

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Excel spreadsheet (2000)

    Assuming you still have the table with data imported from Excel:
    1. <LI>First, open the table (in datasheet view) and delete all records. Close it.
      <LI>Second, open the table in design view (select the table in the database window and click Design).
      <LI>In the top half of the window, you see a grid with three columns: Field Name, Data Type and Description.
      <LI>Click in the Data Type column of the Zip code field. A dropdown arrow should appear.
      <LI>Click the dropdown arrow and select Text from the list.
      <LI>In the lower half of the window, the properties of the field are displayed. Set the Length property to something reasonable, for instance 15.
      <LI>Close the table and confirm that you want to save changes.
      <LI>Now import the Excel data again to the table you just modified. Both types of zip code should import without problems now.
    HTH,
    Hans

  8. #8
    New Lounger
    Join Date
    Aug 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Excel spreadsheet (2000)

    Hans,
    Thanks for the step-by-step information!! I think that the Excel file is corrupt - I still get a data type conversion error on the zip codes even after following your instructions. Back to the drawing board . . .

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Excel spreadsheet (2000)

    Oops, I spoke too soon!

    I hadn't really tried my own description. I just did so now with a small sample spreadsheet, and ran into the same problems! Apparently, the import routine tries to import the zip code column as numbers before converting it to text...

    I found two workarounds:

    (1) Insert a dummy row immediately below the field names (column headers). Enter dummy data of the correct type in this row, for instance xxxx in the zip code column. This forces the import routine to treat this column as text.

    (2) Export from Excel to a text file (tab-delimited) and import the text file into Access. The Text Import Wizard lets you change field types.

    Sorry about my gaffe. I hope this helps more.

    Regards,
    Hans

Posting Permissions

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