monkeybiz12345
asked on
How to avoid Type Conversion Error when importing data from Excel into Access
I put some data into a spreadsheet for a user to edit. When they gave me back the edited spreadsheet, I got Type Conversion Errors when I tried to import it back to Excel.
These errors occured on fields that had numbers in them. For example, I gave the user
F1 F2 F3 F4
ABCDE 60 290000 9000
BCDEF 10 100000 9000
They gave me back
ABCDE 60 290000 9000
BCDEF 60 100000 9000
WERTF 10 290000 9000
When I imported it into Access, the first line imported fine (no changes to this line).
On the 2nd line, I got a Type Conversion Error on F2 and the value "60" didn't load.
On the 3rd line, I got a Type Conversion Error on F2, F3, and F4.
I need to be able to easily exchange data between Excel and Access without losing valuable data to Type Conversion Errors. The Access file import wizard looks like it should allow me to specify the field type before importing, but I find that I can only select the first field. Is there a way to either 1) force Access to ignore any types that come from Excel and treat everything as text or 2) force Excel to enter all data as text?
These errors occured on fields that had numbers in them. For example, I gave the user
F1 F2 F3 F4
ABCDE 60 290000 9000
BCDEF 10 100000 9000
They gave me back
ABCDE 60 290000 9000
BCDEF 60 100000 9000
WERTF 10 290000 9000
When I imported it into Access, the first line imported fine (no changes to this line).
On the 2nd line, I got a Type Conversion Error on F2 and the value "60" didn't load.
On the 3rd line, I got a Type Conversion Error on F2, F3, and F4.
I need to be able to easily exchange data between Excel and Access without losing valuable data to Type Conversion Errors. The Access file import wizard looks like it should allow me to specify the field type before importing, but I find that I can only select the first field. Is there a way to either 1) force Access to ignore any types that come from Excel and treat everything as text or 2) force Excel to enter all data as text?
Hi monkeybiz,
When you said "I got Type Conversion Errors when I tried to import it back to Excel" did you mean to say Access?
What are the data types of the columns that you're trying to import into?
Jim
When you said "I got Type Conversion Errors when I tried to import it back to Excel" did you mean to say Access?
What are the data types of the columns that you're trying to import into?
Jim
ASKER
Yes, Jim, I meant to say "Access".
I've prepared several samples for illustration and, so far, am unable to reproduce the problem. I think the issue may have been the way I created the Excel wrokbook from the Access file originally.
When preparing the sample, I changed all the fields in the Access table to type Text before creating the Excel file. I've been able to import these back into Access without error, even after editing the data in Excel. I've asked the user who did the editing to test this for me, in case it has something to do with their copy of Excel or the way they are doing the editing.
I don't recall changing the Access field types to Text on the original file so that may be my solution.
I've prepared several samples for illustration and, so far, am unable to reproduce the problem. I think the issue may have been the way I created the Excel wrokbook from the Access file originally.
When preparing the sample, I changed all the fields in the Access table to type Text before creating the Excel file. I've been able to import these back into Access without error, even after editing the data in Excel. I've asked the user who did the editing to test this for me, in case it has something to do with their copy of Excel or the way they are doing the editing.
I don't recall changing the Access field types to Text on the original file so that may be my solution.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, Jim. Looks like Access decides which data type to use based on the data in the first record. I kinda stumbled on this awhile ago when resorting the data allowed it to import without error. It didn't register at the time that this was the reason.
I think its something like 8 rows.
1. Post sample Excel workbook to demonstrate the error;
2. Offer more than 50 points for a solution. This one is going to take a bit of work.