Parsing comes into play when you need to turn a text file into a table with rows and columns. There are plenty of those text files scattered all over the Web with numbers and other information organized into columns, or, once in a while, you get a disk from a bureaucrat with data in report format: The files look like tables because the information is organized into separate columns, but they are flat text files. They may have a .txt extension, the extension .dat or some other creative three-letter extension - or no extension at all. You can open these files with a text editor, but no database manager or a spread sheet will recognize them as tables. At this moment, you know it's time to parse. One word about downloading files using Netscape: If you are saving a table-looking file from the Web, make sure to save it as a text file and not as an html file. You don't want to waste time fishing out html coding from the document; let the browser do it for you. If you are not using Netscape, you may have to strip out the html coding using an html browswer or text editor before proceding.
Straight to the business
Lets download and parse one of the tables from the Census Bureau Web site ( http:// www.census.gov/) The easiest tool to use for parsing is Excel, but remember: Excel can only handle up to 16,000 records in one table. Table 1. Projections of the Population of Voting Age, for States, by Sex, Race, and Selected Ages: November 1996: http:// www.census.gov/ftp/pub/population/ socdemo/voting/proj/votepgl.asc Go to the File pull-down menu, pick Save As and for File Type, specify All files. Save the file, and go to Excel. In Excel, under the File pull-down menu, choose Open. Find your file, making sure to specify the correct file type - it should be text only(??). Excel will bring you to the Text Import Wizard. On Step 1, you have an option of specifying Original Data Type. Here are your choices:
Fixed Width: Fields are aligned in columns, with spaces between each column of text or numbers.: Choose Fixed Width (see Variation below). If your file has a lot of text on the top that you don't need in your table, note a small Start Import at Row box. You can browse your file in a window below your text with the numbers of each line. For our Census Projection file, choose Row 8 to start importing. If your file has some junk at the bottom, we will kill it after the parsing by deleting rows. The Wizard adjusts the view in the window. Click on Next. In Step 2, you will slice your file into the columns: Use the mouse and click once to insert a new line; double click to delete a line. In some cases, Excel will put black vertical lines subdividing the columns for you. Often, though, it doesn't guess right. Click and drag the lines within the window. If you see any repeating junk characters, such as vertical bars, you can separate them from the other data with the lines, or you can clean them out later using Excel's search and replace function under the Edit pull-down menu. In Step 3, specify in what format to import the data. Excel usually sets all the data types to General and guesses the data types; however, it doesn't always guess right. We will import all the columns as text. Now, save your table using Save As under the File pull-down menu. Under Save As Type, pick Microsoft Excel Workbook.
Delimited: All elements in the text are separated by special characters, such as quote marks, ampersands, tabs, spaces or commas.
Variation
This file also is a good example of how you can use the Delimited option. Before, when you opened the file, you specified Fixed Width. To see how the Delimited option works, repeat the process with the following exceptions: During Step 1, examine your file - each column is separated by a series of vertical bars. Let's use them as delimiters. Check the Delimited button. In Step 2 in the Delimiters part of the dialog window, check the Other box and type (vertical bar) in the provided window.
In Step 3, Excel puts the separating lines in the place of those pesky vertical bars. Let's let Excel do the data format. Never trust it completely, though, especially with dates.
Now, let's give the columns their names just type them into the cells above the appropriate columns of information. If you plan to use your file later in FoxPro, limit the names to eight characters and don't use spaces.
Clean-up
Let's get rid of periods in our first column: Highlight the column by clicking on the gray bar above the column, and use the Replace option from the Edit pull-down menu. Put a period into the Find box and leave the Replace With box empty. Click Replace and see the periods dissapear into the ether. Let's save this file as Excel Workbook, too.
What we just did with Excel is possible with Visual Foxpro 3.0. In FoxPro 2.6, in order to import a text file, you must build a corresponding table structure and then append a text file to the table as an SDF (standard data-file format), herding the data into correct chutes.
Visual Fox lets you do the parsing without going to Excel for help. Use Import under the File pull-down menu. Choose Import Wizard from the pop-up menu and follow the steps. Visual FoxPro will ask you to name the columns and set up the data format within the Wizard. Natalya Shulyakovskaya can be reached at (573) 882-0684, or send e-mail to natalya@nicar.org