I recently went through a situation where the data was very hard to import for reasons that weren’t obvious to me even after all my years—more than 30—of importing and exporting data. There was clearly something different about the files that I couldn’t see. Access wouldn’t accept them. It took some time, but I got my data. The key is to persevere. Don’t give up after the first try. When in doubt, it also helps to return to the basics. Let’s review those and then talk about some other tips for importing data.
IMPORT BASICS
When exporting from your data source, try to export the data in a text-delimited format using a semicolon as the delimiter. Access works best with common delimiters such as tab and semicolon.
Once your data is available for import into Access, the simplest method is to have the import wizard guide you through the process. On the External Data tab, clicking on the type of file you want to import will start the wizard.
With my problematic data, I first tried importing it directly from the text file using the import wizard. The first field wouldn’t come in as a number because it was 13 digits long. Programs like to put that into exponential form, and that doesn’t always translate well on import. I tried changing the data type on import to Double or Decimal, which should allow for more digits to the left of the decimal. Ultimately, because the number I was importing was an identification number, not something I was going to use for calculations, I chose Text as the data type.
If you’re importing a text file, you can click on the Advanced button of the import wizard to save the import specifications (see Figure 1). Use Save As to give your specifications an appropriate name to use in the macro you will create for your import process.
IMPORT TIPS
Importing data boils down to two things: try everything, and try combinations. Sometimes I take the exported file, import it into Excel first, and then save the Excel file for import into Access. When designing an import process, I follow the notion of KISS (Keep It So Simple). In other words, I find the fewest steps that work and build them into a macro.
I always let Access assign a primary key if the table doesn’t have one already. This allows you to know the order in which records came into the table. It makes it easier to identify records if duplicate data is imported or if other problems arise where it would be helpful to know the original order of the records.
Save the import process in case you plan to complete the same import in the future. This option is offered to you when you end your import. To use the process again, click the Saved Imports icon on the External Data ribbon.
When you’re building the macro for the import process, use ImportExportText to import a text file. (If you’re using Access 2007 or earlier, the action is called TransferText.) Identify whether you want to bring in Delimited or Fixed Width data (see Figure 2). Then select the Specification Name if you have saved one in your manual import process. Enter the name of the table you’re importing that data into, and type in the full path and filename of the file you’re importing. If you’re importing the same data on a regular basis, make the source file name generic, like “Import.txt.” Each time you have new data to bring in, save it with that file name in the same location.
SQL SERVER DATABASE
New methods for importing are becoming available to users as technology advances and changes. Upsizing is the process of migrating some or all database objects from an Access database to a new or existing SQL Server database. The benefits of upsizing a database to SQL Server include better performance, scalability, and better data security. You can link to the SQL Server tables with ODBC (see the January 2015 column for information on that) and still create your queries and reports in Access.
The Upsizing Wizard moves database objects and the data they contain from an Access database to a new or existing SQL Server database. You will need to work with your IT department to set up the SQL Server database or gain access to an existing one. Make sure you backup your database by making a copy of it before you do this.
Download this month's database here: SF_JUN_2015.
SF BEST PRACTICES
Your first attempt at importing a file may not work. Try multiple file types, delimiters, and field types to get the best results for your specific situation.
June 2015
