VisiCube Knowledge Base Article #000009

How do I access spreadsheet data?

Background

VisiCube provides direct access to delimited text (ASCII) data files. A delimited text file is a text file in which each record is comprised of the same data fields, these fields occur in the same relative position of each record, and each field is delimited by a special character (such as a tab, comma, or space). Most data, however, is stored in a proprietary data source, such as a spreadsheet, which is not directly supported by VisiCube.

Question

How do I access source data in a spreadsheet (such as Excel or Lotus 1-2-3)?

Solution

You need to extract your data to a delimited text file. This is accomplished differently for each spreadsheet program, but generally can be accomplished with either of the facilities described below.

The system's copy-and-paste facility can be utilized to copy the desired data from your spreadsheet to a text file. This is generally done in the following manner:

• Open your spreadsheet to the applicable sheet.
• Select the cells which contain the desired data.
• Copy the cells to the clipboard.
• Open a new text file with a text editor (such as Notepad).
• Paste the clipboard contents into the text file.
• Close your spreadsheet.
• Close and save your text file.

The paste operation will typically generate tab-delimited data in your text file, but this is determined by your spreadsheet program.

The spreadsheet's extract facility can be utilized to extract the desired data from the spreadsheet and save it in a new text file. For Microsoft Excel 2002, for instance, this is accomplished in the following manner:

• Open your spreadsheet to the applicable sheet. (Only one sheet can be saved at a time.)
• In the "File" drop-down menu, choose the "Save As..." option.
• In the "Save as type" field, choose the "Text (Tab delimited) (*.txt)" option.
• Specify the name and location of the new file in the standard manner.
• Push the "Save" button.
• Close your spreadsheet.

The above is provided as an example. Each spreadsheet program, as well as each version of such programs, has its own proprietary method of accomplishing this task. For details, refer to the help system or documentation for your particular product.

More Information

VisiCube also supports zoned text files, of course. However, most spreadsheet programs do not support data extraction to a zoned format. If your spreadsheet program does, you may use zoned text files if you wish.

Many analysis products develop interfaces to obtain data from proprietary data sources to eliminate the extraction step described above. I have decided not to do so for a few reasons. The development effort to create and maintain those interfaces is always extensive and somewhat incomplete as there are innumerable sources. There are also compatibility issues as I attempt to keep the product (VisiCube in this case) up to date with changes made by other software manufactures (not all of whom are willing to expose their data structures, especially in advance of the release of their own product). Finally, such efforts are not directed at the desired core functionality. VisiCube is, after all, an analysis tool. That is why you use it. In the end, I have decided to keep my development efforts focused on the core functionality of VisiCube...giving you the best exploration tool I can. Besides, most data files that are shared among researchers are distributed as delimited-text files, not in their original proprietary form.

References

• PAR#100937; PAR#101296
• Last Modified: June 12, 2004
• Last Reviewed: June 12, 2004