Creating an Import or Export Specification

The process of creating an import specification for importing a fixed-width text file is described next; you create specifications for importing a comma-delimited text file, or exporting either of those file types, in a similar manner, with different options depending on the file type. You might want to use an import specification, for example, if you receive a text file of comma-delimited data downloaded from a mainframe every week, and you need to import the weekly data into an Access table.

1. First, click the Text File button on the External Data menu, as shown in Figure 10.1.

FIGURE 10.1

Starting an import of a text file.

FIGURE 10.1

Starting an import of a text file.

2. Next, in the Get External Data - Text File dialog box (shown in Figure 10.2), use the Browse button to select the text file to import (Jobs 02-Jul-2006.txt in the example).

Selecting a text file to import.

Selecting a text file to import.

3. After selecting the file, clicking OK on the Get External Data - Text File dialog box opens the Import Text Wizard (Figure 10.3), with a selection of Delimited or Fixed Width; generally Access pre-selects the correct option.

FIGURE 10.3

The Import Text Wizard dialog box.

import Text Wizard

FIGURE 10.3

The Import Text Wizard dialog box.

import Text Wizard

4. Clicking Next brings up a screen where you can adjust the column widths, as shown in Figure 10.4.

FIGURE 10.4

Adjusting columns in a fixed-width file.

FIGURE 10.4

Adjusting columns in a fixed-width file.

5. The next screen, shown in Figure 10.5, lets you specify field names if they are lacking in the text file (this is often the case with files exported from mainframes). The figure shows the first field (originally named ProductNumber, and given a default field name of Fieldl by the wizard) being renamed with its original name. You can also change the data type of the field, if necessary; for example, making a Text field containing numeric data a Long Integer or Currency field, or a Text field containing date/time data a Date/Time field.

6. The screen in Figure 10.6 lets you add, select, or not specify a primary key; in this case, because the data will be appended to a table that has an AutoNumber field, the "No primary key" option is correct.

7. You will next see a screen that lets you enter the name of the target Access table.

Specifying field names for an imported text file.

3J import Text Wizard

You can specify information about each of the fields you are importing, Select fields in the area below. You can then modify field information in the 'Field Options' area,

-Field Options -

I Advanced.^ | I^Cancd. | ~| | Next> | | Finish

FIGURE 10.6

Primary key choices for an imported text file.

You have now supplied all the information needed to set up the specification; the next steps, where you save the specification, are new to Access 2007.

^ y V- p"^-'r/i^i" Clicking the Finish button opens a new Save Import Steps screen (shown in Figure 10.7).

If you check the "Save import steps" checkbox, more controls appear on the dialog, where you can enter the name and description of the saved import specification, and even create an Outlook task to run it automatically at a specified interval.

FIGURE 10.7

Saving an import specification.

FIGURE 10.7

Saving an import specification.

Clicking the Save Import button saves the import specification. This allows you to select the saved specification and run it in the future from the Saved Imports button on the External Data tab of the Ribbon (shown in Figure 10.1), which saves a lot of time compared with going through all the steps of the wizard each time you want to do the import.

Unfortunately, Access 2007 VBA code doesn't recognize saved specifications. This fea-~ ture worked for several previous versions, but at present it is broken, so we must wait for a patch or service pack to fix it. For now, only code that avoids using specifications will work. (You can import from, or export to, a comma-delimited file without a specification, but not a fixed-width file.)

The Manage Data Tasks screen is shown in Figure 10.8. It has two tabs, one for saved imports and the other for saved exports. On each tab, you can select a saved specification to run.

The Manage Data Tasks dialog, where you can select a saved import or export specification.

Sometimes the wizard doesn't recognize that the first line of a text file contains the field " names. In that case, you need to give the fields appropriate names, and then delete the first row, with the field names as data, after the import is finished (you will probably get some Type Conversion errors in the Import Errors table for that line). Figure 10.9 shows the table of imported jobs data, and the Import Errors table with errors on the first row containing the field names.

FIGURE 10.9

A table of imported text data, with field names in the first data row, and an Import Errors table.

£:È J o bs 02 -J u 1 -2006Jmp ortErrors

1

Error

Field - Row '

Type Conversion Failure

JobDateTime 1

Type Conversion Failure

Producti onLine 1

Type Conversion Failure

BatchQyantity 1

1

1 ¡Record: m • |lof3 | ► w y

1 Iseardn

ÜI tbINewJobs - a x

ProductNo

JobDateTime » FactoryName -

- ProductionLine -

Batch Quantity - 1

ProductNumber

FactoryName

ÜJ

FRB-1011024-B

7/2/20061:01:00 PM Nutley

114

1122.24

F RB-1013889-B

7/2/2006 4:43:00 PM Amberson

120

2233.22

FRB-1013S98-C

7/2/2006 8:45:00 PM Amberson

120

2334.44

*

1 Record; < Il of 4 |

► M Mj I I (Search

Was this article helpful?

0 0

Post a comment