Importing Data from Another Application into MS Access
If you are switching to Access after keeping your data in another application, you may find the idea of retyping your data rather daunting.
Fortunately, you rarely need to retype since you can either copy the data to the clipboard and paste it into Access, or use Access’s nifty import feature In general you will want to use the import feature if you are creating a brand new table from imported data, or if you are importing a lot of data.
You can import data from a variety of sources including other Access databases, and the files created by popular spreadsheet and database applications.
The following simulation below the diagram takes you through importing data to create a new MS Access table.
Columns consisting of 1) Invoice number, 2)Company, 3)Amount, 4)Date Sent, 5) Check received
spreadsheet that contains the data you want in your Access table:
Import Data from Excel Spreadsheet into MS Access Table
This is the database window of the Consulting database. In this simulation, you are going to create a new table with the data stored in the Invoices spreadsheet. To begin,choose File>>Get External Data from the menu.
The Import dialog box helps you find the file containing the data you want to import. The first step is to make sure you are looking at the write type of files. You want to import data from an Excel spreadsheet. Click the Files of type option and choose Microsoft Excel which have a file extension of .xls
Now you see the Excel files in the selected folder. The file you want to import data from is Invoices.xls.
Here's the first window of the Import Spreadsheet Wizard. If you choose a different file type, you will see different options in the wizard that Access uses to import the data. This window asks you to choose the sheet that your data is on, the data is on Sheet 1, so you do not need to make a change there.
The bottom half of the window shows the data in the worksheet. You do not need to do anything on this window.
Click Next to see the next window of the wizard.
This window highlights the data that will be imported. Access has figured out that the first row of the worksheet contains labels, which now appear on a gray background. The option in the top of the window, First Row Contains Column Headings, is selected. Click Next to see the next window of the wizard.
This window of the wizard asks you where you want to put the data, in a new table or in an existing table. Since this data does not fit in an existing table import it to a new table. Since that option is already selected you can [[following text in bold blue courier]] click the Next button to go to the next window.
This window of the wizard allows you to change the properties of the fields you are creating. The Field Name option shows the text used for column label in the spreadsheet. (Notice that one column of data is selected in the bottom half of the window.) If you wanted to, you could
change the name of the field. However, the names in the spreadsheet make perfectly good field names, so there is no need to change them in this case. You can work with other columns by clicking the column in the bottom half of the window. Since the column labels in the spreadsheet make good field names, click Next to see the next window.
This window asks you which field you want to be the primary key for the table. You will learn more about the primary key later in this course. For now, let Access create a new field to be the primary key, it is the first column shown at the bottom of the window.
Click Next to see the next window.
Finally, the last window of the wizard! All you need to do here is give your new table a name. Type “Invoices” in the Import to Table box to rename the table and click the Finish button.
Access tells you the data has been imported. Click the OK button to close the wizard.
You see the database window with the new table listed. Double-click the new table, Invoices, to see its contents.
Here is your imported data, which is easier than retyping.
I have altered the column widths so that you can see all the data.
Importing Data from Application - Exercise
In this exercise, your database is still short a little data you need to import some additional data from another application. Importing Data from Application - Exercise
