By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,346 Members | 2,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,346 IT Pros & Developers. It's quick & easy.

Import Excel to Access: many to one, superfluous blank lines

P: 6
(Excel 2003, Access 2003, XP, novice user here)

I can't get Access to accept multiple Excel files to the same table. I can import the spreadsheets to a new table, but I need to import 23 spreadsheets to the same table. I'm not getting a formatting error, just a "file did not import" message.

Also, when I do import one-to-one, Access inserts thousands of blank lines before the first line of imported data. How do I keep Access from doing this?

Perhaps I should be asking how to import from Crystal reports...that would eliminate the entire Excel issue if I could do that.
Aug 8 '07 #1
Share this Question
Share on Google+
3 Replies


P: 56
After doing some research, I found that Access is a bit funny when importing from Excel. Losing and not importing data etc. What I decided to do was to import , delimited .txt files. Much safer.

Save your .xls as .csv, then make .txt, and stick it in!
Aug 8 '07 #2

P: 6
After doing some research, I found that Access is a bit funny when importing from Excel. Losing and not importing data etc. What I decided to do was to import , delimited .txt files. Much safer.

Save your .xls as .csv, then make .txt, and stick it in!
Thank you. I tried your solution, but unfortunately we have so much garbage in the data source that importing a .txt file yields quotation marks everywhere. A simple find/replace won't get rid of them b/c quotation marks are used as abbreviations for foot/inch measurements in the description column.

How did you old timers manage to keep your sanity all this time?
Aug 8 '07 #3

Expert 100+
P: 1,221
What you need to do is save the Excel worksheet as a tab delimited file, not a quote-comma delimited file. (That assumes that you have not also used tabs within your data.)

In Excel, go to File/Save As, and look over the list of file types you can save under. You'll see "Tab delimited" in there ... then when you are importing into Access, it will probably automatically recognize that you are using tab delimiters, but if it doesn't, just change the radio button to that choice.

Good luck, home I'm not too late with this advice.
Jim
Aug 25 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.