Difference between revisions of "Access 2000 Data Import Guide"

Revision as of 11:45, 9 January 2008

This is a basic "how to" guide on importing data into Microsoft Access 2000.

Import Excel File Into Access 2000

Create a New Database in Access

The first step is to create a new, empty database. Unlike a spreadsheet, where you can launch the program and immediately start entering data, in Access you must create an empty database first before you can enter or import data. You can think of this empty database as a container that is going to hold all of your information. You also are unable to simply open an Excel file or any other file directly in Access. Your options are to import the file into a new or existing database. Once you open Access, depending on which version you are using, either click on New Blank Database in the menu on the right, or go to the File menu and select New Database.

You will be prompted to name your new database file and navigate to a folder for storing it. Microsoft Access databases have the file extension mdb.

Once you create the database you will be presented with the database screen. By default you are in the Tables section, which is naturally blank, as you have not added any data yet. The top of the database window shows the name of the database.

Next to the name of the database is the Access file format version. By default, the version of Access that you are using defaults to the file format used by the previous version, to help insure backward compatibility between your datasets.

Prepare Excel Data For Import

It is necessary to prepare the dataset for import. In a database, and unlike a spreadsheet, all data in a particular column must be of the same type; numbers, text, dates, etc. If there is a column of numbers, then this column must contain numbers and no text whatsoever. Numbers can be stored as text in a database, but you will not be able to perform any calculations on these columns. Generally speaking, identification numbers should be saved as text, and numbers that you would perform mathematical calculations on should be stored as numbers. So the first step is to examine each of columns to verify that they contain the same data type, and make edits where appropriate.

Columns in a database can only have one field heading or name. The first row is recognized as the header row, and all subsequent rows are recognized as data. If there happens to be two header rows in the Excel spreadsheet, you may have to delete the second header row, or cut and paste labels into the first header row. Typically it is best to keep the first header row and delete the second. You also need to check to make sure header names are not too long so that they don't exceed the database’s length limit for field names. They also must not contain spaces, which is something that is not allowed in field names. Field names should never contain strange punctuation, and should never begin with numbers.

After making changes save the Excel spreadsheet, close it, and go back to the Access database.

Import Excel Data into Access Database

Go to the File menu, and select Get External Data, then Import. Now you must select the file to import. By default, Access is set to import .mdb files, which are other Access databases. Since the goal is to import an Excel spreadsheet file, change the file type dropdown to Excel. Navigate to the directory where the Excel file is stored and select it.

* FILE, GET EXTERNAM DATA, IMPORT

If the Excel file contains multiple sheets, you will be prompted to specify which sheet to import. Otherwise, Access will select the only sheet if there is one. Now there are other options Access will question you for. The first question is whether the first column in our spreadsheet contains the column headings. These prompts or questions being asked by Access are dependant on the file type being imported. The questions you are seeing are specific to importing an Excel spreadsheet file. If importing a file of a different type, the series of questions may be different from these.

You will be asked if you want to change any of the field names, data types for the fields, or if you want to exclude particular fields. The simplest thing to do here is to accept the defaults and click Next, as you can always make changes to these options later, once the data is imported.

Now you are prompted to specify a primary key, which is a number or code that uniquely identifies each individual record. You may choose not to set one at this time, and do it later, after the import. If you know which column you want to use as the primary key, then select it. The primary key should be a column of unique data.

Now select a name for the new database table. Access now begins importing the spreadsheet data into the new Access database table. The time it takes for Access to do this depends on the speed of your computer and the amount of data in the spreadsheet. When the process is complete, you end up back at the table objects window, and now have one object in this window.

You may now double click the table to open it in what is called a Datasheet View. In this view you can see the data and total number of records. You may also use another view called the Design View to make any alterations in the table structure or data types.

Remember, you use the Datasheet View to modify the data itself or add new data.

You use the Design View to make changes to the table structure or data types.