Importing Data From Excel
IIHE  |  Education  |  Getting Started With Microsoft Access  |  Importing Data From Excel

Sometimes data needs to be imported from Excel. Many people start their databases in Excel and later convert them over to Access.

Appending Data Using Copy and Paste

There are two ways that you can import data from Excel. If the table is already defined, you can import data into Access using a modified form of copy and paste:

• Open the spreadsheet in Excel
• Highlight the cells that you want to import into Access. The fields must be in the same order as in the Access table. Also, don't include the field headings in the selection:

• Copy the data to the clipboard by pressing Control C or choosing Copy from the Edit menu.
• Open Access (or bring it to the foreground).
• Open the table that you want to add the data to.
• Choose Paste Append from the Edit menu:

• You may get other messages first if your data doesn't conform to the table's data. You might want to look over your table and spreadsheet before importing the data and check the order of the fields, that there are no field names at the beginning of the selection, and the fields contain the same type of data.
• If you are satisfied that the data is correct, click on the Yes button and the data will be added to the table.
  

Importing Excel Files

The second way of importing data into Access from Excel is to import the data directly from an Excel spreadsheet:

• In Excel, save a spreadsheet with the data you want to import into Access. If the spreadsheet has more data in it than just data to import, you can name the range of cells containing the data or you can delete any rows or columns that provide additional information that you don't want in Access.
• Open Access (or bring it to the foreground).
• If you're planning on adding the data to an existing table, make sure that the table is closed.
• From the Get External Data in the File menu, choose Import...

• The standard File Open dialog comes up. Click on the triangle at the end of the line beginning with Save as type:

• From the pop-up menu, choose Excel.
• Now locate the spreadsheet containing the data that you want to import and click on the Import Button:

• Here, Access should the names of the sheets in the spreadsheet. Also, you can import a Named Range of cells. In Excel, you do this by highlighting the group of cells that you want to name, clicking in the Name box (highlighted in the picture below) to the left of the formula bar, and typing the range name.

• After selecting the Sheet or Named Range, you see:

• Basically, all this is asking is if the cells have the field names in the top row. After clicking Next, you are asked:

• If you are importing the data into a new table, click the In a New Table button.
• If you want to import into an existing table, click on the In an Existing Table button and then select the table you want to import it to from the pop-up menu. Once you click the Next button, you are asked:

• Here you confirm that you do want to import data into the table by clicking on the Finish button.

Finally, the computer goes through the spreadsheet and import the records.

Linking Tables in a Query  |  Getting Started With Microsoft Access  |  Exporting Access Data to Excel