Importing and exporting data
Access has wizards to guide you through specific operations; these are particularly useful when it comes to importing or exporting data. When you import data, you are making a copy of the original data and storing that data in your database file.
In this exercise, you’ll import data into a database.
Click the External Data tab.
Click Excel in the Import & Export group.
Browse in the File Name box to locate the CompanyNames.xlsx spreadsheet.
Ensure that the first button to Import data is selected, and press OK. The Import Spreadsheet Wizard will now be displayed.
Select the First Row Contains Column Headings check box, and notice how the display changes to remove the column headings from the data records. Click Next.
Click the second URL column, and use the drop-down Data Type to change the data type to a Hyperlink, and then click Next.
On the next screen, Access will display an ID as the primary key (if you wanted a different column to be the primary key, then it could be selected from the Choose my own primary key drop-down list). For this exercise, accept the default choice of ID, and then click Next.
The Import to Table will display the name Companies. You can change the name of the new table at this point. Click Finish.
The very last screen allows you to save these steps if you will need to repeat them. Click Close (you do not need to save the steps).
Double-click the table now shown in the Navigation pane to display your data.
Importing from another Access database
When you import data from another Access database, you can choose to import any of the available design objects. The MSOfficeProData.accdb database contains data that you will need in the following exercise. You will import some of the items in this database into your blank database.
In this exercise, you’ll import data from another Access database.
Click the External Data tab.
Click Access in the Import & Export group.
Locate the MSOfficeProData.accdb file in the File Name box.
Leave the default selection of Import tables, queries, forms active, and click OK.
On the Tables tab, click Select All to highlight all the tables.
Click the Forms tab and click to highlight the frmCustomers form. Then click OK to import all the tables and the selected form.
Click Close when prompted to save the import steps (you do not need to save a record of these steps, because you will not repeat this operation).
You will then have the option to import different Access objects from the database.
Exporting data from an Access database
Access supports the export of data from both tables and queries (which we will discuss in the next chapter). To export data, select the table or query in the Navigation pane and then either use the right-click option to select an export format, or use the appropriate icon on the ribbon.
In this exercise, you’ll export data from an Access database.
In the Navigation pane, click the Customers table.
Click the External Data tab, and click the Text File icon in the Export group.
Use the File name text box to browse to a location to save your exported data.
Do not make any further selections, but click OK to proceed.
It is recommended that you follow through and accept the default selections. More details will be revealed regarding important choices as we proceed through the steps. This wizard, which is similar to the Import Text Wizard, has a number of advanced choices.
On the next wizard page, you can choose between Delimited or Fixed Width, Delimited; which is the default for a more standard choice. An Advanced option (bottom left) allows you very detailed control of how each column is exported. Click Next to proceed.
The next wizard page allows you to change the delimiters used for the export, and you can optionally include field names. Click the box to Include Field Names on First Row.
- Change the Export to file name setting, if required, and then click Finish.
Click Close when prompted to save the export steps (you do not need to save the steps).
This will then display the Text File Export wizard.
You should then have produced an export file.