Problem importing excel file 110 fields, 550000 rows, access 2007

[DavidCRT]

Hi guys. Please help with this situation. I'm fighting with it from this morning and i'm desperate

I have an excel file as said in the title with 110000 fields and 550000 rows. I have eliminated all the potential symbols in the fields that are not compatible with access. When i try to import with the wizard i get error "subscript out of range" or the wizard does not found your file or it is closed, something like that. The excel file works perfectly, and if i cut and paste as trial 10 columns and 1000 rows the improts works perfectly. Si, considering that the file is 255 mb and the range respects access 2007 limits what could be the problems??

Please help, i'm desperate..
Thnx e ciao
Davide

 

[Joe4]

Welcome to the Board!

Are there 110 fields, as your title suggests, or 110000 fields as your questions asks?
You can only have 255 fields in Access.

What happens if you try to link the Excel file instead of importing it?

 

[DavidCRT]

Hi Joe. Thanks for the welcome. 110 fields. I'm not able to choice for the link cause the process get blocked before the choice. It loads for some minutes and then say
subscript out of range..

 

[Joe4]

It sounds like the issue may be an entry that you have somewhere in one of your fields.
The problem with importing/linking Excel files in Access is that Excel and Access communicate and try to determine the format of each field themselves (instead of letting you choose). I believe that Excel looks at the first 10 tens to determine that. So if you have a field that has numeric entries in the first ten rows, but a text entry somewhere further on down the page, it will blow up.

Typically, I see this issue most with mixed data types, unexpected blanks or nulls in your data, or blank rows or trailer/total rows being included in your import range.

You can spend hours trying to figure out where the problem is occurring (Access isn't much help in telling you which field may be causing issues).
One work-around that many people employ is to export your Excel file to a CSV file (or some other type of Excel file). Then import that text file into Access. When you import a Text file into Access, you control the Format of each field. You can choose to set every field to Text to see if that will import it. If that does work, you can try converting the columns to their appropriate data type after conversion, and see if any data is being dropped.

 

[DavidCRT]

It sounds like the issue may be an entry that you have somewhere in one of your fields.
The problem with importing/linking Excel files in Access is that Excel and Access communicate and try to determine the format of each field themselves (instead of letting you choose). I believe that Excel looks at the first 10 tens to determine that. So if you have a field that has numeric entries in the first ten rows, but a text entry somewhere further on down the page, it will blow up.

Typically, I see this issue most with mixed data types, unexpected blanks or nulls in your data, or blank rows or trailer/total rows being included in your import range.

You can spend hours trying to figure out where the problem is occurring (Access isn't much help in telling you which field may be causing issues).
One work-around that many people employ is to export your Excel file to a CSV file (or some other type of Excel file). Then import that text file into Access. When you import a Text file into Access, you control the Format of each field. You can choose to set every field to Text to see if that will import it. If that does work, you can try converting the columns to their appropriate data type after conversion, and see if any data is being dropped.

I have checked rapidly and there are some fields that are alphanumerical, and i have also some blank cells...I will try by removing aplhanumerical fields...But that's mean that access does not accep field like for instace alphanumerical codes??

 

[Joe4]

No, that is not what I am saying at all.

I am saying that if the top 10 rows of your data is not a good representation of what is contained below that, that is when problems occur (since Access is looking at the top 10 rows to determine what the data type for each column should be).
For example, if your first ten rows contain ALL numeric entries for a column, Access will probably set that column to a numeric data-type. So, is somewhere further on down that column there is a non-numeric entry, that is going to cause an issue..

 

[nuked]

+1 for csv