web stats

More on importing data into Access

by Steve on July 7, 2009

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. Access import trick: Make sure columns are wide enough
  3. Another trick for bypassing unknown error on Access data import
  4. Access data import is idiotic
  5. Another trick for avoiding an Access import error

Leave a Comment

Previous post:

Next post: