Data Converstion errors – Excel to Access (Both Office 97)

Topic

New Reply

I am merging three Excel files (from differenct sources) into one Access database. I merged them all in Excel first to
get the data organized in common columns. I then selected whole columns in Excel to apply consistent formatting.
When I import it I get a lot of Conversion Errors (mostly dates but some numbers, too). Whole columns do not get
kicked out – only large chunks of data from certain columns. So I went back to the Excel spreadsheet and picked one
record that worked and pasted that record’s formatting over the ones that didn’t. Still no luck. Finally worked around
temporarily by making all fields text. Not much use for the long run though. Thank you for any suggestions you can offer.

Reply | Quote

Replies

Now you have the data in Access have you tried using Access to convert it back to numbers, dates etc.??

Peter

Reply | Quote

Beware of empty cells at the top of your columns.
You may want to fill in some ‘filler’ data in the top record of the final merged sheet
which insures there is data in the first cells when it imports.

For instance if you have a column of numbers and you just want them as text , then drop in a few capital AAAs.
Obviously for numeric, you want a different approach. You can always filter them out later, cuz it’s at the top of the stack.

Reply | Quote

Thank you for your reply to my post last month. I did try this and it worked for most of the columns I was having problems with. I finally took the 4 or 5 remaining columns and moved them back into Excel individually and just kept beating them up until they behaved the way I wanted them to and then copied them back into the Access database.

Thanks again for your help and sorry for the belated response.

Reply | Quote