Importing and Exporting Data between Access and Excel

When you work with any sort of external data in relation to Microsoft Access, you have three options.  You can Import, Export or Link.  The difference between Import and Export is simple: you are either importing  data into Access, or exporting data out of Access.  The data you are working with, in this case, is a snapshot. In the case of Importing, once data has entered your Access application from an external data source, any changes made to the original data source, are not reflected in your imported data.  The same principle applies when you export data to an external application - any changes you subsequently make in Access is not reflected in your exported data.  This is in contrast to the option we covered in the last blog post on Linking Access to an External Data Source, where the connection made is 'live'.  Any changes made in Access is reflected in the external application , and any changes made in the external application is reflected in Access.

In this blog post we are specifically going to be looking at Exporting and Importing snapshot data to and from Microsoft Excel.  NB you can also partially link an Access Database to an Excel spreadsheet, but the connection in this case in not completely live - you cannot change data from within the Access Database (which is then reflected in Excel), although data changed within Excel is reflected within Access - in other words, the connection is read-only.

Exporting Access Data to Excel

Let's begin by going through the procedure of Exporting Access data to Excel.  This is a procedure you might go through to export an Access Query or Table to Excel in order to perform further analysis.  We shall be using the Access database from my last blog post which you can download from here.

1. Open the database containing the table to be exported.  
2. Click the name of the table in the NAVIGATION PANE so that it is highlighted in orange.  Alternatively you could actually open the table.
3. Click the EXCEL Icon located in the EXPORT GROUP of the EXTERNAL DATA Ribbon.
   
   
   
   
4. When the EXPORT dialogue box opens, click the BROWSE button and select a location and filename for the newly exported excel spreadsheet.  Then click the EXPORT DATA WITH FORMATTING AND LAYOUT and the OPEN THE DESTINATION FILE AFTER THE EXPORT OPERATION IS COMPLETE check box's.
   
   

   

   Figure 1:  The EXPORT dialogue box for exporting to Microsoft Excel.

   
   
5. Then click the OK button below.  This completes the export process.  The finished result can be seen in the screen shot below:

Figure 2: Table data exported from Access into Excel.

Importing Data into Access from Excel


Now that we have exported our Access Table into Excel, lets have a go at Importing data back into a new Access Table from an Excel spreadsheet.  This is a procedure Access Developers often make when they are convert an Excel spreadsheet to Access.  For sake of convenience, we are going to Import data from the same Excel Spreadsheet which was created when we did our original Export Procedure.

1. Open the Access database into which we are going to Import the data.  You can use the same database as before, because we are going to import the data into a new table.
2. Click the IMPORT EXCEL SPREADSHEET Icon located in the IMPORT GROUP of the EXTERNAL DATA Ribbon.
   
   
3. This opens the GET EXTERNAL DATA dialogue box for Microsoft Excel.  Click the BROWSE button and search for the spreadsheet file to Import.
4. You then have three options.  You can Import the data into a new table, append the data into an existing table, or create a link.  We are going to Import the data into a new table, so click the top check box which says IMPORT THE SOURCE DATA INTO A NEW TABLE IN THE CURRENT DATABASE.  Then click OK.
   
   

   

   Figure 3:  The GET EXTERNAL DATA dialogue box for Importing to Excel.

   
   
5. This opens the first page of the IMPORT SPREADSHEET WIZARD (see screenshot below).  Our spreadsheet contains column headings which can be used as field names, so click the Check Box which says FIRST ROW CONTAINS COLUMN HEADINGS.
   
   
   
   

   

   Figure 4: The first page of the IMPORT SPREADSHEET WIZARD.

   
   
6. Click the NEXT button for the second page of the Import Spreadsheet Wizard.  This is where we are able to specify information about each of the fields we are importing - information such as DATA TYPE, INDEX, and FIELD NAME.  Click the ID column heading so that it is highlighted (if it is not already).  Then change the INDEX to YES(NO DUPLICATES) and the DATA TYPE to LONG INTEGER.  This is so we can use the ID field as the table's primary key.
   
   

   

   Figure 4: Setting the  FIELD OPTIONS in the IMPORT SPREADSHEET WIZARD.

   
   
7. Click NEXT to move to the third page of the Import Spreadsheet Wizard.  This is where we explicitly define the Primary Key for the table.  We already have a suitable column to use as the Primary Key, so we do not need Access to do this for us (the default option).  As such, click the middle Option Box where it says CHOOSE MY OWN PRIMARY KEY.  Then select the ID field from the drop down list (if it is not already selected).
   
   

   

   Figure 5: Defining the Primary Key.

   
   
8. Click NEXT to bring up the final page of the Import Spreadsheet Wizard.  This asks you to enter the name of the table.  Change the default name to tblExcelImport (a new table name that does not already exist) and then click the FINISH button.
   
   

   

   Figure 6: Selecting the new Table Name for the Imported Data.

The table has now been imported and appears in the NAVIGATION PANE of your Access Database. You are now free to open the form and view the records, and may also edit the new table in DESIGN VIEW if you wish.