Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to identify import errors (XP 2000)

    I am trying to import an Excel worksheet into a an existing table (although the table has no data). As I go through the steps, the wizard finally tells me there was an error and the file was not imported. I would like to correct the error but I don't understand what is wrong.

    1) is there any was to find out why the file had an error?
    2) Does the importing table impose any particular "rules" on the data being imported?

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

    Re: How to identify import errors (XP 2000)

    It would be useful to know what the exact text of the error message is.

    A frequent source of problems when importing tables from Excel is the matching of data types. Excel doesn't impose data types for each column, Access does.
    So check the following:
    1. <LI>Compare the data type of each field in the Access table to the data in the corrsponding column in Excel.
      <LI>Also check if the Excel data will fit - a 100 character string will not fit in a 25 character text field, etc.
      <LI>Two related problems:
      <UL><LI>If the first few values in a column in Excel are numeric, Access will assume that the entire column is numeric; if later values in the same column are text, you will have a problem.
      <LI>Similarly, if the first few values in a column are blank, Access can't determine the data type for that column.
    You can get around this by inserting a dummy first record in Excel, in which each column contains dummy data of the correct type. After importing, remove this dummy record.[/list]If this doesn't help, please provide more information about the Excel workbook, the table and the error message.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: How to identify import errors (XP 2000)

    As Hans mentioned, there are a lot of variables in successfully importing Excel spreadsheets. One thing that I do when experiencing problems importing spreadsheers is import the spreadsheet into a NEW table and let Access create the field types. Then I compare the Access table to the one I was importing into.

    Ken

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to identify import errors (XP 2000)

    Just wanted to follow up that I found the source of most of my import problems. It seems that there must be an exact match between the Excel worksheet and the Access table. In other words, simply because your worksheet contains columns that match your Access table, you cannot import it if there are other columns in the worksheet without corresponding fields in the table.

    I have learned to create special worksheets containing only the columns (fields) I want to import and then all goes well... notwithstanding the need to match data types.

Posting Permissions

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