No matter what type of data you want to import, you’ll go through the same basic steps. Here’s an overview:
-
In the ribbon’s External Data
Import section, click the button that corre-sponds to the type of file you want to import.When you choose a format, Access launches the Import wizard (Figure 10-4).
-
Enter the name of the file you want to import.
If you don’t remember the file path (or you just don’t want to type it in by hand), then click Browse, and then navigate to the right place in the File Open window. Once you find the file, double-click it.
-
Choose where to place the imported content in your database.
You have three possible choices for placing your data. Depending on the file format you’re using, all these may not be available.
-
Create a new table. This option creates a fresh new table for the data you’re importing, which saves you the headache of worrying about conflicting records. However, if a table of the same name already exists in the Access database, then this option wipes it out.
Figure 10-4. No matter what format you choose, the Import wizard’s more or less the same, although certain options may be restricted. In this first step, you choose the source file name, and the way Access inserts the information into your database. -
Append to an existing table. This option takes the rows you’re importing and adds them to an existing table. In order for this option to work, the structure of the data you’re importing must match the structure of the table you’re using. For example, the field names much match exactly. However, the data you’re importing can leave out fields that aren’t required (Section 4.1) or have default values (Section 4.1.2).
- Create a linked table. If you use this approach, then Access doesn’t actually transfer the information into your database. Instead, every time you view the linked table, Access checks the original file to get the most recent information. The neat thing here’s that your linked table always shows the most recent infor-mation. With any other option, the imported table’s left untouched if you change the original file. However, linked tables are also risky, because you don’t have any guarantee that the file won’t travel to another location on your hard drive (where Access can’t find it).
Note: Linked tables are a good way to bridge the gap between different Access databases or other databases (like SQL Server). However, they don’t work well with other more limited formats, like text files.
-
-
Click OK.
A wizard launches that collects the remaining information that Access needs. If you’re importing an Excel file, then Access asks you which worksheet to use. If you’re importing a text file, then Access asks you how the fields are separated.
-
Answer all questions in the wizard to tell Access what it needs to know about the structure of the data you’re importing.
Once you’re finished with this stage, Access asks you its final question whether or not you want to save your import steps.
-
If you want to perform this import again later on, then select “Save import steps”. Then, click Close.
Note: If Access finds any errors while importing your data, then it creates another table with the same name as the table you’re importing to, with _ImportErrors tacked on the end. Access adds one record to that table for each problem. If you try to import a bunch of information into a table named SalesData, and Access can’t convert the values to the data type you want (for example, there’s text in a column that should only hold numbers), you get a table named SalesData_ImportErrors.
The following sections walk you through the specifics for two common data formats that need a few extra steps: Excel workbooks and text files.
Popularity: 13% [?]
Recent Comments