More on importing data into Access

So you’re trying to import data into access, and you’re getting an error. “Subscript out of range” is a popular one, but some flavor of “import failed” with zero explanation is also infamous.

Here’s the laundry list (in no particular order) of things I do to resolve the problem:

• Export the data out of Excel into a csv file first, and then import into Access.
• Check the data in Excel for the folloiwng. Sorting can reveal these issues, and search and replace can usually take care of them.:
  • Empty cells
  • A rare number in a text column or rare text in a number column. Some government agencies are fond of putting “N/A” in place of an empty cell or zero.The also are fond of putting years in as text rather than as a number.
  • Empty rows.
• Let Access create the table on import, rather than trying to import into an existing table – resolves conversion issues.
• Delete the rows below and the columns to the right of, the data. How many? Usually 10-20, but really the intent is just to get rid of any cells containing whitespace.
• Use the Trim function in Excel to get rid of space before or after numbers, to prevent their being imported as text.

How do you solve these problems? Have a tough import problem? Let’s have a look!

Related posts:

1. Avoiding errors when importing data into Access.
2. Another Access Import Problem Cause To Avoid
3. Access data import is idiotic
4. Access import trick: Make sure columns are wide enough
5. Another trick for bypassing unknown error on Access data import

Tagged as: Access, Subscript out of Range, Unknown Error