Blank rows when importing Excel data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I imported three Excel databases into Access, each about 24,000 rows. Each
time I imported a data set, Access inserted some 24,000 blank rows on top of
the table which I had to delete manually. Is there a way to avoid that?

Thanks!!
 
No, I had trouble importing with a named range so I imported the entire
worksheet, which however had no blank rows - the list started on Row 1.
 
Try opening the spreadsheet in question. Then press the Control key (Ctrl)
and the End key at the same time. Which cell reference do you go to? In other
words, I'm wondering if you might not have some formatting applied in row
24000, even though there is no visible indication.

If the spreadsheet in question contains data that is not too sensitive in
nature, and you feel comfortable sending me a zipped copy, I will take a look
at it for you. If you are interested, send me a private e-mail message with a
valid reply-to address. My e-mail address is available at the bottom of the
contributor's page indicated below. Please do not post your e-mail address
(or mine) to a newsgroup reply. Doing so will only attract the unwanted
attention of spammers.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
I'll try your suggestion. Unfortunately, the databases are proprietary to a
3rd party and I'm not permitted to disseminate any portion of them. However,
I do appreciate immensely your offer to take a look at them. Thanks!
 
I tried your suggestion to hit CTRL+END; in the first of the data bases that
I imported into Access, the cursor goes to U24443, the last populated cell.
In the other two data bases that were imported and appended, the cursor goes
to U65536. I'm not sure where to go from here.
Thanks for your patience!
 
Hi KG,

That means that there is *something* in those cells. Apparently Access's
import wizard is seeing that something, and deciding that the data goes that
far.

You indicated earlier that you had trouble importing with a named range.
What type of trouble did you experience? The reason I ask is that I find
defining named ranges, and then using these to import, is usually the most
reliable method.

You might also try exporting your spreadsheet to a comma separate variable
(*.csv) text file. Open it with WordPad (or NotePad) and examine the results.
Try importing the text file instead.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Back
Top