I just discovered another thing that will cause an import problem when importing data from Excel to Access. I had a formula that was using an if statement to apply a multiplier to a column of numbers – like ‘=if(a1<>””,a1*b1,””)’ I did it to avoid the extra step of sorting the data to handle just the rows where there was data in a1. However, while the formula seem to yield an empty cell it didn’t really and I couldn’t import. When I went back and deleted it from the cells that were supposed to empty (where the else clause of the if statement would have been in effect) I found the import error went away.
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 I’m busy importing survey data (an Excel file) into our SQL Server via Access, and I my queries are turning out screwy results. I finally figured out why.
A field that should have been imported as a varchar (i.e. text) is instead being imported as a floating point number. Access was assigning the wrong data type to the field, and then dumping whatever data didn’t fit that data type. That’s because the first few dozen rows have zeros in that field. The text responses don’t turn up until later. Evidently Access looked at the zeros, decided it was a float, and then in the process of importing just skipped any data that wasn’t a float.
When I sorted the file so that text values were at the top, everything comes in fine.
Still an annoying bug, though.
Yesterday I was trying to append some data to an existing file on an SQL server database and got an unknown error. Now, I’m not sure why it seems that every error one encounters in this situation is unknown, but this one had me scratching my head.
I was importing 5 files of survey data into a master database, and the incoming data was coming out of an online survey package. The surveys were all nearly identical, copies of an original, and had the same excel format.
So why was one kicking up an error?
Simple: I’d added one column to all 5 files, and in the process misspelled it in one file. Access didn’t tell me that, and it took a while for me to figure it out but there it was. Change the name, and no errors.
So Access Import Sanity Check #15: Check the spelling of the column/field names!
So I’m importing a bunch of data into Access from Excel, and for whatever reason today is a day full of strange bugs and bizarre behaviour. So I’ve resorted to exporting much of the data out of Excel into CSV files…
Have you ever noticed these idiotic things about importing data into Access?
- If you try to import a CSV file, you’ll get an error if the file is open in any other pogram. Why? I can import in other contexts with no problems with open files.
- When you open a CSV file in Excel, and then save it, Excel throws up an error box stating that the sheet may contain features that are incompatible with CSV format. I’m not sure how that could be, but one must still answer that they do indeed want to keep the file in the format it is in before Excel will save the file.
- Excel (and Word) often decide that you have changed something in a file when you haven’t, prompting you to save when you don’t need to. CSV files are simple in format – if I didn’t add or subtract data, it didn’t change.
I know there are several import tools out there, but it bugs me to have to buy another application to get something done that Access/Excel should do more easily.
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!