Although most of the time you will create PivotTables from data stored in Excel 2007 worksheets, you can also bring data from outside sources into Excel 2007. For example, you might need to work with data created in another spreadsheet program with a file format that Excel 2007 can't read directly. Fortunately, you can transfer worksheets from one program to another by exporting the data from the original program into a text file, which Excel 2007 then translates into a worksheet.
Spreadsheet programs store data in cells, so the goal of representing spreadsheet data in a text file is to indicate where the contents of one cell end and those of the next cell begin. The character that marks the end of a cell is a delimiter, in that it marks the end (or "limit") of a cell. The most common cell delimiter is the comma, so the delimited sequence 15, 18, 24, 28 represents data in four cells. The problem with using commas to delimit financial data is that larger valuessuch as 52,802can be written by using commas as thousands markers. To avoid confusion when importing a text file, the most commonly used delimiter for financial data is the Tab character.
To import data from a text file, on the Data tab, in the Get External Data group, click From Text to display the Import Text File dialog box.
From within the Import Text File dialog box, you browse to the directory that contains the text file you want to import. Double-clicking the file launches the Text Import Wizard.
The first page of the Text Import Wizard enables you to indicate whether the data file you are importing is Delimited or Fixed Width; Fixed Width means that each cell value will fall within a specific position in the file. Clicking Next to accept the default choice, Delimited (which Excel 2007 assigns after examining the data source you selected), advances you to the next wizard screen.
This screen enables you to choose the delimiter for the file (in this case, Excel 2007 detected tabs in the file and selected the Tab check box for you) and gives you a preview of what the text file will look like when imported. Clicking Next advances you to the final wizard screen.
This screen enables you to change the data type and formatting of the columns in your data list. Because you'll assign number styles and PivotTable Quick Styles after you create the PivotTable, you can click Finish to import the data into your worksheet. After the data is in Excel 2007, you can work with it normally.
In this exercise, you'll import a data list into Excel 2007 from a text file and then create a PivotTable based on that list.
BE SURE to start Excel 2007 before beginning this exercise.
CLOSE the Imported Data workbook.