In the last tutorial, you learned how to import data into SQL Server from an excel file. We will do the same but this time, we will be importing the data into Microsoft Access.
Before we begin, you can get a copy of SQL and Data: The step-by-step guide you need in learning how to manipulate and analyze data and information for beginners to become proficient in creating, manipulating data and database objects using SQL in Microsoft Access.
Open Microsoft Access and give it a name then click the Create button. Microsoft Access creates a table named “Table1” by default. You can delete the table. We will create our table during the data import process. Below is a snapshot of the Excel file we want to import:
To import the data, follow the steps below:
1) Press ALT + Q (or click the textbox after the Help menu) and click Import Excel Spreadsheet. You can also import excel data in Access from the External Data menu
2) This opens the Get External Data page
Click the browse button and navigate to the folder where the Excel file is saved. Select it, making sure that the import the source data into a new table in the current database radio button is selected.
3) Click OK
4) A new window will open. This is the Import Spreadsheet Wizard which displays the data to be imported. Since the file contains headings, it is important to check the First row contains column headings (it’s checked by default).
5) Click Next
6) You can give more information about the columns (eg. Index, Data type, etc.)
7) Assign a Primary key to the table. The Primary key is a constraint of the database that makes sure that each record in a table can be identified uniquely. It is a very important concept that aids in adding index (enables faster retrieval of records from the table) in the table.
8) Click Next
9) Type the name of the table in the textbox under the Import to Table heading (See fig 6)
10) Click Finish and then the Close buttons
Access now creates the table and imports the data as needed (see fig 7).
I hope this article helps you to quickly create tables and import spreadsheet data into Microsoft Access. Until then, see you next time.
