Locked lesson.

Upgrade

About this lesson

Import data from Excel spreadsheets into tables in Access.

Quick reference

Import Data from Excel

Application Terminology

Import from Excel

Most Access databases start out in Excel.  The data then grows to the point where it is more efficient to work with it in Access.  To import data from Excel, choose Excel from the Import & Link group on the External Data ribbon.  When linking, data is read-only.

When importing from Excel, a Worksheet or Range (Named Range) can be specified.

Be sure to check the "First Row Contains Column Headings" box.  If column headings in Excel contain illegal characters for field names, or they are too long, Access will create ambiguous fieldnames that you can later rename.

If you choose to let Access adds a Primary Key, it will be an AutoNumber, which is a special form of Long Integer.  This is a good idea so you can refer back to original data by storing an ImportID to tables where data is moved.

When naming newly imported tabled, preface the names with "Import_" so they are easier to find.

Select Multiple Records

To select multiple records in Access, Click on the first record.  If you can see the last record you want to delete, you can click and drag in the record selector box.  If you cannot see all the record you want to select, Click on the first record, then scroll, and Shift-Click on the last record. 

Delete Records

To delete one record in Access, you can right-click on the record selector box and choose DELETE from the shortcut menu.  You can also click in the record selector box to select the record then press DELETE on your keyboard.

If you have multiple records selected, press the DELETE key on your keyboard to delete them all at once.

Document Relationships

Printing the Relationships Diagram is a great way to document your database.  Press the Print Screen Key to put an image of your screen on the Windows Clipboard so that you can paste into another Windows application for printing.

Steps

Import from Excel

  1. Click on the External Data ribbon
  2. Choose Excel from the Import & Link group
  3. Browse to the Excel file you want to import from
  4. When the wizard comes up, pick the objects that you want
  5. Open each table and delete blank columns and rows you do not want
Login to download
  • 00:04 This is Lesson 8 of Access 2013.
  • 00:07 Hi, this is Crystal.
  • 00:09 Most Access databases start out in Excel.
  • 00:13 This is an Excel workbook that Joe has been using
  • 00:16 to keep track of the invoices he gives his customers.
  • 00:21 There is a sheet for each customer.
  • 00:24 On each sheet, Joe has the Date that the work was done,
  • 00:28 the amount of the invoice, and the invoice number.
  • 00:32 At the bottom of each Amount column,
  • 00:35 there is a formula for the total amount invoiced.
  • 00:38 We will import these Excel spreadsheets into Access
  • 00:43 so close the file in Excel, and make a note of where it is.
  • 00:49 Each spreadsheet will go into a separate table in Access.
  • 00:56 In a later lesson, we will use Append and Update queries
  • 01:00 to move the data to where it belongs.
  • 01:03 In your lesson folder is an Excel file with this information called
  • 01:07 08_ExcelToImport.xlsx
  • 01:15 Go to the EXTERNAL DATA ribbon in your Access database.
  • 01:19 Here you will find options for importing & linking, and exporting.
  • 01:25 In the Import & Link group, click on the Excel icon.
  • 01:30 Browse to the file and click Open.
  • 01:33 The wizard gives us 3 choices:
  • 01:36 1. import the source data into a new table
  • 01:40 2. append the source data to an existing table, or
  • 01:45 3. link to the data source by creating a linked table.
  • 01:50 The default is to import to a new table,
  • 01:53 which is what we will do.
  • 01:55 Once the file is identified, click OK in the wizard.
  • 02:01 First we will get data from Betty's sheet.
  • 02:04 Since it is the first sheet, it is selected by default. Click Next.
  • 02:09 Access detects that the first row contains
  • 02:13 the field headings and checks the box. This is correct.
  • 02:18 If yours doesn't have the box checked, then check it,
  • 02:21 and click Next.
  • 02:24 Here you can change the data types of fields.
  • 02:27 We will take the defaults and click Next.
  • 02:32 Now Access asks about a primary key.
  • 02:36 Let Access add one.
  • 02:38 It will be an AutoNumber.
  • 02:41 Click Next and Access wants to know what to call the new table.
  • 02:46 Name it Import_Betty and click Finish.
  • 02:52 When done, Close the wizard.
  • 02:55 Open the table to see what came in.
  • 02:57 I see 3 columns: one with a date,
  • 03:01 one with an amount, and one with an invoice number.
  • 03:05 Do this process again to get data on the next sheet.
  • 03:09 External Data, Excel, browse to the Excel file and click Open.
  • 03:15 In the list box with worksheet names,
  • 03:17 choose Charlie and step through the dialog boxes,
  • 03:21 taking all the defaults.
  • 03:23 When it is time to name the new table,
  • 03:26 call it Import_Charlie.
  • 03:31 Close the wizard when done.
  • 03:34 Open the table to see what came in.
  • 03:37 There are several extra rows at the bottom.
  • 03:40 Click in the record selector box of the first unwanted row,
  • 03:44 shift-click on the record selector box for the last unwanted row,
  • 03:49 and press the DELETE key on your keyboard.
  • 03:53 Access tells you how many rows will be deleted
  • 03:56 and gives you a chance to change your mind.
  • 03:59 I choose Yes to really delete them.
  • 04:04 Since there were extra rows on this spreadsheet,
  • 04:07 there might have been extra rows on Betty's spreadsheet too.
  • 04:12 I open Import_Betty
  • 04:15 by double-clicking its name in the Navigation Pane..
  • 04:19 I see there are extra rows here too.
  • 04:22 I click and and drag in the record selector box
  • 04:24 of the last 2 rows, and press DELETE,
  • 04:28 click Yes to really delete them, and close Betty's table.
  • 04:33 I have one more sheet left to get from the Excel file.
  • 04:37 External Data, Excel, Browse to the file,
  • 04:41 and this time pick Dave's sheet.
  • 04:44 Click Next in each step of the
  • 04:46 Import Spreadsheet Wizard dialog box
  • 04:50 and name the new table Import_Dave.
  • 04:55 When done, open the table
  • 04:57 and delete the extra rows at the bottom.
  • 05:01 Because we still need to incorporate this data,
  • 05:05 let's add the import tables to the Relationships diagram.
  • 05:10 Go to the DATABASE TOOLS ribbon
  • 05:12 to open the Relationships Diagram.
  • 05:16 Click on the first table in the Navigation Pane,
  • 05:20 Shift-Click the last table, since they are all next to each other,
  • 05:24 and drag the selection to the Relationships Diagram.
  • 05:30 Resize the fieldlists
  • 05:32 and since there aren't any relationships,
  • 05:34 let's just put them over here to the right.
  • 05:36 Save the Relationships Diagram.
  • 05:40 Document it if you want to by taking a screenshot.
  • 05:43 You can press Print Screen on the keyboard
  • 05:47 to copy the screen to the Clipboard.
  • 05:49 Then you can paste that into Word or PowerPoint,
  • 05:53 crop it, and print it out.
  • 05:55 Printing the Relationships Diagram is a great way
  • 05:58 to document your database as you are building it.
  • 06:02 In part 2 of this course, we will create Append Queries
  • 06:06 to copy data from the imported tables to Joe's Invoice table.
  • 06:11 In the next lesson, we will learn more
  • 06:13 about the Navigation Pane.

Lesson notes are only available for subscribers.

Gift this course