Import Records from an Excel File to Access

Suppose that someone has entered additional records for faculty in a different file. Data from spreadsheets and text files can be appended to existing tables in Access from other applications, such as Excel. The key to importing data is that the data must be arranged and organized in tabular (columnar) format, much like an Access table appears. When appending data from an existing spreadsheet, each cell in a spreadsheet column must contain similar types of data, and column labels must match Access field names. A complete spreadsheet or just a named range of cells may be imported.

Next, we'll import additional faculty members' data from an Excel spreadsheet into our Access table.

Step1. To switch to the External Data command tab, on the Ribbon,

Click the External Data command tab

The external data options are displayed.

Step2. To begin importing data from an Excel file, in the Import & Link group,

Click , Point From File, Click Excel

The Get External Data dialog box opens:

Step 3. To find the file to import,

Click

Step4. Navigate to the Access-The Basics folder.

Step5. To select the correct file for import,

Double-Click Faculty_to_import.xlsx

The correct file is now in the File name field.

In the second half of the Get External Data window, we have three options for what we want to do with the data we import. We can import the data into a new table in this database, we can append a copy of the records to an existing table, or we can create a linked table that will refer back to the original source.

We will add these records to our existing table.

Step6. To append the data in this file to our existing table,

Click the "Append a copy of the records to the table:" radio button

Next, we have to select our faculty table. Because we only have one table in our database, tblFaculty, it automatically appears in the drop-down menu. If we had multiple tables, we could use that menu to choose the correct table for our imported data.

Step7. To continue,

Click

The Import Spreadsheet Wizard opens:

We see a preview of our data, which looks very similar to the data in our table. It's essential that the structure of our imported data match the structure of our Access table exactly; that is, the data types must be the same, and the field headers must be precisely the same, or we will get an error from Access.

Step8. To continue,

Click

Notice on this screen that the option "First Row Contains Column Headings" is checked and unable to be deselected. This is how Access will know which fields match up to the destination table.

Step9. To continue,

Click

The last screen shows the checkered flag symbol, which lets us know that we've reached the last step in the wizard.

Step10. Confirm that tblFaculty is displayed in the Import to Table field.

Step11. To finish importing the data,

Click

Access displays the following error message:

This error appears because there is something that doesn't quite match between the imported data and the existing data. Access is very precise, and will not accept the data import if there is a mismatch between field names in the imported data and the destination table.

In this case, Access tells us that the field "Hire Date" does not exist in the destination table. Note that the field name has a space between the words "Hire" and "Date." If we look very carefully in tblFaculty. we see that our field is actually named "HireDate" with no space between the words. This is enough of a difference for Access to reject the data import.

Other common problems that may cause import errors when appending data to an existing table are:

• Data types are not consistent between the external file and the existing Access table. For example, text data may not import correctly into a field in which the data type is set to Number.
• The field size in the Access table is too small to accommodate the field data from the external file. Either the record will not import or the data will be truncated.
• There are duplicate primary key values in some records.
• A field property is set to "required" in Access, but a field value in the external file is blank. The record most likely will not import.
• The number of columns being imported doesn't match up with those in the current database.

We will have to fix this problem before we can proceed.

Step12. To dismiss the error message,

Click

Access now informs us that due to the error, it did not import any data.

Step13. To dismiss the message,

Click

Step14. To cancel the import,

Click

We are returned to the Access database.

Fixing the Excel Table for Import

There are a number of ways we could go about fixing the problem with the mismatch between the "Hire Date" field in our Excel file and the "HireDate" field in our Access table. The simplest way to fix this problem, particularly because there is only one field name that is problematic, is to open the Excel file and correct the column heading, and then redo the import steps.

Step1. To minimize Access, in the upper-right corner of the Access window,

Click

Step2. Navigate to the Access-The Basics folder on your computer.

Step3. To open the Excel file,

Double-Click Faculty_to_import.xlsx

The file opens in Excel.

Step4. To dismiss the Protected View warning, if necessary,

Click Enable Editing

Now, we can edit the cell that caused the import to fail.

Step5. To edit the cell, in the Excel file,

Click the first cell in column J

The cell with the text "Hire Date" will be active.

Step6. To replace the text, type:

HireDate

NOTE: Make sure you type the text precisely, with a capital "H" and a capital "D," but no space between "Hire" and "Date."

Step7. To move away from the cell, on the keyboard, press:

Enter key

Step8. To save the changes, in the Quick Access toolbar,

Click

Step9. To close Excel, in the upper-right corner,

Click

Now, we can maximize Access and try the import steps again.

Re-importing the Excel Data

Now that our HireDate column heading should match the field heading in Access, we should be able to successfully import our Excel data.

Step1. To switch back to the Access window, in the taskbar,

Click

Step2. To open the External Data tab, on the Ribbon, if necessary,

Click the External Data tab

Step3. To begin importing the data again, in the Import & Link group of the Ribbon,

Click , Point From File, Click Excel

Step4. To locate the Excel file, in the Get External Data window,

Click

Step5. Browse to the Access-The Basics folder, if necessary.

Step6. In the File Open window,

Double-Click Faculty_to_import.xlsx

Step7. In the lower part of the Get External Data window,

Click the "Append a copy of the records to the table:" radio button

Step8. Confirm that tblFaculty is displayed in the drop-down menu.

Step9. To accept these settings,

Click

Step10. To confirm that the new column heading is correct, at the bottom of the spreadsheet preview,

Click until the HireDate column is visible

NOTE: If the data preview shows strange characters instead of text, try clicking in an empty section of the scroll bar rather than using the arrows on either end.

Step11. To continue,

Click

Access recognizes that the first row of our Excel table contains our column headings.

Step12. To continue,

Click

Confirm that tblFaculty appears as the destination table below "Import to Table."

Access asks if we would like a wizard to analyze the data, but we will not be taking advantage of this option at this time.

Step13. To finish the import process,

Click

This time, if all has gone well, we do not see an error. Instead, we get confirmation that the import has been completed and the chance to save our import steps:

If we were going to get a new spreadsheet on a regular basis with new faculty names, saving these steps would be a great time saver. For us, this was a one-time operation, so we will not select this option.

Step14. To dismiss the Get External Data window,

Click

In the next section, we'll confirm that our data has successfully imported by opening our faculty table and examining the contents.