Having trouble woth type conversion when importing from Excel to Access

[boksi]

Hello,
I have getting type conversion errors for numeric and currency fields when importing an Ecxel spreadsheet into a Table in Access. I have tried changing the data type to text in Excel, and that is what it says I have done when I examine the field formats in question. How can I force this to be text, it keeps reverting,
i.e. the fields in question, to numeric and currency. Can anyone help? Thank you.
Joe

 

[snoko]

Make the first excel record value to text by adding an alpha character to the value. i.e if the value is 1234 amend it to read a1234 then import the data. Access will then set the data type of the field to text. In access remove the 'a' from the data.

Job done.

 

[neileg]

I've had this problem so often that I tend to include a dummy record in Excel with data that forces the right type.

 

[boksi]

Acknowelgement

Thank you both - I will give it a try. Joe

 

[The_Doc_Man]

There is another, "purer" solution with less manipulation required, long-term.

Create the table first with the right data types in fields with names that match the field names (column names) of the spreadsheet.

Then import the spreadsheet but DO NOT create a new table. Append to an existing table - the one you created with the right decisions already made.

If this process is commonly done, two more things will help.

1. Do the first import by hand, creating an import spec. Save it. On later imports, use the saved spec rather than trying to redo the spec right each time. As a matter of fact, you have the option to do an import spec that will get data types right even WITHOUT creating a table before-hand - but text field sizes will be too big most of the time if you haven't reset the default field sizes on the Preferences dialog box. With a pre-defined table, even this problem doesn't occur.

(But if you get text field sizes wrong, you can still get import errors.)

2. Create a Delete query that, in SQL, might be so simple as DELETE * FROM MyFunkyTable; - then run the delete query before you do the import a second or subsequent time. Keeping an empty table hanging around isn't really that expensive. Just a TableDef entry and some FieldDef entries, none of which have to be particularly big.

Sometimes, the Import wizards are just plain DUMB. But if you help them this much, you win every time, almost.