Taking Forever to Import Excell spreadsheet to Access

[flyinghippo99]

hi All,

Is there a size limitation on how many rows an Excel 2007 spreadsheet can have when importing to Access 2007? I have about 9300 rows of data in Excel(not big) and the eventual size will be much bigger. The file size is not even a 1000 kb.

But even now, it's taking forever to import just 9300 rows of Excel data. Geez. I did it through the import wizard as well as through the DoCmd.TransferSpreadSheet function, which normally works like a charm.

In fact, it's still hasn't completed. I killed the process the first few times. But then I Iit run to see how long it takes to import. The next day I came in it finally completed BUT I do not see the table. Something is wrong... Shouldn't take this long and the size isn't that big too.

Any ideas/suggestions?

thanks!

 

[The_Doc_Man]

Might be a field limitation. How many COLUMNS are in the spreadsheet? Is there a chance that one row will have more than 2048 characters?

Also, Access typically builds a second table of anomalous imports where it puts a message that says "row xxxx failed to import because..." Did one of those get created?

 

[flyinghippo99]

Might be a field limitation. How many COLUMNS are in the spreadsheet? Is there a chance that one row will have more than 2048 characters?

Also, Access typically builds a second table of anomalous imports where it puts a message that says "row xxxx failed to import because..." Did one of those get created?

TheDocMan,

there aren't that many columns in the spreadsheet(only 10 columns). The file size isn't even that big only 444kb! The rows have small characters. Nothing usual. In fact,
I imported a smaller version(i.e. 100 rows instead of the 9300 rows) and it went
perfectly.

I didn't get the "row xxxx failed to import because..." because the import process
just hang so long that I either killed the process or I let it run overnight and by the
time I got there in the morning the process was already completed and there was
no error message(or at least not when I looked at it) and there was no error table
either and the spreadsheet never made it to the database.

Very strange... However, I just noticed one thing about this spreadsheet that I
should tell you. The first column is Date. The second column is a KeyNum. When
the spreadsheet is imported(<100 rows test case), I use the first AND the second
column as composite keys to make it a unique combination. That's part of the
table/db design.

The reason that is because the Dates repeats(i.e. not unique) by themselves. Because a KeyNum(the second column) can have values over a range of Dates
(1/1/2008, 2/1/2009,etc. etc.). Then another KeyNum can have values over the
same date range hence the need for composite key pair.

So with that new piece of information, I was wondering if so many repeated dates
might be through off the Import process because Access needs it to be unique?
Usually I would import it(the small test case), then go to Table design to make
it a composite key pair. But I can't even get it to import to do that..

thanks!

flyinghippo99

 

[spikepl]

If 100 imports ok, maybe there is something weird in the data.

Split your Excel in half, and import each half. If one fails to complete in "reasonable" time, verify that the other half reads ok. Split the failing half in two, and repeat the entire process till you find the culprit

 

[flyinghippo99]

Nevermind. For some reason, the Import Wizard works now. But now it showing a bunch of type conversion error. Does anyone know if the DoCmd.TransferSpreadsheet allow one to specify each column format of the spreadsheet as they come in...?

thnx!

 

[spikepl]

Make a table, with the same field names as the headers in your excel, and specify that your excel has headers in the transferspreadsheet parameters.

 

[flyinghippo99]

Make a table, with the same field names as the headers in your excel, and specify that your excel has headers in the transferspreadsheet parameters.

Yep. makes sense. thnx!

 

[The_Doc_Man]

As to the key problems, I almost NEVER import to a keyed table. Instead, I import to a simple table that I call a "staging" table, one with practically nothing defined - perhaps except for the preferred data types for each field. Then I do an append query from the unstructured "staging" table to my final table. Then I dump the staging table. Finally, for big enough inputs, I compact and repair.

I've had direct imports fail more than once but I've never had a staging import fail unless I blew one of the hard limits in Access.

 

[Galaxiom]

As to the key problems, I almost NEVER import to a keyed table.

Good advice.

Instead, I import to a simple table that I call a "staging" table, one with practically nothing defined - perhaps except for the preferred data types for each field.

Pehaps indeed. I generally import everything to the staging table as text. That way I know it will arrive without invalid data problems. I have seen too many imports with a random alpha value in a column that should have been numbers.

 

[spikepl]

#9 I do it the same way. But, when importing as TEXT, one has to take care with decimal values: you get only what is DISPLAYED in Excel, ie. your imported SINGLE or DOUBLE values have only as many digits after the decimal separator imported as DISPLAYED in Excel, according to the cell format, and not the actual values contained in the cells.

 

[Galaxiom]

only as many digits after the decimal separator imported as DISPLAYED in Excel, according to the cell format, and not the actual values contained in the cells.

Just shows how the developer must be acutely aware of the nature of the data. One must never be complacent and just follow habital processes without appropriate analysis.