Creating a new table using an Excel file
As I became familiar with Access, I was very pleased to know that
you could import existing information to use in Access. This saved
retyping and editing time. In most cases, it is easier to use Access
to store, retrieve, manipulate, and report data than other applications.
This article will explain, in a step-by-step format, how to create
a table by importing an existing Excel file.
Importing from Excel (.xls)
Prior to importing from Excel it is necessary to eliminate all of
the formatting in the file. This includes, but is not limited to,
centering, bold, underlining, etc. Also, ensure that there are no
spaces in front of the column headings. By eliminating the spaces,
you prevent importing errors and column heading issues so Access can
recognize the headings. Below, you have the step-by-step guidelines
for creating an Access table using an Excel file.
- Save then close the .xls file.
- Next, go to "New" on the "Tables" tab.
- Click on "import table."
- Change the "files of type" to Microsoft Excel.
- Navigate to the "Excel" file that you would like to
import.
- Select the worksheet or named ranges you would like to import.
- In the bottom half of the "Import Spreadsheet Wizard"
dialog box, you will see the information that will be imported.
- Click "Next."
- If you have put the column headings in the Excel file, they are
transferred to Access. If the first row of your data contains the
column headings, check the box "First Row Contains Column Headings".
If your first row of information does not contain column headings,
do not check the box.
- Click "Next". You will see that the first row of your
data is grey and will be the column heading for your table.
- To create a new table based on the data you are importing, click
on "In a New Table". If you would like to import the information
into an existing table, click on "In an Existing Table"
and use the drop-down box to select the table name.
Please understand that you will be adding
the information to an existing table daily. Please ensure that you
want to do that. Instead, you will be taken straight to the last
step of the wizard to click on "Finish."
- Click "Next."
- Select the indexing options for each field by clicking on the
field heading to highlight the column then go to the "Indexed"
field drop-down box and determine whether or not you would like
to allow duplicate values (allows fields on different rows to contain
the same information), to not allow duplicate values (fields on
different rows must contain unique information), to not index the
field.
Indexing the field allows the database to
run faster. If you do not want to import a column, click on the
column heading to highlight the entire column, then check "Do
not import field (Skip)." You can adjust the data types after
the table has been imported.
- Click "Next."
- At this point, decide if you want Access to add a primary key,
if you want to choose your own primary key, or if you do not want
a primary key. If Access creates a primary key, it will create an
ID field which numbers your records. If you choose your own primary
key, select a column that does not contain duplicate fields. For
example, an invoice number field cannot contain invoice 12345678
twice in the same column.
- Click "Next."
- Type the name of your table into the "Import to Table:"
field. If you do not want to overwrite an existing table, choose
a name that is not currently being used.
- Click "Finish."
- Once you have successfully imported the file, a dialog box will
display that reads: "Finished importing file [filepath] to
table [table name]."
You can go to the table’s design view to format the data types,
field sizes, create drop-down boxes, index fields, etc.