Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Data from Excel Problem (2003)

    A rather strange thing has happened to my data I was importing into a table from an excel source.

    I have field called InvoiceNo in my access table and the data type is set to Text.

    After importing the data I wanted, I noticed that the invoice numbers containing text have not been imported at all.

    I thought I would do an update query to fix this so I linked the excel sheet to access and the tried to run an update query which would update the InvoiceNo fields with the values in the linked excel table if the original value was blank. This hasn't worked either.

    I checked the linked excel table and noticed that the invoice numbers which are not numeric aren't being displayed.

    What am I doing wrong?

    Kind regards
    Hayden

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Import Data from Excel Problem (2003)

    It sounds like your database may be corrupted in some respect. If you didn't get text values, then there should be an Import Errors table that was created during the import process and it will give you a reason for each record/field that didn't import correctly. If you had a pre-existing table with the data type set to text, it should have behaved properly. The fact that a linked table is not displaying correctly either suggests all is not well with your database, although Access makes some assumptions about data type for a linked table based on the first 50 or so rows of data.
    Wendell

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Import Data from Excel Problem (2003)

    Maybe your excel sheet contains only numerics in the 1st 25 rows or so (I cannot remember how many it checks for), so it will kick out the text chars it finds later in the sheet.
    Do you use a Import Specification, if you don't, maybe you should.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Data from Excel Problem (2003)

    Hi Pat

    Your suggestion was very useful, thanks very much, I re-sorted the excel data so that some non numeric invoice numbers fell into the first 25 or so rows.

    I then successfully imported the data into access.

    Many thanks for your help
    Hayden

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Import Data from Excel Problem (2003)

    Those sort of issues are why we usually recomend importing into a table that already exists, but is empty.
    Wendell

Posting Permissions

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