Parsing Data

Problem

You've imported data from a text file as discussed in Recipe 3.3, but all data on each row is lumped into a single cell. You'd like to separate the data on each row into columns.

Solution

Select the data you'd like to parse. Then select Data "Text to Columns... " from the main menu bar to open the "Convert Text to Columns Wizard.

Figure 3-12. Data imported using web query

 

Discussion

The data shown in Figure 3-7 (in Recipe 3.3) was imported from a text file via drag-and-drop. The problem with this approach is that all the data on each row is lumped into a single cell. This is less of a problem with the import method in Recipe 3.1, because using File images/U2192.jpg border=0> Open... automatically launches the Text Import Wizard, which allows you to set columns for your data either manually or by specifying delimiters.

The "Convert Text to Columns" Wizard, accessible via Data "Text to Columns ...," is essentially the Text Import Wizard. The three steps in the "Convert Text to Columns Wizard are identical to those in the Text Import Wizard. First you specify whether the text to be parsed is in fixed-width form or delimited. Then, depending on your selection in step 1, you either manually set column breaks or select the delimiting character. Finally, you can specify data formats and choose which columns to ignore.

Performing these steps on the data shown in Figure 3-7 yields the parsed data shown in Figure 3-13.

Figure 3-13. Parsed data