Content

• Steps
• Part 1 of 3: Prepare to Import Data from Excel into Access
• Part 2 of 3: Import data from Excel into Access
• Part 3 of 3: Using the Data Import Wizard
• Warnings

Access is a relational database management system into which you can import one or more Excel spreadsheets and reconcile their homogeneous elements. Moreover, with Access, you can collect and analyze a large amount of data, since one Access file includes several Excel tables. But first, you need to import data from Excel into Access; this can be done by following a few basic steps.

Steps

Part 1 of 3: Prepare to Import Data from Excel into Access

1. 1 Start Excel and Access on your computer. You need to buy and download a Microsoft Office package that contains both Excel and Access. This can be done on the official Microsoft website.
   • After installing Microsoft Office, in Windows click "Start" - "All Programs".
   • Click "Microsoft Office" and select "Access" (or "Excel") from the menu that opens. Most likely, you already have an Excel spreadsheet that you downloaded or received by email. With Excel, you can open such a table.
2. 2 Process the Excel table before importing the data into Access. This will greatly facilitate the task of transferring data from Excel to Access.The bottom line is that in different tables, certain data must have the same format.
   • First, make sure that the first row of each imported table contains the column names (headers) - the names should clearly describe the data entered in the columns. For example, if a column contains people's last names, name it Last Names. Name it accurately to make it easier to reconcile the column headings in different tables.
   • In Access, you can reconcile similar items in two or more Excel tables. For example, you have an Excel spreadsheet with salary data that contains your full name (last name, first name, patronymic), addresses, and salary amounts. Let's say you want to compare this table with another table that includes data on donations for a certain project (name, addresses and donation amounts). In Access, you can make column headings consistent across tables. In our example, match the column names with the last names to find out which people are in both tables.
   • Review each Excel spreadsheet and make sure the data is in the same format; otherwise, process the table to bring the data to the same format. This approach to data format reflects the word "relational" (from relation) in the description of Access. For example, if last names, first names and patronymic names are entered in the "Full Name" column in the salary table, and only last names and first names are entered in the "Full Name" column in the table of donations, then Access does not consider these columns homogeneous (that is, it will not be able to match them). Therefore, both the names of the columns and the format of the data contained in these columns must be the same.
3. 3 Divide the data in the columns of the Excel table. To make the elements of the Excel spreadsheet homogeneous (for importing them into Access), separate the information in the appropriate columns.
   • For example, you could put the last name in one column, first name in the second, and middle name in the third. Do the same with the corresponding column in the second table. Now in Access, you can match, for example, last names from one table with last names from another, and find people who appear in both tables.
   • To split data in an Excel column, select the desired column. On the toolbar, click Data. Then click Text by Columns. It is recommended to select the "Delimited" option. Then click Next.
4. 4 To separate data located in one column, follow the prompts of the Text Wizard. Follow these steps:
   • Select a separator character between data. Remember that the information entered in the cell is separated by some kind of symbol. Typically, this separator character is a space, comma, or semicolon. In the vast majority of cases, information is separated by a space. For example, the following information is entered in a cell: Ivanov Ivan Ivanovich. Here the Surname is separated from the first name by a space, and the first name is also separated from the patronymic by a space. In this case, select the "Space" option in the Text Wizard window.
   • Click Next. Then click Finish. The column with the cell "Ivanov Ivan Ivanovich" will be divided into three columns. Now you can assign a name to each of the three new columns, that is, name them as "Surname", "First name", "Patronymic". Before splitting the information to the right of the split column, insert some blank columns so that Excel will fill in the split data (and not in the columns with other data that are already filled in).

Part 2 of 3: Import data from Excel into Access

1. 1 Start Access. To do this, click "Start" - "Microsoft Office" - "Microsoft Access". To import data from Excel, create a new Access database.
   • To create a database, in the Access window, click New Database.
   • If you want, give the created database a name. Then click "Create".
2. 2 Import your Excel spreadsheet into Access. You can now import data from one or more Excel spreadsheets into Access.
   • On the toolbar (in the Access window), click External Data.In some versions of Access, on the toolbar, click File - External Data.
   • Under File Name, click Browse to find the Excel table you want.
   • Check the box next to "Import raw data into a new table in the current database" (this option is enabled by default).
   • When you find the table you want, click on it. Then click "OK". The Import Data from Excel to Access Wizard opens.

Part 3 of 3: Using the Data Import Wizard

1. 1 Follow the instructions of the Data Import Wizard. You must do this to complete the import process of the Excel spreadsheet into Access.
   • Select the sheet in the Excel spreadsheet whose data you want to import into Access. Sometimes this is very easy, because the table contains only one sheet. But sometimes one Excel spreadsheet includes many sheets, the tabs of which appear at the bottom of the Excel window; in this case, you need to specify a specific sheet. Then click Next.
   • A window appears asking if the first row of the table contains column headings. This refers to the names that characterize the data in each column (for example, last name, address, salary, and so on). It is great if you have previously processed the Excel spreadsheet so that the first row contains strictly defined column names; in this case, check the option that the first row contains column headings. Then click Next.
   • If there are no column headings in the first row, a window will open asking you if you want to name the "fields" (in Access, "fields" are column headings). If you haven't entered column names before, do so now.
2. 2 Finish importing data. There are only a few steps left to complete. In the window that opens, define the primary key (if you like).
   • You can, but you don't have to, do this. The primary key is a unique number assigned to each row of data; this can be useful when sorting data. Then click Next.
   • The last window displays the default name. You can rename the Excel table (when the import is complete, it appears as an Access table on the left side of the screen).
   • Click Import and then click Close. The table will be displayed on the left side of the screen; this means that it is fully imported into Access.
   • If you want to reconcile multiple databases, follow the steps above to import one or more additional Excel tables. You can now begin to reconcile your data in Access.

Warnings

• Keep in mind that if the version of Excel in which the table was created is different from the version of Access, you may run into problems importing the data.
• To reiterate, process the Excel spreadsheet before importing the data. That is, pay attention to the format of the data with which you are going to work.
• Make a copy of the original table so as a last resort you can import the data again.
• Access can import a maximum of 255 columns.