April 16, 2015

What Errors Lead to Access Data Import from Excel or Export Failure

Even though data transfer between Access and Excel is usual now, errors are still occurring during data import or export. If you have tried to import data into Access from Excel or export data from Access database, believe you can understand it.

Now the following are some errors probably you would encounter during Access data import or export. Hope the solutions can give you a hand when you really meet Access import or export errors.

Error 1: Excel XLS cell data is over maximum limits

  • Import Error: The wizard is unable to access information in the file '<filename>'. Please check that the file exists and is in correct format.
  • Export Error: External table is not in the expected format.
This is an error during Access import/export to Excel XLS. Actually, the reason is that the Excel XLS file has cells containing more than 8224 bytes of data.

So, how to correct errors if you still want to import data from excel xls to Access or export data from Access to excel xls?

1. Use the *.xlsx format instead of *.xls.
2. Restrict any memo/long text fields to a length less than 8224 bytes.
3. When exporting, delete the existing .xls file before performing the export using the same file name.
4. Open the .xls file in Excel before performing the import/export from Access.

Error 2: Imported Excel file field is out of range 

When I try to import data into a table of Access database, I am getting "Field "F42" Doesn't Exist in Destination Table" error. I have a "client lists" table, and I am trying to add additional records to it.
If you see this error, it is because your excel file field is out of range and the extra fields cannot be imported into Access database table.

Now you would have to give up something, so you can import Excel data into Access successfully. Here are some suggestions for you.

Solution 1: Go to Excel file and hit Ctrl + End to see whether it is much further than you expect. If it is, delete all extra rows and columns, save, and try again.

Solution 2: Name the range in Excel that you wish to import into Access. Then, as you are importing the file into Access, you can elect to import that named range instead of the whole sheet. That should ensure that you are only going to import what you want.

Solution 3: Delete all the extra columns and a lone variable in a distant cell. Then you can import data into Access.

Error 3: Too much rows are in Excel spreadsheet

When you are using Access data import wizard to import data from Excel spreadsheet, you receive this prompting message:

Not all of your data was successfully imported into Access. Errors descriptions with associated row numbers of bad records can be found in the Microsoft Access table <>.

Actually, this is far from unique. When you open ImportErrors table, you would find it is Type Conversion Failure. When Access scans the data in spreadsheet, row value in spreadsheet cannot be confirmed.
Whether we can make row value verified correctly? The answer is surely yes.

Because the value is stored in registry, so it can be easily changed. Then it can be confirmed when import spreadsheet into Access.

Now see how to change the registry key?

1. In Windows desktop computer or laptop, click Start and type regedit in search or Run box. Press Enter to display Registry Editor.

2. In the registry tree, select HKEY_LOCAL_MACHINE - Software - Microsoft - Office - 15.0* - Access Connectivity Engine - Engines.

Or if your Access is not the latest version, you would not navigate to HKEY_LOCAL_MACHINE - Software - Microsoft - Office - 15.0*, it would probably be Office - 14.

correct access data import errors by changing registry key

3. Double-click the Excel node.
4. In the right panel, double-click the TypeGuessRows entry.
5. Change the value data to be 0.
6. Click OK.
7. Select File - Exit to exit the Registry Editor window.

After setting the registry key to 0, Access will now scan the entire spreadsheet. This registry change works effectively especially when you frequently import huge spreadsheets into Access databases. But not all of spreadsheet row value should be changed. You can obviously set this value to whatever number of rows makes sense for you.

When importing data into an MS Access database, sometimes there are errors in the formatting of the data source. When this happens, MS Access will put the offending row into a separate table. In Access 2010, the file is called <name of import file>_ImportErrors. For example, importing try.txt, it would give try_ImportErrors.

Related Articles:

No comments:

Post a Comment