Quick way to Import Multiple Excel Files into MS Access

Recently, I had requested a big size of sales/inventory data from one of our internal team. While the data was available at a reasonable level of detail, it was too big to come in one spreadsheet. So, the concerned data operator gave me the data in multiple spreadsheets (MS Excel) with each spreadsheet of significant file size running into 100s of MBs. When I went through the data, I realised I might have to add some helper columns to add some more details – but doing so in each and every spreadsheet was going to be too time consuming. So, I started thinking of importing these multiple files into a single table of MS Access, then run queries as per my needs. Quick way to Import Multiple Excel Files into MS Access

In this post, you’ll learn about this in-built Macro function in MS Access to import data known as “ImportExportSpreadsheet”.

Business Scenario

Let’s say you have received a set of data in 25 different spreadsheets. Each spreadsheet has say 26 fields. Now, instead of working on each of the 25 sheets individually, you want to import them into a single Access file. This way you would then have to work on just one big set of data.

So, one way of doing that was to import the data using the wizard under the “External Data” tab of the application but soon I realised that it was too slow. Further, I did not have much time or inclination to write a macro code to facilitate this import (major reason – I didn’t know how to!).

So, finally I came across a built-in Macro function in MS access that helped me to import the file. It was significantly faster than the wizard and probably slower than any code – but at least I was not required to know or write any code.

So, lets get started with the steps required to start using this function to import data.

Step 1 – Create Empty Destination Table

Step 1: Let say you have received the following sample data:

Quick way to Import Multiple Excel Files into MS Access

Examine the files and list down the columns as well as the data type of the data under each field.

Based on the data provided, list down the columns and the data types separately in a notepad/excel. This is important since you are going to need these column names and datatypes while creating a new empty table in MS Access.

Quick way to Import Multiple Excel Files into MS Access

 

Background work:

Just to be on the safer side, you need to make note of the following things (these points will serve as checkpoints to see whether or not data transfer has taken place correctly):

  1. Make a note of the number of records of data in each of the spreadsheet (in this case 25). So, if there are n-records in each file, the final access table should have 25n records.
  2. It is advisable to check whether a particular field in each of the 25 sheets holds the data of the same datatype. For e.g.: values in Date_Field in 24 sheets could be of the type date but in the 25th file it might be stored as a text. Such differences can introduce errors while importing the data. You just have to ensure that the data that you are importing is uniform.

Once you have examined and confirmed the file structure, fields and the datatypes of each field, you will now create an empty Access table as follows:

  1. You can either create it using a GUI (i.e. create query in query design grid) – but that would be a bit tedious given that you need to create 26 fields.
  2. Instead you will create a SQL query as follows using Create “Query Design” function. On clicking Create Query, the query design grid will open. You need to again right click on the open query grid (as shown) and switch to SQL view.

Quick way to Import Multiple Excel Files into MS Access
In the SQL view, then type the above code. The SQL windows will now look as follows:

Quick way to Import Multiple Excel Files into MS Access

Before hitting the Run Option, make sure you first Save the query. On clicking Run, you’ll find that a new Table has been created.

Quick way to Import Multiple Excel Files into MS Access

Before you start the data transfer, do a quick check to see if the final table created is as expected. As can be seen from above, the table has indeed been created as expected. 

Step 2 – Setup Data Import Macro

Now that you have created the empty table, you’ll start the data import process.

Go to Create and Click on “Macro” under “Macros & Code”.

Quick way to Import Multiple Excel Files into MS Access

A new screen will pop-up as shown below:

Under the “Design” tab of the new screen, you’ll find that “Action Catalog” is activated by default. In addition to that, you also have to activate “Show All Actions” by clicking on it once.

After doing so, on the right hand side “Action Catalog” window, locate the option “ImportExportSpreadsheet” and double click on it. (Note: If “Show All Actions” is not clicked, you won’t find the option “ImportExportSpreadsheet”.)

Quick way to Import Multiple Excel Files into MS Access

Once you can see the “ImportExportSpreadsheet”, you need to double click it. After double clicking it, you’ll see a small pop-up in the new screen that will ask you to provide the following details:

Quick way to Import Multiple Excel Files into MS Access

Transfer Type

You need to specify whether you need to Import or Export. In this example, you’ll transferring data from multiple excel sheets into ACCESS. Hence, you’ll choose Import.

Spreadsheet Type

Select “Excel Workbook”.

Table Name

Specify the name of the new empty Table Created in Step 1.

File Name

Specify the complete pathname of the file that you want to import along with the filename with its extension.

Has Field Names

In this example, each of the 25 files has field names. So, choose “YES”.

Range

Since you want to import the entire data, leave this blank.

Note that, since you need to import 25 different files into the Access table, you can go about it in the following two ways:

  1. As described above, you first need to specify the filename of the first file, save the Macro and then Click Run. Likewise, you need to do for all the 25 files – just edit the filename and click run. In this case, you’ll be clicking RUN option 25 times. But this is not advisable. I’d rather give instructions to import 25 files only once and then have myself click “Run” just once every time I need to import the 25 files. 
  2. Under the new Macro Window, you’ll click “ImportExportSpreadsheet” 25 times so that you now have 25 windows open within the Macro Screen. Or Copy the first windows (Ctrl+C) and then (Ctrl+V) 24 times. Here, you can also specify which file should be imported first. In this case, you’ll have to Run the query only once.

Quick way to Import Multiple Excel Files into MS Access

On Clicking save, you’ll see that the macro has been created. If you want to edit the macro in future, you can do so by right clicking it and by choosing the design view (just like you do for any query or table).   Quick way to Import Multiple Excel Files into MS Access

Just before you Click Run, you can add a custom message to notify you when the data import is complete. It can be done as follows:

Quick way to Import Multiple Excel Files into MS Access

Save the Macro and name it as you like. Then Click on Run and the data transfer/import should begin.

Once the data transfer is complete, the customised message will pop-up indicating that the process is complete.

Quick way to Import Multiple Excel Files into MS Access

 

And once the data transfer is complete, you can go to the table to do a quick sense check.

Quick way to Import Multiple Excel Files into MS Access

Alert: Error & Exceptions

Now, here’s a homework for you guys.

While importing the data, it does happen that you might set a field to a particular datatype but the the data that is actually getting imported might be of a different datatype in few rows – either due to some error or an oversight in the data source.

E.g.: Let’s say you have a column “StorageBinNo” which is set as a “Text” datatype. And in few rows of that field there are numbers or dates present in the source data. So, while importing the data, MS Access will try to convert these number and dates and store them as text.

However, in some cases, MS access will not be able to successfully convert data types into a datatype that the field is set to in the table. A quick example would be  trying to import “Text” in a field set as “Date”. The tool will not able to import text and save it as  a date.

In such cases, after the import is complete, Access will create a table titled “Field1$_ImportErrors” and it would specify the Error (i.e. Type Conversion Error), Field name in which the error is encountered and the row number.

In conclusion, be mindful if there are any errors in the data source. These will be handled in the following two ways:

  1. Access will indicate errors by listing the errors in an another table. The cell that it shows has errors will contain no data.
  2. There will be no visible notification but there can be critical data loss. This is one thing that you need to be extremely mindful of while running data imports.

Given that there are following most commonly datatypes used in MS Access, I would want you to experiment a bit and try to import different datatypes into fields of some other datatype.  So, create a field of “Currency” Datatype and try importing text, long, date etc. and share your observations with everyone here in the comments section.

  1. Memo
  2. Byte
  3. Integer
  4. Long
  5. Single
  6. Double
  7. Currency
  8. Date/Time

Warning!

Importing data to Access may not be as straight-forward as it looks. On the contrary, it could be risky if you are not careful enough with the data types. Sometimes, you may not even get an error though the data transfer has not occurred as you expected. Keep an open eye for such fields that contain multiple data types.

Summary

In this way you can import multiple excel files into a single Access Table. Please try to run this macro on you own and let our readers know whether you face any problems. Also, keen to know whether this method is helpful or whether you still prefer using the data import wizard.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s