Import Excel worksheet (win XP Access 2003)

Topic

New Reply

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.

Reply | Quote

Replies

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:

• 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.
• 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.
• 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.

Reply | Quote

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

Reply | Quote