Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem Importing Data from Excel XP to Access XP (10.4302.4219 SP2)

    I am having a problem importing postal zip code data from Excel to Access XP. The field includes US zip, zip+4, and Canadian codes, which differ in both the number of digits and whether or not letters are included, so I've explicitly formatted it as text in Excel. The first entry is a standard 5-digit US code. Access is setting the data type for the field to double precision. According to Access Help, I should be able to change the data type for the field in the import wizard, but the data type drop down box is grayed out and inaccessible to me. Consequently, the Canadian codes, and any US codes with a leading 0, are not being imported. Is this a bug or a feature, and is there a fix for it?

    Thanks,
    Terry

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Importing Data from Excel XP to Access XP (10.4302.4219 SP2)

    I don't know if this is a bug or not, but I doubt it since it works the same in Access 97...

    I'm assuming you've selected to import into a New Table...
    If so, I'd suggest that you try this:
    <UL><LI>import as is, so that you have a new table shell to work with...
    <LI>delete all records from the table...
    <LI>edit the field in the table design to Text data type...
    <LI>and then try importing again, but select import into Existing table this time... [/list]HTH

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

    Re: Problem Importing Data from Excel XP to Access XP (10.4302.4219 SP2)

    An alternative to Alexya's solution is to insert a dummy first record in the Excel worksheet, with text like "abcde" in text fields, numbers like 12345 in numeric fields etc. This will force Access to import the data correctly. You can delete the first record from the imported table.

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Importing Data from Excel XP to Access XP (10.4302.4219 SP2)

    That's a good idea Hans... Never thought of that... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    Star Lounger
    Join Date
    May 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Importing Data from Excel XP to Access XP (10.4302.4219 SP2)

    Thanks for the suggestions. When I imported from an excel file where one of the Canadian addresses was the first record (postal code definitely text), the import went without a hitch.

    Terry

Posting Permissions

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