Access 2010: Import Worksheet From Excel 2010

You can share data between Access 2010 and Excel 2010 in many ways. Excel worksheet consists of cells that are organized into columns and rows, Access  recognize them as fields and records. Access 2010 provides an easy way to import Excel worksheets, this post will explain in detail how to import worksheet in Access from Excel 2010.

Open Excel worksheet, make sure that table is in a list format; each column has a title/label in first row and contains similar data types (similar facts), and there is no blank rows and columns.

For Instance: I have created a work sheet, with columns containing title in it’s respective first row, and left no blank cells.

Excel Sheet

Launch Access, create database. Navigate to External Data tab and click Excel button.

Click external

You will see Get External Data dialog box, it shows three different options of how and to where database will be stored. Go through the options (with details) and select one. We need to create a database in which table will be created by Access automatically.

open import dialog

Hit Browse to select Excel worksheet you want to import and click Open.

import file

Now the Import Worksheet Wizards will open up where you can select the desired worksheet from the list you want to import and click Next. A sample data of selected worksheet will be shown as seen in the screenshot below.

import spreadsheet wizard

Enable the First row Contains Column Headings checkbox. As mentioned earlier, your worksheet must have column’s label in first row, an example can be seen in the screenshot below.


Import spreadsheet headings 1

Now specify information about each of the fields you are importing. Select field/column in the area below and  specify corresponding Field Options. We will select ID field and select Data Type as Integer (number only), and from Indexed drop-down menu we will choose No. You can however also choose Yes (Indexed), or Yes (Not Indexed) from the list, depending on your situation. Click Next.

specify fields (2)

Now choose another field/column to specify Field options.

specify fields enable duplicates

Next step provides different options of selecting primary key (Unique Identifier). You can enable Let Access add primary key, or choose your own primary key by selecting column/field label from drop-down box. We will select the default option, i.e, Let Access add the primary key. Click Next.

Allow primary key

You can enable Save import steps to save the import steps for later use. When done, hit Close.

Save import way

Excel worksheet has been successfully imported in Access 2010, as you can see from the screenshot below.

import success

To check if data types are correctly weaved with column’s label, right-click on imported table (Addictivetips Example) and click Design View. In this view you can apply different constraints over fields and specify data types.

imported data design view

Advertisement
  • Pingback: Access 2010: Export Database Table To Excel 2010

  • D Porter

    I successfully imported (not linked) an excel file to access, and then wanted access to analyse it. I went through all the steps, but in the end access refused to analyse it saying access does not support linking to an excel workbook saved in a format which is a later version than the current database format. Then it closes me down.
    1. I don’t want to link to the excel worksheet: I imported it
    2. I have tried all combinations of Excel and Access 2003 and 2010 to import to and from and still get the same message

    Any help available?

    DP

    • Harmony

      I am having the same problem as above post.
      I need help too! As I am having no luck in making it happen.

  • azhar

    the steps are in a very lucid language and upto the point!!!!!helped me a lot 4 my project!!!!thnx!!

  • Dpm1028

    Is it possible to then import another Excel spreadsheet (with the same format) into the existing Access database but only update the changes and new entries?  Without creating duplicates?  

    • Lipichaks

       did you find out how to do that

  • Babaiaga

    Ok it is good … now a article with an automatic way with a macro … how to do ?

  • 8673juifj

    this is gay

    • Afaf

      NOPE

  • Eyoung

    Good step by step approach.  But what if want to load multiple excel files with the same fields into one Access table?

  • krystyne

    what happens if you just copy and paste the information from access to excel? does the information get all messed up?

  • Jackson

    How to imports excel data into Microsoft visual studio any idea just let me know..???

  • Dee

    How do you create a linked table in Access with an Excel spreadsheet that has the headings in the first column instead of the first row?