MS-Access / Getting Started

---

Importing Text Files

You can import data from a text file into Access 2010 even though, unlike the data in a spreadsheet, the data in a text file isn't arranged in columns and rows in an orderly way. You make the data in a text file understandable to Access either by creating a delimited text file, in which special characters delimit the fields in each record, or by creating a fixed-width text file, in which each field occupies the same location in each record.

Preparing a Text File

You might be able to import some text files into Access 2010 without changing them, particularly if a text file was created by a program using standard field delimiters. However, in many cases, you'll have to modify the contents of the file, define the file for Access with an import specification, or do both before you can import it.

Setting Up Delimited Data

Access 2010 needs some way to distinguish where fields start and end in each incoming text string. Access supports four standard separator characters: a comma, a tab, a semicolon, and a space. When you use a comma as the separator (a very common technique), the comma (or the carriage return at the end of the record) indicates the end of each field, and the next field begins with the first nonblank character. The commas are not part of the data. To include a comma within a text string as data, you must enclose all text strings within single or double quotation marks (the text qualifier). If any of your text strings contain double quotation marks, you must enclose the strings within single quotation marks, and vice versa. Access accepts only single or double quotation marks (but not both) as the text qualifier, so all embedded quotes in a file that you want to import into Access must be of the same type. In other words, you can't include a single quotation mark in one field and a double quotation mark in another field within the same file.

Another common way to separate data is to use the tab character between fields. In fact, when you save a spreadsheet file as text in most spreadsheet programs, the program stores the columns with tab characters between them.

As with data type analysis, Access examines the first few rows of your file to determine the delimiter and the text qualifier. As you'll see later in this tutorial, if you want to import a file that is delimited differently, you can specify different delimiters and separators in the Import Text Wizard. The important thing to remember is that your data should have a consistent data type in all the rows for each column, just as it should in spreadsheet files. If your text file is delimited, the delimiters must be consistent throughout the file.

Setting Up Fixed-Width Data

Access 2010 can also import text files when the fields appear in fixed locations in each record in the file. You might encounter this type of file if you download a print output file from a host computer. Notice that each field begins in exactly the same location in all the records. (To see this sort of fixed spacing on your screen, you must display the file using a monospaced font such as Courier New.) Unlike delimited files, to prepare this type of file for importing, you must first remove any heading or summary lines from the file. The file must contain only records, with the data you want to import in fixed locations.

Importing a Text File

Before you can import a text file, you'll probably need to prepare the data or define the file for Access 2010 with an import specification, or both. After you do that, you can import the text file into an Access database by doing the following:

1. Open the Access database that will receive the text data. If that database is already open, close any objects so that only the Navigation pane is visible.
2. On the External Data tab, in the Import & Link group, click the Text File command.
3. Access opens the Get External Data - Text File dialog box, shown next. Click Browse to open the File Open dialog box. Select the folder and the name of the file you want to import. Click the Open button in the File Open dialog box to return to the Get External Data - Text File dialog box.
4. Make sure the Import The Source Data Into A New Table In The Current Database option is selected and then click OK. Access starts the Import Text Wizard and displays the first page of the wizard.
   On this page, the wizard makes its best estimation about whether the data is delimited or fixed-width. It displays the first several rows of data, which you can examine to confirm the wizard's choice. If the wizard has made the wrong choice, your data is probably formatted incorrectly. You should exit the wizard and fix the source file as suggested in "Preparing a Text File". If the wizard has made the correct choice, click Next to go to the next step.
5. If your file is delimited, the Import Text Wizard displays.
   Here, you can verify the character that delimits the fields in your text file and the qualifier character that surrounds text strings. Remember that usually when you save a delimited text file from a spreadsheet program, the field delimiter is a tab character, and you'll find quotation marks only around strings that contain commas. If the wizard doesn't find a text field with quotation marks in the first few lines, it might assume that no text is surrounded by quotes, and therefore, it might set the Text Qualifier field to {none}. You might need to change the Text Qualifier field from {none} to " if this is the case.
6. If you decided to create a new table in the Get External Data - Text File dialog box, the wizard displays. Use this page to specify or confirm field names (you can change field names even if the first row in the text file contains names), select field data types, and set indexed properties. If you're working in a fixed-width text file, you should provide the field names; otherwise, Access names the fields Field1, Field2, and so on.
   If you decided to append the data to an existing table, either the columns must exactly match both the count and the data type of the columns in the target table (left to right) or the file must be a delimited file with column names in the first row that match column names in the target table.
7. Click Next to go to the next page, where you can select a primary key, much as you did for spreadsheet files. Click Next when you are finished setting a primary key.
8. On the final page of the wizard, you confirm the name of the new table or the target table. You can also select the check box to start the Table Analyzer Wizard to analyze your new table. If you enter the name of an existing table, Access asks if you want to replace the old table.
   Click Finish to import your data. Access displays a confirmation message at the top of the Get External Data - Text File dialog box to show you the result of the import procedure. If the wizard encounters an error that prevents any data from being imported, it creates an import errors table in your database (with the name of the text file in the title) that contains a record for each error. The final page of the wizard also includes a check box you can select to save the import steps you just completed.

Fixing Errors

While importing text files, you might encounter errors that are similar to those described in "Importing Spreadsheet Data" previously. For example, when you append a text file to an existing table, some rows might be rejected because of duplicate primary keys. Unless the primary key for your table is an AutoNumber field, the rows you append from the text file must contain primary key fields and the values in those fields must be unique.

For delimited text files, Access 2010 determines the data type (and delimiter and text qualifier) based on the fields in the first several records being imported. If a number appears in a field in the first several records but subsequent records contain text data, you must enclose that field in quotation marks in at least one of the first few rows so that Access will use the Text data type for that field. If a number first appears without decimal places, Access will use the Number data type with the Field Size property set to Long Integer. This setting will generate errors later if the numbers in other records contain decimal places. You can also explicitly tell Access the data type to use by defining a custom import specification. See the "Defining an Import Specification," for details.

Access displays a message if it encounters any errors. As with errors that are generated when you import a spreadsheet, Access creates an import errors table. The table contains a record for each error. The import errors table lists not only the type of error but also the column and row in the text file in which the error occurred. The errors you can encounter with a text file are similar to those described earlier for a spreadsheet file.

You can correct some errors in the table in Design view. For example, you can change the data type of a field if the content of the field can be converted to the new data type. With other errors, you must either add missing data in Datasheet view or delete the imported records and import the table again after correcting the values in the text file that originally caused the errors.

Defining an Import Specification

If you are likely to import the same fixed-width file often (for example, a text file you receive from a mainframe once a month) or if you want to be able to use a macro or a Visual Basic procedure to automate importing a text file, you can use the Import Text Wizard to save an import specification for use by your automation procedures. To do so, begin by clicking Text File in the Import & Link group on the External Data tab. Next, select the file you want to import and click OK. Access now opens the Import Text Wizard, which you should use to examine your file, and verify that the wizard identifies the correct fields. At this point, click Advanced to see an Import Specification dialog box.

For fixed-width specifications, you can define the field names, data types, start column, width, indexed properties, and whether to skip a field. You can identify the language in the Language box and the character set in the Code Page box. You can also specify the way Access recognizes date and time values and numeric fractions. (For example, for a file coming from a non-U.S. computer, the Date Order might be DMY, and the Decimal Symbol might be a comma.) Click Save As to save your specification, and give it a name. You can also click Specs to load and edit other previously saved specifications. The loaded specification is the one Access uses to import the current file.