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:
