Previous
Previous
 
Next
Next


IMPORT (spreadsheet)

You can use the IMPORT (from spreadsheet) command to copy data (not formulas) from a spreadsheet file into an Oracle OLAP worksheet object. A worksheet's dimensions are similar to the columns and rows of a spreadsheet. IMPORT always copies an entire spreadsheet file at a time.

IMPORT is commonly used to copy data from other software products (for example, a Lotus spreadsheet) into an Oracle OLAP workspace.

Syntax

IMPORT worksheetname FROM source [INTO workspace]

where source is one of the following:

WKS FILE file-id [NLS_CHARSET charset-exp]
WK1 FILE file-id [NLS_CHARSET charset-exp]
WRK FILE file-id [NLS_CHARSET charset-exp]
WR1 FILE file-id [NLS_CHARSET charset-exp]
DIF FILE file-id [NLS_CHARSET charset-exp]
CSV FILE file-id [STOPAFTER n|DELIMITER dchar|NLS_CHARSET charset-exp]

Parameters

worksheetname

An Oracle OLAP worksheet object. In any one IMPORT statement, you can import only one worksheetname from one spreadsheet file. You can specify a qualified object name for the worksheet; however, when you specify the INTO worksheet argument, the target workspace specified must be identical. See the INTO workspace argument for information about where the worksheet object is imported.

FROM WKS
FROM WK1
FROM WRK
FROM WR1
FROM DIF

Indicates that you want to import your Oracle OLAP worksheet from a 1-2-3 file, Version 1 (WKS) or Version 2 (WK1); a Symphony file, Version 1.0 (WRK) or Version 1.1 (WR1); or a data interchange format file (DIF).

Oracle OLAP does not recognize numbers in E format (exponential notation) in DIF files.

INTO workspace

An analytic workspace name that identifies the attached workspace into which data is imported. When worksheetname exists in the specified workspace, then its data is overwritten by the imported data. When worksheetname does not already exist, IMPORT creates it in the specified workspace. IMPORT ignores an identically named worksheet when it exists in another attached workspace.

When you do not specify this argument, then Oracle OLAP does the following:

FILE file-id

Identifies the file you want to import. The file-id argument is a text expression that represents the name of the file. The name must be in a standard format for a file identifier.

NLS_CHARSET charset-exp

Specifies the character set that Oracle OLAP uses when importing text data from the file specified by file-id which allows Oracle OLAP to convert the data accurately from that character set. This argument must be the last one specified. When this argument is omitted, and Oracle OLAP cannot determine the character set from the worksheet itself, then Oracle OLAP imports the data in the database character set, which is recorded in the NLS_LANG option.


Note:

The NTEXT data type is not supported in worksheets.

FROM CSV FILE file-id [STOPAFTER n] [DELIMITER dchar]

Indicates that you want to import from a source file on a cell-by-cell basis. See "CSV Import".

STOPAFTER n specifies that no more than n records should be read from the file. When STOPAFTER is omitted, Oracle OLAP reads the whole file.

DELIMITER dchar identifies the single character (dchar) that you want Oracle OLAP to interpret as the general field delimiter. The default value is comma.

Usage Notes

Default Dimensions of an Oracle OLAP worksheet object

The default dimensions of an Oracle OLAP worksheet are WKSCOL and WKSROW, which correspond to the columns and rows of a spreadsheet. WKSCOL and WKSROW are INTEGER dimensions with values of 1, 2, 3, and so on. When these dimensions already exist in an attached workspace but not in the current workspace, the IMPORT statement fails when it tries to create these dimensions. You can prevent this problem by first defining the worksheet with different dimensions. (See "Worksheet Dimensions" for more information.)

Addition of Cells when Needed

When the source spreadsheet contains more cells than are defined by the dimensions of the worksheet, IMPORT automatically adds dimension values to provide the required number of cells.

Empty and NA Cells

IMPORT merges the source file with the worksheet on a cell-by-cell basis. Cells from the source file that are not empty, even when they just contain NA, overwrite the contents of the corresponding cells in the worksheet; empty cells in the source file do not overwrite the worksheet; source-file cells beyond the end of the current worksheet are appended to it so that no data is discarded.

Numbers in DIF Files

When importing any number from DIF files, IMPORT gives it a DECIMAL data type.

CSV Import

The CSV import option automatically performs the following functions when importing from a source file into the cells of a worksheet:

When a group of characters is inside double quotation marks:

Examples

Importing a DIF File

This example shows how to import a spreadsheet in DIF format in a file called mortgage.dif. We define the worksheet first as a temporary object, which saves memory and storage space. IMPORT would define the worksheet sheet1 automatically when it did not already exist. When it had already been used in a previous IMPORT statement, any data in it would be overwritten with new data.

DEFINE sheet1 WORKSHEET TEMP
IMPORT sheet1 FROM DIF FILE 'mortgage.dif'