Access is a database manager interface that allows users to import one or more parts of an Excel database into the system so that they can find matches between or within their fields (fields). Because a single Access file can contain multiple Excel spreadsheets, this program is also great for collating or analyzing large amounts of Excel spreadsheet information into Access. You just need to do a few key steps.
Step
Part 1 of 3: Preparing to Import Excel into Access
Step 1. Open both programs to the computer
You will need to purchase and download a Microsoft Office plan that includes Excel and Access. You can only do this through the Microsoft website.
- After the program has finished downloading, click "Start" in Windows and select "All Programs".
- Click "Microsoft Office," and select "Access" (or "Excel") from the drop-down menu. Perhaps the Excel spreadsheet was emailed or downloaded from a site. The file will open an Office program on your computer.
Step 2. Clean up the Excel spreadsheet before importing it into Access
To make things easier for you, we recommend doing a few simple steps before importing the Excel file into Access. The key is that the data between the spreadsheets to be imported must be consistent.
- It's a good idea to make sure the first row of the Excel spreadsheet contains the column headings (or cell names), and can be understood clearly and easily. For example, for a column containing people's last names, we recommend that you title the column "Last Name". Provide clear and accurate headings to make it easier to match the column headings of one Excel sheet to another.
- Access lets you link two or more fields between multiple worksheets. Say you have an Excel worksheet that contains payroll information. This spreadsheet contains the person's first and last name, address, and salary amount. Say you want to match a sheet in Access to a second Excel sheet that contains information regarding a financial contribution campaign. This second sheet contains the person's name, address, and donation. Access lets you match one column heading to another. For example, you can link Name Titles to see people with the same name appearing in both databases.
- Scan the Excel sheet to make sure each type of data is handled the same way, and clean it up before importing it into Access. This is what is called a “relational” in Access. For example, if your payroll spreadsheet contains first, last, and middle initial names in one column, but the second spreadsheet contains only first names in separate columns, Access will show no matches. Column/field headings must match exactly.
Step 3. Divide the information in columns in Excel
To work around this problem, the information needs to be split in columns in Excel so that it doesn't result in " no match " in Access.
- For example, you can divide first name, middle initial, and last name so that they each have their own column. If the second worksheet has been structured the same way, when you link, say, last name with last name in Access, match results appear for the same names.
- To split a column in Excel, highlight the column that has the information you want to split. Click the "data" data on the toolbar in the Excel program. Click "text to columns". Usually, you will choose the " delimited " option. Click next (next).
Step 4. Continue using the wizard to split the converging columns
Now you are ready to execute the process of dividing the information that is concatenated in one column into several columns.
- Choose how to “delimit” the data in the column. That is, each piece of information in a column is separated by something. Usually, this separator is a space, comma, or semicolon. Often this information is simply space-separated. For example in the following example, say the name "Budi A. Dodo" appears in one column. First name (Budi) and initials A. are separated by spaces. Dodo's last name and initials A. are also space-separated. So, choose a space in the delimited wizard.
- Click next, then finish (finished). The program will separate Budi, A., and Dodo into three columns. You can then give these new columns new titles to indicate the type of information in them (Last Name, First Name, etc.) It is best to first create a few blank columns to the right of the split data so that the data is pushed to a new blank column (instead of -instead of overwriting columns that already contain information).
Part 2 of 3: Importing Excel into Access
Step 1. Open the Access program on your computer
Go to the computer program menu. go to the Start menu, select Microsoft Office, and click Microsoft Access. You need to open a new blank Access database to be able to import Excel.
- Select "blank desktop database" to create a new database in the Access program.
- Give it a new name if you want. Click "create" (create).
Step 2. Import the Excel spreadsheet into Access
The next step is to drag the Excel spreadsheet (one or more) into the Access database.
- Click "External Data" in the toolbar once in Access database manager. Select "Excel". In some versions of Access, this function can be found by clicking "file" in the toolbar and "Get External Data".
- When " file name " appears, click " browse " to find the Excel sheet on your computer.
- Keep the box that says "import the source data into a new table in the current database" unchecked. This setting is preset by default.
- When you find the spreadsheet that you want to import to your computer, click on it. Click "OK". This step will take you to the wizard for importing Excel into Access.
Part 3 of 3: Using the Wizard to complete the Import Process
Step 1. Go through the steps in the wizard that appears in Access
To complete the spreadsheet import process, complete the steps in the wizard.
- Select the worksheet in the Excel spreadsheet that you want to import. However, sometimes people create multiple pages in one Excel spreadsheet, which can be viewed by clicking the labels at the bottom of the spreadsheet. If so, you need to tell the Access wizard the selected worksheet. Click next.
- The next page has a box that asks if the first row of the Excel sheet has column headings. This means that the rows in the spreadsheet that represent the data in each column (e.g. last name, address, salary, etc.) should be cleaned first to make sure the first row is the column heading. Then, check “yes that the first row contains column headings” (yes, the first row does contain column headings). This is the simplest way. Click next.
- If your first row contains no column headings, the next page will ask if you want to name what are called " fields " in Access (these are column headings). If you didn't fill each of these fields with clear and easy-to-remember names before importing (recommended), you do so here.
Step 2. Complete the import process
There are a few steps left in the importing process. The next page of the wizard will ask if you want to identify the primary key.
- This step is not required, but can be done. The primary key means that the computer program will assign a unique number to each line of information. This step can be helpful later when sorting data. Click next.
- The end screen in the wizard has a space containing the initial name. You can rename the imported Excel sheet (the file will become a “table” in Access on the left side of the page when it finishes importing).
- Click " import ", then click " close ". You will see a table on the left side of the screen. The sheet is now imported into Access.
- If you want to link more than one data set, repeat this process with one or more other spreadsheets. Now, you are ready to match the data on the sheet in Access.
Warning
- If the Excel file is from a version of Office other than Access, the import process might be a little difficult.
- Again we emphasize that you need to clean up the spreadsheet before importing it. One of the reasons is to analyze the existence of problems in the data.
- Always keep a copy of the original spreadsheet so this process doesn't have to be repeated if you make a mistake.
- You cannot import more than 255 cells/boxes into Access.
