Difference between revisions of "Access 2000 Data Import Guide"

Revision as of 11:19, 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. We also are unable to simply open an Excel file or any other file directly in Access. We have 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