Importing Text Files

You can import data from a text file into Access 2007 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 2007 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. See the sidebar, “Defining an Import Specification,” on page 291 for details.

Setting Up Delimited Data

Access 2007 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. Figure 6–9 shows a sample comma-separated and double-quote-qualified text file. You can find this file (CompaniesCSV.txt) on the companion CD.

image from book
Figure 6–9: A comma-separated and double-quote-delimited text file uses commas between the field values and surrounds text values in double quotations marks.

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. Figure 6–10 shows one of the worksheets from the CompaniesTABxl.txt Excel spreadsheet saved as text in WordPad. (You can see the tab alignment marks that we placed on the ruler to line up the columns.) Notice that Excel added double quotation marks around several of the text fields-the company names that contain a comma. Because this file is tab delimited, Access accepts the text fields without quotation marks. However, if all or part of your incoming data contains a text qualifier surrounding some of the fields, you should specify that qualifier when you import the data. If you do not do that, Access imports the qualifier characters as well.

image from book
Figure 6–10: A tab-separated text file uses tab characters to separate the fields.

As with data type analysis, Access examines the first few rows of your file to determine the delimiter and the text qualifier. Notice in Figure 6–10 that tabs are clearly the delimiter, but three of the company name fields are qualified with double quotes. As you’ll see later, 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 2007 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. Figure 6–11 shows a sample fixed-width text file. 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.

image from book
Figure 6–11: A fixed-width text file contains data in fixed-width columns.

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 2007 with an import specification, or both, as discussed in “Preparing a Text File” on page 282. 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 group, click the Text File command, as shown here.

    image from book

  3. Access opens the Get External Data-Text File dialog box shown next. Click the Browse button to open the File Open dialog box shown previously on page 262. Select the folder and the name of the file you want to import. (For these examples, we used the CompaniesTAB.txt and CompaniesFIX.txt files on the companion CD.) Click the Open button in the File Open dialog box to return to the Get External Data-Text File dialog box.

    image from book

  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, as shown next. On this page, the wizard makes its best guess 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 incorrectl. You should exit the wizard and fix the source file as suggested in “Preparing a Text File” on page 282. If the wizard has made the correct choice, click Next to go to the next step.

    image from book

  5. If your file is delimited, the Import Text Wizard displays the following page.

    image from book

    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. (You’ll need to do this if you use the CompaniesTABxl.txt sample file.) Also be sure to select the First Row Contains Field Names check box if your file has column names in the first row. If you don’t do that, the wizard assigns generic field names (Field1, Field2, and so on) and might misidentify the field data types.

    If your file is in fixed-width format, the wizard displays this page. (We have scrolled to the right to show one of the problems.)

    image from book

    Instead of showing delimiting characters, the wizard offers a graphic representation of where it thinks each field begins. To change the definition of a field, you can drag any line to move it. You can also create an additional field by clicking at the position on the display where fields should be separated. If the wizard creates too many fields, you can double-click any extra delimiting lines to remove them. In the example shown in the preceding illustration (using the CompaniesFIX.txt file on the companion CD), the wizard assumes that the street number is separate from the rest of the address. It also assumes that the State and Zip fields are one field. Because many of the spaces in the sample Comments field line up, it splits this field into several fields. You can double-click the line following the street number to remove it. You can click between the state and zip data to separate those into two fields. Finally, you can double-click all the extra lines the wizard inserted in Comments to turn that into one field. Click Next to go to the next step.

  6. If you decided to create a new table in the Get External Data-Text File dialog box, the wizard displays the page shown here. 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.

    image from book

    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. See Chapter 4 for details about the Table Analyzer Wizard. 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” on page 273. 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 2007 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 “Defining an Import Specification” on the next page 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.

For details about modifying your table design, see Chapter 5, “Modifying Your Table Design.” See Table 5–3 on page 207 for data conversion limitations.

image from book
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 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 the Advanced button to see an Import Specification dialog box like the one shown here.

image from book

For fixed-width specifications, you can define the field names, data types, start column, width, indexed properties, and whether or not 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 the Save As button to save your specification, and give it a name. You can also click the Specs button to load and edit other previously saved specifications. The loaded specification is the one Access uses to import the current file.

image from book

Microsoft Office Access 2007 Inside Out
MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)
ISBN: 0735623252
EAN: 2147483647
Year: 2007
Pages: 234

Similar book on Amazon
Access 2007: The Missing Manual
Access 2007: The Missing Manual
Microsoftu00ae Office Access(TM) 2007 Step by Step (Step By Step (Microsoft))
Microsoftu00ae Office Access(TM) 2007 Step by Step (Step By Step (Microsoft))
Microsoft Office Access 2007 Forms, Reports, and Queries
Microsoft Office Access 2007 Forms, Reports, and Queries
Alison Balter's Mastering Microsoft Office Access 2007 Development
Alison Balter's Mastering Microsoft Office Access 2007 Development © 2008-2017.
If you may any questions please contact us: