Importing large Excel file

[dbrady]

Hello,

I have a co-worker trying to import a large excel file into Access. The file imports normally if I have less than 30,000 rows. The current file I am trying to load is 35,000 rows. I get an error that says the import wizard has an error and can not load the file.

Is there limits on the size of the file to import? If so, is there a way to adjust the settings to allow for a larger file to import?

Thank You in advance!

 

[Ziggy]

what does the error message say?

 

[Philem]

Hullo! Normally, Ziggy, that error is entirely unhelpful, saying that there was a failure, and nothing else.

But, to answer the OP's question: I suggest making sure that there are no "trash" columns or rows outside the data area. Just go to the edges of the data area, highlight everything OUTSIDE it, and delete. Then, make sure that your column headers have no forbidden characters in Access. I've successfully imported 63,000 some-odd records from E97 to A97.

HTH

P

 

[chariot]

I too have imported large files into Access- over 150k records. How are you importing the file into Access?

 

[SydneyGeek]

I agree with the comment about "Trash" columns -- a big headache. I usually do the following:
1. Create a named range containing the data I want to import
2. Set the number/text formats for the columns
3. Import the named range into Access.
First time, I import into a new table -- with no ID field. For subsequent imports, I delete all existing records and import into the existing table. This gets around pretty well all of the import issues in my experience.

Denis

 

[dbrady]

Thank You,

I will try deleting the empty columns in the file before I import. We are currently spliting the file in half and importing both halves of the file. It worked but just seemed a waste of time.

Thank You again.

 

[mdmilner]

Interesting, I was betting Sydney was dead-on with his answer.
I've got two questions which may help the next guy that actually answers this.

Splitting it in half as a workaround doesn't make sense to me. Should be if you have stray data in the columns that it should stay in at least one of the halves.

What version of Office/Windows are you using. Could this be a memory issue for the computer?

Mike