Content

• To step
• Part 1 of 3: Importing Excel into Access (preparation)
• Part 2 of 3: Importing Excel into Access
• Part 3 of 3: Using the Wizard to Complete the Import
• Warnings

Access is a relational database manager that allows you to import one or more Excel databases so that you can compare common fields. Since a single Access file can contain multiple Excel spreadsheets, the program is also a great way to collect or analyze large amounts of information. First, however, you need to import the Excel worksheet into Access.

To step

Part 1 of 3: Importing Excel into Access (preparation)

1. Open both programs on your computer. You will need to purchase and download a Microsoft Office suite with both Excel and Access. You can do this online from the Microsoft website.
   • Once the programs have been downloaded, click "Start" in Windows and select "All Programs".
   • Click on "Microsoft Office" and then select "Access" (or "Excel") from the drop-down menu. You may already have an Excel spreadsheet that someone sent you or that you downloaded elsewhere. You can open it with Office on your computer.
2. Clean up your Excel worksheet before importing it into Access. It will be easier if you go through a few simple steps before importing the Excel worksheet into Access. The point is that your data is consistent between imported worksheets.
   • It is a good idea to make sure that the first row in the Excel worksheet contains your column headers (or field names), and that they are very clear and easy to understand. For example, for a column of people's surnames, you can name the column header / field name as "Last Name." Be clear and accurate, as this will make it easier to compare the column headings in two Excel worksheets.
   • In Access you can link corresponding fields between two or more worksheets. Suppose you have an Excel sheet with salary information. It contains people's first and last names, addresses and salaries. For example, suppose you want to compare that worksheet from Access with a second Excel worksheet containing information about the financial contributions for a campaign. This second worksheet contains people's names, addresses, and donations. What you can do with Access is to compare different column headings with each other. You can link name headers to see where people with the same name are in both databases.
   • Check the Excel worksheets to make sure that each type of data is treated the same way, and clean them up before importing it into Access. They must be, as Access calls it, "relational." For example, if the hypothetical salary spreadsheet contains the first name, last name, and middle initial in one column, but the second worksheet only contains the first name and last name in separate columns, Access will register this as no match. The headers / column fields must be the same.
3. Split information in a column in Excel. To solve this problem, you can split data in a column in Excel so that it is not marked as "no match" in Access.
   • For example, you can split the first name into its own column, the middle initial in its own column and the last name in its own column. If this is done in the same way in the second worksheet, then a link between, for example, last name and last name in Access, should generate a match when the names are the same.
   • To split a column in Excel, highlight the column of data you want to split. Click on "Data" in the main Excel menu. Click on "Text to Columns". In general, you choose the option "Divorced". Click on Next.
4. Continue splitting merged columns with the wizard. Now you are ready to complete the process of splitting merged data in one column into multiple columns.
   • Choose how the data in the column is "separated". This means that every bit of information in the column is separated by something. The most common choices are a space, a comma or a semicolon. The information is often only separated by a space. As in the following example: The name "John A. Doe" is displayed in one column. The first name John is separated from the middle first A by a space. The surname Doe is separated from the middle first A by a space. So choose your space as separators in the wizard.
   • Click on Next. Click on Finish. The program splits John, A. and Doe into three columns. You can then give the new columns new header names to indicate what data they contain (last name, first name, etc.). It is a good idea to create several empty columns to the right of the data you are splitting up before doing this, as it will push the data into the new empty columns (instead of columns that already contain data).

Part 2 of 3: Importing Excel into Access

1. Open the Access program on your computer. Go to the start menu, choose Microsoft Office and click on Microsoft Access. You need to open a new empty Access database to import the Excel worksheet into it.
   • Choose "Empty Desktop Database" to create a new database from the Access program.
   • Rename it if you want. Click on "Create".
2. Import an Excel worksheet into Access. The next step is to get the Excel worksheet (or several) in the Access database.
   • Click on "External data" in the toolbar from the Access database manager. Choose "Excel" In some versions of Access, you can find this feature by clicking "File" in the main menu and "Get External Data".
   • When "File name" is displayed, click "Browse". This will allow you to find the Excel worksheet on your computer.
   • Leave the checkbox with "Import the source data into a new table in the current database" checked. This is checked by default.
   • When you find the Excel worksheet you want to import on your computer, click it. Click on "OK". The Excel to Access Import Wizard opens.

Part 3 of 3: Using the Wizard to Complete the Import

1. Follow the steps in the wizard in Access. To finish importing your worksheet, you have to go through the steps in the wizard.
   • Choose the worksheet in the Excel workbook that you want to import. Sometimes this is easy because the Excel worksheet is only one sheet. However, sometimes people create multiple worksheets in one Excel worksheet, which you can view by clicking the tabs at the bottom of the worksheet. If so, you should tell the Access wizard which spreadsheet to choose. Click on Next.
   • The next page has a box asking if the first row in the Excel sheet has column headings. This is the row in a worksheet that identifies the data in each column (such as last name, address, salary, etc.). It is a good idea to clean up the Excel worksheet first to make sure that the first row of column headings has clear column headings. Then check "Yes" that the first row contains column headings. This is the simplest approach. Click on Next.
   • If the first row does not contain column headers, the next window asks if you want to rename the fields in Access (these are the column headings). If you haven't already given each field a clear and easily identifiable name before importing (recommended), you can do so here.
2. Complete the import. There are just a few more steps to go in terms of importing. The next window of the wizard asks if you want to specify a primary key.
   • You don't have to do this, but it is possible. A primary key means that Access assigns each row of data a unique number. This can be useful later when you sort the data. Click on Next.
   • The last screen of the wizard has a place for a default name. You can change the name of the Excel sheet you want to import (it will become a "table" in Access on the left side of the page when you are done importing).
   • Click on "Import". Click on "Close". You will see your table on the left side of the screen. It is now imported into Access.
   • If you want to link to more than one data foundation, repeat this process with another or multiple Excel worksheets. Now you are ready to compare the data in the worksheets in Access.

Warnings

• If the Excel file comes from a version of Office other than Access, you may notice problems importing files smoothly.
• It cannot be underlined enough: you will have to clean up your Excel worksheet before importing it. This partly means analyzing the data you have for problems.
• Always keep a copy of your original spreadsheets so if you mess up you can repeat the process.
• You cannot import more than 255 fields into Access.