So there I am trying to import data into Access, and I’m getting the dreaded subscript out of range error. Grrr….
I went through the usual fixes, but nothing worked. Then I decided to investigate a little. I imported the data into a new table, and then compared the field types of that table with the table I’d been trying to append the data to. Turns out that two date fields were being imported as ‘nvarchar’ instead of ‘datetime’. Hmmmm. I look at the data. All the values in these date fields are ‘######’.
If you import an excel sheet with a column narrow enough that the data shows up as ‘######’ in Excel, the data will be imported as ‘######’. How stupid is that?
Turns out those two date columns were just narrow enough to show the values as ‘######’, and that caused them to be imported as the wrong datatype.
[UPDATE 1-30-2010: Whoops! Turns out that it was a macro I wrote to depivot data that somehow takes the #### literally. Still, it pays to make sure columns are wide enough]
So I was just trying to import some data into an existing table, and I get unknown error. The existing table had two fields I was importing, one is a varchar, the other an int. The file I’m trying to import was an excel file, but I suspected that Access was interpreting the field that would be the varchar as a float. So I added a line at the top of the file, put a word into the nvarchar field, and a value into the int field and tried the import again – success!
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!
The other day I was importing some data into Access from an Excel file. I got a “Subscript out of range error”. This is a pretty infamous error that Access throws up quite a bit when importing data, and there are numerous causes.
Some are pretty logical, like having text data in a column you’ve designated as a number, or having invalid column names that you’re trying to turn into field names.
Sometimes however, it just seems like it’s astrological, related to the weather, or what I had for breakfast.
But I just figured out a trick. It turns out I was trying to import data into an existing table. I’d created the table, and defined fields how I wanted them and made sure the spreadsheet had fields that matched. However when I tried to import I got an error.
I struggled with the usual nonsense – checking for invalid entries, deleting innocently-empty looking rows and columns, and looked for trailing whitespace. No luck.
So for kicks I tried to import the file and instead of importing it into a new table, I just had Access create a new table. It worked. Hmmm.
Checking out the difference between the two tables it seems that some of the fields I’d designated as integers in the table I’d created were actually imported as float values by Access. I changed the fields in my table, did another import, and it worked. Hurray!