How to Import Excel Into Access

Preparing to Import Excel into Access

1. How to Import Excel Into Access Picture 1
   Open both programs on your computer. You will need to purchase and download a Microsoft Office package that includes both Excel^{[1] X Research source} and Access. You can do this online through the Microsoft site.

   1. Once the programs are downloaded, click "Start" in Windows and select "All Programs."^{[2] X Research source}
   2. Click on "Microsoft Office," and then select "Access" (or "Excel") from the drop-down menu. You may already have an Excel spreadsheet created that someone else sent you or that you downloaded from elsewhere. Having Office on your computer allows you to open it.
2. How to Import Excel Into Access Picture 2
   Clean up your Excel spreadsheet before importing it into Access. It will make it easier if you do a few simple steps before you import the Excel spreadsheet into Access. The key is that your data must be consistent between imported spreadsheets.^{[3] X Research source}

   1. It's a good idea to make sure that the first row in the Excel spreadsheet contains your column headers (or field names), and that these are very clear and easy to understand.^{[4] X Research source} For example, for a column containing people's last names, you may want to call the column header/field name "last name." Be clear and precise because it will make it easier when you try to match column headers in one Excel sheet with another.
   2. Access allows you to link common fields between two or among many spreadsheets. Let's say you have an Excel sheet that contains payroll information. It includes people's first and last names, addresses, and salaries. Let's say for the sake of argument that you want to match that sheet within Access to a second Excel sheet that contains information about campaign finance contributions. This second sheet contains people's names, addresses, and donations. What Access allows you to do is to match different column headers with one another. You could link name headers to see which people of the same name appear in both databases, for example.
   3. Scan the Excel sheets to make sure that each type of data is handled the same way, and clean it up before importing it into Access.^{[5] X Research source} It must be what Access calls "relational." For example, if the hypothetical spreadsheet for payroll contained first name, last name and middle initial in one column, but the second spreadsheet contained only first name and last name in separate columns, Access will register this as no match. There needs to be matching column headers/fields.
3. How to Import Excel Into Access Picture 3
   Split information within a column in Excel. To take care of this issue, you may want to split information in a column within Excel, so that it doesn't erroneously register as a "no match" in Access.

   1. As an example, you may want to split the first name into its own column, the middle initial into its own column, and the last name into its own column. If it's already done the same way in the second spreadsheet, when you link, say, last name with last name in Access, it should generate matches when the names are the same.
   2. To split a column in Excel, highlight the column that has information you want to split in it. Click on "data"in the toolbar within the Excel program. Click on "text to columns." Generally, you will choose the option "delimited." Click next.
4. How to Import Excel Into Access Picture 4
   Continue using the wizard to split merged columns. Now you are ready to complete the process of splitting merged information in one column into multiple columns.

   1. Choose how the data within the column is "delimited." This means that each piece of information in the column is separated by something. Most common choices include a space, a comma, or a semi-colon. Often the information is just separated by a space. As in the following example: let's say the name "John A. Doe" appears in a single column. The first name John is separated from the middle initial A by a space. The last name Doe is separated from the middle initial A by a space. So choose space in the delimited wizard.
   2. Click next. Click finish. The program should split John, A., and Doe into three columns. You can then give the new columns new header names to indicate the kind of information housed in them (last name, first name, etc.). It's a good idea to create several blank columns to the right of the data you're splitting before you do it because it will push the data into the new blank columns (instead of columns that already contain information).