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.

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

قالب وردپرس

About Alyse Kalish

Alyse Kalish
As an Associate Editor for The SalesJobInfo, Alyse is proud to prove that yes, English majors can change the world. She calls many places home, including Illinois where she grew up and the small town of Hamilton where she attended Colgate University, but she was born to be a New Yorker. In addition to being an avid writer, Alyse loves to dance, both professionally and while waiting for the subway.

Leave a Reply

Your email address will not be published. Required fields are marked *