Use Spreadsheet Data Sources

You can use data from Lotus 1-2-3 or Microsoft Excel spreadsheets as source files, in either crosstab or database format.

Before using a crosstab as a source, you must define one or more named ranges in the spreadsheet. These ranges determine which data will become columns in the model. When using a database as a source, you must define a named database range in the spreadsheet, and also specify that range in Transformer.

Transformer reads the named ranges from the crosstab or database, and the Data Class (text, numeric, or date) for each cell value. For more information about how to define named ranges, see the documentation provided with your spreadsheet software.

Example 1

This example shows one page of a multipage crosstab.

 
A
B
C
D
E
1
Glassware Division
Q1
Q2
Q3
Q4
2
     
3
Income - Net Sales
$10,000
$10,000
$15,000
$20,000
4
Expenses - Salary
$2,000
$2,000
$2,500
$2,500
5
Expenses - Rent
$800
$800
$800
$800
6
Expenses - Advertising
$600
$700
$600
$600
7
Expenses - Production
$1,500
$1,500
$2,000
$4,000
8
NET INCOME
$5,100
$5,000
$9,100
$12,100

For this crosstab, the following ranges have been named (range names automatically become the columns in a Transformer model).

Name

Range

Name

Range

Division

A1

Expense

A4 .. A7

Quarter

B1 .. E1

Expense Amount

B4 .. E7

Income

B3 .. E3

Net Income

B8 .. E8

For a model requiring only the DIVISION, QUARTER, EXPENSE, and EXPENSE_AMT columns, Transformer reads all the expense types and expense amounts for each quarter and for each division.

Division

Qtr

Expense

AMT

Glassware

Q1

Expenses - Salary

$2,000

Glassware

Q2

Expenses - Salary

$2,000

Glassware

Q3

Expenses - Salary

$2,500

Glassware

Q4

Expenses - Salary

$2,500

...

   

Glassware

Q1

Production

$1,500

Glassware

Q2

Production

$1,500

The Data Sources list shows the following:

Example 2

This example shows a simple spreadsheet database.

    A       B           C        D
1   EMP_NO  NAME        DEPT     SALARY
2   0256    Wilson, K.  TECHW    $50,000
3   0141    Barnes, J.  DESIGN   $60,000
4   0724    Paul, N.    DESIGN   $70,000
5   1290    Power, R.   DESIGN   $80,000

The database has a named range.

Name

Range

DataTable

A1 .. D5

Transformer reads rows 1 to 5 in the range DataTable. The Data Sources list shows the following:

Notes

  • Range names from Lotus 1-2-3 that contain spaces are not valid. Replace the space with an underscore; for example, change a Lotus 1-2-3 range named "NET INCOME" to "NET_INCOME."

  • Before generating any categories, Transformer verifies that the named range represented by each column in the model still exists in the spreadsheet, and that the spreadsheet still contains the same number of pages, rows, and columns as when you first identified it as a data source. If Transformer detects a difference, it issues a warning, and prompts you to use the Modify Columns command to add, modify, match, or delete columns.

  • If the Data Sources list contains columns identifying named ranges that you don't need, you can delete them from the list without affecting the spreadsheet.

Steps

  1. Right-click the Data Sources list and select Insert Data Source to open the New Data Source wizard.

  2. In the Data Source Name box, type a name for the new source.

  3. In the Source Type box, select one of the following:

    • Lotus 1-2-3 crosstab
    • Excel crosstab
    • Lotus 1-2-3 database
    • Excel database

  4. In the Local Data File box, type the file name of the spreadsheet file.

  5. For a Lotus 1-2-3 or Excel database, select a range name from the Table or Range box.

  6. When you have specified all of the required parameters, click Finish.