How to import a Microsoft Excel spreadsheet (one sheet) to a Microsoft Access table?

By MercedeArvaneh

import a Microsoft Excel spreadsheet (one sheet) to a Microsoft Access table?

Rate: (2 Ratings)

Flag Article

Instructions

Difficulty: Moderate

Things You’ll Need:

Microsoft Excel Spreadsheet file
Microsoft Access Application

Step1

First of all, look at your Microsoft Excel data, you will find several columns, rows and entities. As you know, each row is a record, each column is a field, and each entity is where a record and a field meet. For best result, make sure all the columns (field or vertical list) has a unique, simple, readable (without special characters), meaningful, related (to the topic or subject), smooth (easy to transfer) name or label. Such as Part ID, SKU, Location, Owner, Comment, ...

Step2

Make sure each column has a label (name or address like a home address or Car Tag) in the first row and contains similar facts and there are no bank rows (records) within your Excel Spreadsheet. Also make sure each column has a unique and clear format. (i.e. if the column name is “Date of birth” all the entities must have a unique format such as mm-dd-yyyy or YYYY. Apply a unique and consistent formatting to all your columns.

Step3

Be aware of the differences between “numeric data type” and “alphanumeric data type”. i.e. if you need to have Part ID as a number, make sure the Part ID column has “Numeric Format”, there is a difference between “123” as an alphanumeric value and “123” as a numeric (or integer) value.

Step4

Close the Excel workbook that contains the data you want to use in Microsoft Access application.

Step5

Import Wizard In Access, open the database where you want to import the Excel data.
On the Access file menu, point to Get External Data, and then click Import.
In the Import dialog box, click Microsoft Excel in the Files of type box.
In the Look in list, locate the file you want to import, and then double-click the file. Follow the directions in the Import Spreadsheet Wizard.

Step6

First row Check! First row contains column heading

Step7

location of imported file Location! Location! Location!
Where would you like to store your data? in a new table or as an existing one

Step8

Primary Key Add Primary key (or leave it alone)

Step9

Finish Choose a name and Finish.

Tips & Warnings

Learn how to use wizard as your assistant.
Choose meaningful names for your source file (Excel) and destination file (Access)
The default name for Excel spreadsheet is "sheet1", rename it to a more useful name.