Solving Problems Importing Data into Microsoft Access

January 8, 2018 by Jamel Cato

Most of my business time is spent with Oracle, SQL Server and mySQL databases, but every so often I get asked to help with an uncooperative Microsoft Access database. When importing data into Access, I sometimes get its dreaded Import Errors or experience other problems. I commonly run into these kinds of issues when importing hospital charge master files because charge codes often contain alpha characters and asterisks mixed in with numbers.

Here are some techniques I've learned over the years to deal with them.

  • Open your source file in a text editor (or its native environment), find the column causing the error and then place a single quote in front of the first numeric value in that column. This will force MS Access to view the entire column as text, even if it contains numeric values. If you need to perform numeric calculations on that column once it's successfully imported into Access, just open the table in Design View and manually change the data type back to a numeric type.
  • If your source data is a Microsoft Excel file, always bring it in with the Import Text Wizard in Access instead of its Import Spreadsheet Wizard. The reason is that the Text Wizard permits you to change the data type of any column before importing it, but the Spreadsheet Wizard does not. Simply save your Excel file as a CSV file first.
  • If you import Excel files into Access on a regular basis, it would behoove you to change the value of the following Windows Registry setting to 0 (zero), which will permanently force Access to guess each Column's data type based on all of its values instead of just the first 8 or 10: 
    [HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel]
"TypeGuessRows"=dword:00000000
    (Disclaimer: Only modify your Windows Registry settings if you understand what you are doing and have a backup copy.)
  • If you try to import a file programmatically with VBA and get a type conversion import error, open your source file in a text editor, add a new row at the top of the file, and then enter Jamel Cato as the value in each field causing the errors. Then try it again.
  • If you're using the DoCmd.TextTransfer method to import a CSV file and Access is skipping or rounding your values after the import, then try adding this line just above the line where you call TextTransfer: 
     [YourSourceFile.txt].[YourColumn].numberformat = "@"
  • If your source file has a field which may contain a value with more the 255 characters, change the Access data type to Memo.
  • If the problem field contains monetary values (such as dollars), try changing the data type to integer or double.
  • If you have a particular format or layout that you expect to import frequently, save your Import settings as an MS Access import specification which you can use over an over again. The easiest way to create one is to click the Advanced Button, then Save As while using the import wizard.
  • If you will be importing a very complex source file, or you need to tweak the import settings of certain fields in ways not possible with the Import Wizard, then create a schema.ini file in the same directory as the source file you will be importing. You can Google the specifics, but in a nutshell a schema.ini file is a text file where you can hard code every possible Access import setting. This works best with delimited source files.
  • If your source file has dates in short format (such as 15-Nov-2017), but Access always brings it in long format (11/15/2017 0:00:00), then just create a query with a calculated field that formats the date the way you want. For example: 
    ExpDate: Format([YourDateField], "Short Date")
  • If your source data is a text file that Access refuses to import, then try using Access to make an ODBC connection to the file. You can do this by creating a new data source in Windows and selecting Microsoft Text Driver as the ODBC driver.

If you try all of the above and still get import errors, I have good news and bad news. The bad news is that your source data is probably corrupt. The good news is that in the process of discovering this you expanded your skills so much that you could probably get a job as an ETL analyst.