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!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s