Exporting Access Data to a Text File

Earlier in this chapter, you exported Access data to a specific type of filean Excel spreadsheet. Access can export data directly to several software applications (see the "More Compatible Formats" section at the end of this chapter for a complete list).

Now, let's suppose you agree to share your list of catalogs with a gardening friend, but she has just bought her first computer and isn't familiar with the software she'll be using. You can't export the data to an Excel spreadsheet file if she's going to be using Word because these two software applications have incompatible formats. This means that you can't open an Excel ( .xls ) file in Word or open a Word document ( .doc ) in Excel. (We're just using Excel and Word as examples; this is true with almost all software applications.) In a situation such as this, you could export the catalog records to a text file because it's the most compatible format of allmost software can import a text file.

Exporting to a text file isn't quite as simple as the earlier export to Excel was because there are two types of text files: delimited and fixed-width. A delimited text file uses a character, such as a tab, space, or comma, to separate the fields of data. A record in a delimited text file might resemble the following: "red", "white", "blue".

A fixed-width text file assigns a specific number of characters to each field. That way, the fields of data line up in columns . Not every column in the file has to be the same width, but every entry in the same column must contain the same number of characters, even if some of those characters are spaces to fill in around the actual data. For instance, if the column width is eight characters and the entry is wagon , you must add three space characters ( ___wagon ) so the entry equals eight characters. (The underscores just help you visualize the entire field, they're not really part of the data.)

Fortunately, Access has a wizard that can handle all the details. The following example demonstrates how to export Access data to a text file by exporting the catalog records to both a delimited and a fixed-width file. Begin with the delimited file by following these steps:

  1. Click the Tables shortcut in the Database window and select Catalogs . (Don't try to export a form or report. You can export both, but we won't attempt to in this book.)

  2. Right-click the table and select Export.

  3. In the Save As Type control, select Text Files . You could rename the file at this point, but Access uses the table's name by default, so stick with that.

  4. Click Export to launch the Export Text Wizard.

  5. The first question the wizard asks is whether you're creating a delimited or fixed-width file. Access uses the comma character ( , ) as the default delimiter. You can click the Advanced button to change the default delimiter , the default language, or any number of settings. However, all are beyond the scope of this example, so don't worry about the advanced options. Just select the Delimited option, and click Next to continue.

  6. The next pane lets you choose a delimiter other than the comma character, but just use the comma for this example. If you check the Include Field Names on First Row option, Access exports the field names along with the text. Whether you need the field names will really depend on how you're going to use the text file. Check it so you can see how the wizard responds in the display at the bottom of the dialog box. Click Next.

  7. The last pane displays the full pathname for the new text file, which should include the default folder. Also notice that the filename itself contains the .txt extension because you're creating a text file. Click Finish to create the text file, and then click OK to clear the confirmation message.

  8. Figure 16.5 shows Catalogs.txt in Word. The first line contains the Catalogs table field names, and each of the remaining lines represents a record for one catalog. You can see that sometimes a record wraps to the next line. That isn't a problem. Close Catalogs.txt and return to Access.

    Figure 16.5. Open the text file in a Word processor.

    graphics/16fig05.gif

Don't worry if this file's format doesn't seem to make much sense to you right now. The truth is, you probably wouldn't use this file as is in Word. We're just using Word to show you the file's structure. If you really wanted to work with the catalog records in Word, you'd probably use the OfficeLinks button to publish the data to Word (you'll learn how to do that in the "Publishing to Word" section later in this chapter). Let's export the same Catalogs table as a fixed-width file. Here's how to do so:

  1. Repeat steps 14 from the previous example. This time, however, name the file CatalogsFixedWidth . If you don't change the name, the wizard will write over the Catalogs.txt file you created in the previous exercise.

  2. In the wizard's first pane, click the Fixed Width option, and click Next.

  3. Things can get a bit complicated with a fixed-width file because the wizard uses a field's Field Size property as the width for each column. (You learned about field properties in Chapter 11.) In a table it doesn't matter if a field allows 225 spaces even though the field's largest entry contains only 15 characters. But those unnecessarily wide columns can be awkward to work with in a text file. So, click the Advanced button and you'll see a list of all the fields and their sizes. Some of them are way too large, and you should adjust these before you go any further. To do so, simply change the Start and Width values. Figure 16.6 shows our new valuestake a minute to change yours. (The Start value equals the Start and Width value of the previous field.) When you're done, click OK to return to the wizard's first pane. Then, click Next to continue.

    Figure 16.6. Reduce the field widths.

    graphics/16fig06.jpg

  4. The next pane lets you adjust the widths one more time. Look closely at the first column (the catalog name column), and you'll see that the column isn't wide enough to fully display Territorial Seed Company. Click the line that separates the name and address fields and drag it to the right a few spacesto about 26 (there's a ruler just above the field display), as shown in Figure 16.7. Check each field the same way and adjust the width if necessary. We also stretched the State field to 84. When you're satisfied with the size of each field, click Next.

    Figure 16.7. We made the name field wider.

    graphics/16fig07.jpg

  5. Click Finish in the final pane and then clear the confirmation message. Figure 16.8 shows CatalogsFixedWidth.txt in Word. Close CatalogsFixedWidth.txt and return to Access.

    Figure 16.8. Open the text file in Word.

    graphics/16fig08.gif

You probably won't actually work with a fixed-width text file in Word. As before, we're just using Word so you can see the file's structure. As you can see, each column comprises one of the original table's fields. It's okay if a record wraps to another line. Close both text files.



Absolute Beginner's Guide to Microsoft Office Access 2003
Absolute Beginners Guide to Microsoft Office Access 2003
ISBN: 0789729407
EAN: 2147483647
Year: 2002
Pages: 124

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net