Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Excel worksheet (win XP Access 2003)

    I am importing an Excel worksheet using the Access wizard. About the fourth step, after specifying a new table, I am given the options of renaming fields, adding indexes, not importing the field, and supposedly change the data type. The data type is greyed out, and cannot be used.

    Why am I unable to use that option? My Excel sheet has a field with all numeric characters, but the cells are formatted as Text; however, the resulting Access table classifys the field as numeric.

    I would appreciate your comments.

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

    Re: Import Excel worksheet (win XP Access 2003)

    This is a bug that was introduced a while ago and hasn't been corrected in Access 2002 and 2003 (I don't know about Access 2007).
    There are several possible workarounds:
    <UL><LI>Change the field from number to text after importing. This may, however, lead to loss of information, for example if the values contained leading zeros in Excel, so it is probably not the best option.
    <LI>Insert a row in the Excel sheet immediately below the column headings (if present), and fill each column with dummy data of the correct type, for example 123 in a number column and blahblah in a text column.
    This should force Access to interpret the data correctly when importing.
    Delete the dummy record from the imported table.
    <LI>Import the sheet once, then delete all records.
    Open the imported table and correct the data types, then save it.
    From now on, you can import into this table.[/list]You can choose the option that suits you best.

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Excel worksheet (win XP Access 2003)

    Creating the table with the correct data types and then importing to that table works just fine. Thank you.

Posting Permissions

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