Excel to Access conversion

5 pts.
Tags:
Access 97
Excel 2003
Excel to Access Conversion
Microsoft Access
Microsoft Excel
When I import data from MS Excel v2003 into MS ACCESS v97, some of the data converts (numbers) converts to scientific form (4268411 converts to 4.26841e+006) even though I specify that the column format is text. How can I stop this from happening?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Yes, I concur with Randym’s observation – I went thru many of those pains. I have two suggestions (but on office 2007 version). This may be available in previous version, I vaguely remember.
1) Is it possible to use quote mark on the numeric field (123 will be noted as ‘123)? I realize this is extra task – but it works. Note: Access table will not have the quote mark.
2) While using the Export step by step procedure – be sure to click on the field (numeric) and select “Text” from the drop-down list.
Good luck.
************************
Excel to Access is a pain because the format you specify in Excel for display does not affect the linking or importing to Access. The way around that I have found is that you need to make the import think that the field is a text field by having some of the first rows in Excel be actual text instead of numbers. So you need maybe the first 5 to 10 rows in excel have letters or something in that column to make it text. After you import the “fake” rows with text, you can then delete them from the Access table.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Try converting your worksheet to a .csv format that’s only plain text separated by ,
so that means that you’ll save exactly the values you see on the screen.

Then you can go to access and import the file.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following