Creating PivotTables from External Data

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 values—such as 52,802—can 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.