| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
The IMPORT command transfers data to an analytic workspace from a text file, a spreadsheet, or another analytic workspace from an EIF file.
Because the syntax of the IMPORT command is different depending on where the data to be imported is located, separate topics are provided for different types of source files:
You can use the IMPORT (from EIF) command to copy data and definitions into your Oracle OLAP analytic workspace from an EIF file. IMPORT also copies any dimensions of the imported data that do not already exist in your workspace, even when you do not specify them in the command. For information on importing variables dimensioned by composites, see "Unnamed Composites".
IMPORT (from EIF) is commonly used in conjunction with EXPORT (to EIF) to copy parts of one Oracle OLAP analytic workspace to another; you export objects from the source workspace to an EIF file and then import the objects from the EIF file into the target workspace. The source and target workspaces can reside on the same platform or on different platforms. When you transfer an EIF file between computers, you use a binary transfer to overcome file-format incompatibilities between platforms. The EIF file must have been created with the EIFVERSION set to a version that is less than or equal to the version number of the target workspace. Use EVERSION to verify the target version number.
You can also use IMPORT to store information in the EIFNAMES and EIFTYPES options.
Syntax
IMPORT import_item FROM EIF FILE file-id [INTO workspace] -
[MATCH [STATUS]|APPEND|REPLACE [DELETE]] [LIST [ONLY]] [DATA] -
[DFNS] [UPDATE] [NOPROP] [NASKIP] [NLS_CHARSET charset-exp]
where:
import_item is one of the following:
name [AS newname]
ALL
Arguments
The name of an analytic workspace object to be imported from an EIF file to an attached workspace. You cannot specify a qualified object name for the object, because the object is not yet in any workspace. You can list more than one name at a time. See the INTO workspace argument for information about where the object will be imported.
AS newname can be used to rename any type of object being imported except dimensions.
When you have exported a multidimensional object as separate variables, list all the variable names. (See the SCATTER AS keyword in the EXPORT (to EIF).)
Indicates that you want to import all the objects contained in the EIF file. (Default)
See the INTO workspace argument for information about where the objects will be imported.
A workspace name that identifies the attached workspace into which objects will be imported. When the objects exist in the specified workspace, then their data will be overwritten by the imported data. When the objects do not already exist, IMPORT creates them it in the specified workspace. IMPORT ignores identically named objects when they exist in other attached workspaces.
When you do not specify this argument, then Oracle OLAP does the following:
When you have not previously defined the objects being imported in an attached workspace, then IMPORT defines them automatically in the current workspace.
When the objects already exist in any attached workspace, then IMPORT overwrites the data they contain with the imported data.
Identifies the file you want to import. File-id is a text expression that represents the name of the file. The name must be in a standard format for a file identifier.
Indicates that the IMPORT command should bring in only the data associated with dimension values that match those already in the target workspace. For dimensions other than time dimensions, be sure that corresponding dimension values are spelled and capitalized identically in the EIF file and your target workspace when you want them to match; for example, Tents does not match TENTS. For time dimensions, Oracle OLAP identifies dimension values by the dates they represent rather than by the way they are displayed. Therefore, time dimension values in the EIF file will automatically match time dimension values in your workspace when they represent the same time periods. When you specify MATCH STATUS, IMPORT only imports data associated with the values included in the current status of that dimension. When the dimension is limited in the target workspace, Oracle OLAP ignores any data in the EIF file associated with the values excluded from the status.
Indicates that the IMPORT command should bring in all the dimension values, along with associated data, regardless of whether or not the dimension values match those already present in the target workspace. APPEND adds those that do not match to those already present; it adds new values to the end of the list of dimension values. For time dimensions, APPEND also adds dimension values to fill in any gaps between the dimension values in your target workspace and the new ones. (Default)
Indicates that, for objects already defined in the workspace, IMPORT should keep the existing dimension values that match the dimension values in the EIF file. IMPORT deletes dimension values (and their data) that do not match dimension values in the EIF file. IMPORT replaces the associated data for the dimension values kept as part of the new dimension when the associated data variables are included in the EIF file. For text dimensions, the order of the dimension values in the EIF file is also adapted.
When you specify REPLACE DELETE, no matching takes place. Before importing a dimension, Oracle OLAP performs a MAINTAIN DELETE ALL, which discards all data associated with the existing dimension, as well as the dimension values.
|
Important: Be careful when using the REPLACE keyword. When you replace the values of a dimension, all variables and relations in the target workspace dimensioned by it are affected. When a variable or relation is not being imported at the same time, replacing the values of one of its dimensions could result in the loss of its data. |
Produces a list of the definitions. For dimensions, the output lists the number of values in each dimension, as they are imported into the target workspace. For composites, the output lists the number of dimension value combinations. IMPORT also indicates the number of bytes read and the elapsed time every two minutes or, in any case, at the end of the import procedure.
When you define a conjoint or composite that uses an index type other than the default, the IMPORT LIST command displays the index type. When you use the default index type (HASH for conjoints, BTREE for composites), that information is not displayed.
EXPORT (to EIF) sends the list to the current outfile. When you specify LIST ONLY, you get only the listing without actually importing anything.
Causes Oracle OLAP to place the correct values in the EIFNAMES and EIFTYPES options without actually importing them. However, Oracle OLAP does not produce a full listing of the object definitions. To produce the list, specify the LIST keyword before the ONLY keyword.
Indicates that, for objects that already exist in the target workspace, IMPORT should update only the data associated with those objects. For formulas that already exist, IMPORT updates their EQ expressions. Objects that IMPORT creates in the target workspace are created with their full definitions, as well as any associated data. You can specify both DATA and DFNS, but when neither is specified, the default is DATA.
Indicates that, for objects that already exist in the target workspace, IMPORT should just update definitions and leave data unchanged. The components of the definition affected by IMPORT DFNS are: LD Command, VNF, and PROPERTY. Objects that IMPORT creates in the target workspace still get their data. You can specify both DATA and DFNS, but when neither is specified, the default is DATA.
Indicates that IMPORT should execute an UPDATE command after importing each object. This can be useful when importing large EIF files that would otherwise cause Oracle OLAP to run out of memory. To control the frequency of updates, use the EIFUPDBYTES option.
Prevents any properties that you have assigned to each object from being read from the EIF file.
Specifies that composite tuples (indexes) that contain only NA data should not be imported into the target workspace. This argument has no effect on tuples that already exist in the workspace.
Specifies the character set that Oracle OLAP will use when importing text data from the file specified by file-id. Normally, an EIF file contains its own specification of its character set, so that this argument is not needed. However, when the EIF file specifies the character set incorrectly or is missing the character set specification, then you must use this argument to specify the character set correctly. For information about the character sets that you can specify, see the Oracle Database Globalization Support Guide.
This argument must be the last one specified. When this argument is omitted, and Oracle OLAP is unable to determine the character set from the EIF file itself, then Oracle OLAP imports the data using the database character set, which is recorded in the NLS_LANG option.
Notes
A number of options determine how EIF files are imported and exported. These options are listed in Table 12-1, "EIF Options".
The MATCH, APPEND, REPLACE, DATA, and DFNS arguments you specify affect all the objects you name to be imported. When you want to treat different objects in different ways, use separate IMPORT commands.
When you are importing a relation, IMPORT also brings in the definition and values for the related dimension as well.
When you import a concat dimension into an analytic workspace and the concat dimension and none of its component dimensions already exist in the analytic workspace, then Oracle OLAP imports the concat dimension, its component dimensions, and the definitions of all of the dimensions.
When you import a concat dimension that does not already exist but one or more of its component dimensions already exist in the analytic workspace, then Oracle OLAP imports the concat dimension and any new component dimensions and their definitions. For the component dimensions that already exist in the analytic workspace, Oracle OLAP imports the component dimensions as it does other dimensions, obeying any MATCH, APPEND, REPLACE specifications in the IMPORT command.
When you import a concat dimension with a name and a definition of a concat dimension that already exists in the analytic workspace, then Oracle OLAP imports the concat dimension as it does other dimension.
When you import a concat dimension with the same name as one that already exists in the analytic workspace but the definition of the imported concat dimension is different than the definition of the existing concat dimension, then the definition of the existing concat dimension does not change and the definitions of the component dimensions of the existing concat dimension do not change. Only the component dimensions of the imported concat dimension that are also component dimensions of the existing concat dimension are imported. When the imported concat dimension does not share any component dimensions with the existing concat dimension, an error condition occurs. When you are importing any objects that are dimensioned by the concat dimension, then Oracle OLAP imports only the values of the object that correspond to the values of the imported dimensions.
You can import or export a dimension surrogate to or from an Express Interchange File (EIF). In those operations, a dimension surrogate behaves like a variable that is dimensioned by the dimension of the surrogate. In an EXPORT operation, the dimension for which the surrogate is defined is also exported. In an IMPORT operation, the dimension for which the surrogate is defined is imported but you can use the MATCH, STATUS, DATA, DFNS, APPEND, and REPLACE keywords to affect which values are imported.
Importing a dimension surrogates also imports the definition and values for the dimension for which it is a surrogate. When a dimension with the same definition already exists in the current analytic workspace, then the effects of the IMPORT keywords such as MATCH, APPEND, REPLACE, DATA, and DFNS are the same for the surrogate as they would be for a variable dimensioned by the dimension. When the name and definition of the imported surrogate is the same as a dimension surrogate that already exists in the current analytic workspace and when the imported surrogate has a value that is identical to a value in the existing surrogate, an error condition occurs.
You can import an INTEGER dimension surrogate when no object of the same name exists in the current analytic workspace or when you use the DFNS keyword. Importing an INTEGER dimension surrogate affects existing INTEGER dimension surrogates when the implicit importing of the dimension of the imported surrogate changes the values of the existing dimension.
When you are importing an INTEGER dimension that already exists in your target workspace, the following considerations apply.
When the imported INTEGER dimension is larger than the existing one, APPEND and REPLACE have the same effect. The dimension will end up with the number of values in the larger, imported dimension.
When the imported INTEGER dimension is smaller, REPLACE drops the appropriate dimension values from the end of the dimension, along with any associated data, while APPEND leaves the existing dimension values alone.
The IMPORT command translates between the INTEGER and SHORTINTEGER data types. When you are importing a variable with one of these data types from an EIF file and it already exists in your workspace as the other type, Oracle OLAP converts the data automatically. The maximum SHORTINTEGER value is 32,767 and the minimum is -32,767. When you import an INTEGER value that exceeds these limits into a SHORTINTEGER variable, the result is NA.
When the EIF file you are importing contains ID data that you want to import into TEXT dimensions, variables, relations, or valuesets, Oracle OLAP automatically converts the ID data to text during the import process.
When you are importing a program or model that already exists in your workspace, you must specify DFNS. A program or a model is a definition only; it does not have any data. The default option DATA does not import the source code when it already exists.
When you define a program, you may specify a data type or a dimension name, which is used when the program is called as a function. When you specify a data type, it determines the data type of the return value. When you specify a dimension name, the return value is a single value of that dimension. When you import an existing program, the data type or the dimension in the imported program definition and the existing program definition must match. Otherwise, Oracle OLAP produces an error message.
The PERMIT commands associated with an object are imported with the object definition. You can see them when you describe the object. However, permission conditions are not evaluated when the object is imported.
When an object with the same name already exists in the target workspace and you specify the DFNS keyword, the PERMIT commands for the object are updated. However, you must execute a PERMITRESET to put the new permission into effect. When an object with the same name already exists in the target workspace and you do not specify the DFNS keyword, the PERMIT commands for the object are not updated. When there is no pre-existing object in the target workspace, and you import with or without the DFNS keyword, the PERMIT commands for the object are updated, but you must execute a PERMITRESET to put the new permission into effect. (See the PERMIT command.)
When you export and import an entire workspace, then update, detach, and reattach the workspace, Oracle OLAP will ensure that all the permissions that were in effect before exporting are in place in the target workspace.
When you export PERMIT_READ or PERMIT_WRITE programs which are hidden, they are empty when imported. Additionally, when you outfilePERMIT_READ or PERMIT_WRITE programs which are hidden, then they are empty when infiled.
|
Tip: Rename PERMIT_READ and PERMIT_WRITE programs before using EXPORT (to EIF) or OUTFILE After copying the programs to an analytic workspace using IMPORT (to EIF) or INFILE. |
You can use IMPORT in conjunction with the EXPORT command to compact an entire workspace at once. To do this, first export the workspace and then import it under a different name. You can then delete the old workspace and rename the new one with the original name.
When you export a HASH, BTREE, or NOHASH conjoint dimension to an EIF file, the conjoint type is exported along with the definition in the EIF file. When you then import the conjoint dimension into a workspace, Oracle OLAP preserves the conjoint type when you import into a new dimension or a dimension already using that conjoint type. When you import the dimension into an existing dimension that does not use the same conjoint type, Oracle OLAP does not preserve the original conjoint type that was saved in the EIF file.
You can use the EIFBYTES option to learn the number of bytes read or written by the most recent IMPORT (EIF File) command. You can use the EIFNAMES option to get a list of the names of all the objects imported by the most recent IMPORT command and use the EIFTYPES option to learn the types of objects in that list.
The following format causes IMPORT to store information about the specified objects into the EIFNAMES and EIFTYPES options without actually importing the objects. IMPORT places a list of the object names specified by the IMPORT command in the EIFNAMES option. IMPORT also places a list of the type of each object listed in EIFNAMES into the EIFTYPES option. You may use the LIST keyword to send to the current outfile a full listing of the object definitions.
IMPORT name FROM EIF FILE file-id [LIST] ONLY
For more information, see the entries for EIFBYTES, EIFNAMES, and EIFTYPES.
When you define variables or other objects with the SPARSE keyword in the dimension list, Oracle OLAP creates an unnamed composite that corresponds to the SPARSE dimension list. When you export or import an object with the unnamed composite in its definition, the composite is automatically exported or imported with the object. Since the unnamed composite is not a regular workspace object, you cannot import or export it independently.
When you use the SEGWIDTH keyword of the CHGDFN command to specify the length of variable segments, segment information cannot be exported and imported automatically. You can save your SEGWIDTH settings by exporting the entire workspace, creating a new workspace, importing only the workspace objects into the new workspace, specifying segmentation, and then importing the variable data into the new workspace.
You can export and import TEXT and NTEXT values. Both data types can be exported to a single EIF file.
Exported TEXT values are stored in the EIF file using the character set specified for the file in the EXPORT (to EIF) command.
Exported NTEXT values are stored in the EIF file as NTEXT (UTF8 Unicode).
NTEXT values imported into TEXT objects are converted into the database character set. This can result in data loss when the NTEXT values cannot be represented in the database character set.
TEXT values imported into NTEXT objects are converted into the NTEXT (UTF8 Unicode) character set.
Examples
Example 14-15 Importing Dimensions from an EIF File
This example shows how to import the contents and dimensions of two variables into the current Oracle OLAP workspace from a disk file called finance.eif in the current directory object.
IMPORT actual budget FROM EIF FILE 'finance.eif'
Example 14-16 IIMPORTING a Concat Dimension
This example shows the result of importing a concat dimension that has a definition that is different than a concat dimension that already exists in the current analytic workspace. Suppose that the DESCRIBE command returns the following definitions for dimensions and variables in the current analytic workspace.
DEFINE city TEXT DIMENSION DEFINE state TEXT DIMENSION DEFINE country TEXT DIMENSION DEFINE locality DIMENSION CONCAT (city, state) DEFINE geog DIMENSION CONCAT (locality, country) DEFINE sales INTEGER VARIABLE <geog>
The following statement reports the sales data.
REPORT sales
The preceding statement produces the following results.
GEOG SALES ------------------- ----- <city: Boston> 1000 <city: Springfield> 2000 <state: Ma> 3000 <country: Usa> 4000
The DESCRIBE command returns the following definitions for dimensions and variables in the diffconcat.eif file.
DEFINE CITY TEXT DIMENSION DEFINE REGION TEXT DIMENSION DEFINE COUNTRY TEXT DIMENSION DEFINE GEOG DIMENSION CONCAT (CITY, REGION, COUNTRY) DEFINE SALES INTEGER VARIABLE <GEOG>
The following statement reports the sales data for the dimension values in the analytic workspace from which you exported the concat dimension that is in the diffconcat.eif file.
REPORT sales
The preceding statement produces the following results.
GEOG SALES ------------------ ----- <city: Boston> 1111 <city: Worcester> 2222 <region: East> 3333 <country: Usa> 4444
The following statement imports the sales variable from the diffconcat.eif file and implicitly imports the concat dimension geog. The APPEND keyword causes Oracle OLAP to add the value Worcester to the city dimension. After that, it imports new values for sales that correspond to <city: Boston>, <city: Worcester>, and <country: Usa>.
IMPORT sales FROM EIF FILE diffconcat.eif APPEND
After the import operation, reporting the SALES values produces the following results.
GEOG SALES ------------------- ----- <city: Boston> 1111 <city: Springfield> 2000 <city: Worcester> 2222 <state: Ma> 3000 <country: Usa> 4444
You can use the IMPORT (from text) command to copy data from a text file into an Oracle OLAP worksheet object. A worksheet's rows are similar to the lines of a text file.
IMPORT is commonly used to copy text files into an analytic workspace from other software products.
Normally, you should use the FILEREAD command for text files instead of IMPORT. FILEREAD is more efficient and does not require a worksheet object or separate handling of each column of data.
Syntax
IMPORT worksheetname FROM [TEXT|STRUCTURED|RULED [RULER ruler-exp] -
PRN FILE file-id [STOPAFTER n] [TEXTSTART schar] [TEXTEND echar] -
[DELIMITER dchar] [NLS_CHARSET charset-exp]
Arguments
A text expression that specifies the name of an Oracle OLAP worksheet object. When you have not previously defined worksheetname in your workspace, IMPORT will define it for you automatically, using the default dimensions WKSCOL and WKSROW. Any previous contents of worksheetname will be overwritten. In any one IMPORT command, you can import only one worksheetname from one text file.
Indicates that you want to import your Oracle OLAP worksheet from a text file.
Imports a whole source file as-is into an Oracle OLAP worksheet on a line-by-line basis. The source file is copied into a single wide worksheet column with a data type of TEXT. The column is always column 1 of the worksheet. Each line in the source file is imported into a separate cell on a separate row in the first column, using as many rows as there are lines in the source file. A blank line in the source file produces a TEXT value with zero characters (a null) in the corresponding row of the worksheet's first column. (TEXT is the default.)
Imports a source file into a target worksheet on a cell-by-cell basis, automatically performing three functions:
Each line of characters in the source file is copied into a single row of the target worksheet.
Each group of characters on a line in the source file is copied into a separate TEXT cell on the target worksheet row. A group of characters is defined by two conditions: an uninterrupted (except by a decimal point) sequence of numbers, or enclosure in double quotes. This means that numbers containing commas to mark off thousands will be split up into different cells unless the commas are first removed.
Any non-numeric characters not enclosed in double quotes are ignored, except minus signs that immediately precede numbers and so are copied into the same TEXT cell along with the numbers. (Be sure there are no spaces between a minus sign and its number in the source file.)
A blank line in the source file results in an NA in the first cell of the corresponding worksheet row.
When your file format does not conform to the pattern described here, you can use the TEXTSTART, TEXTEND, and DELIMITER keywords. These arguments let you customize the delimiters IMPORT uses to identify the start and end of each field.
Indicates import of a file on a column-by-column basis into worksheet cells of various data types. Every line in the source file must follow the same pattern of data along its length as every other line in the file. You describe this data pattern to Oracle OLAP in the one-line ruler-exp using the RULER keyword. IMPORT loops over each line in the source file and copies its contents into a corresponding pattern of cells on a row of the target worksheet, one row for each line. As ruler-exp loops over successive lines in the source file, it adds row after row to the target worksheet, building vertical columns of similar cells as it goes along. Status messages are sent to the current outfile every 20 rows, starting with the message 20 rows processed.
When the source file contains records that follow several different patterns of character groups, you will have to use the less exacting options, STRUCTURED or TEXT, to import the data.
Used only with the RULED keyword to specify the data type, length, and repeat count of each character group in the record pattern of the source file. Ruler-exp consists of a list of character-group specifications. Each character-group specification must be separated by a comma (,), by backslash N (\n), or by a space( ). You do not have to include enough specifications to account for all the characters in the basic record pattern (or line pattern) of the source file; RULER will step to the next record as soon as it runs out of specifications on each line, regardless of how far it is from the end of the current record. Remember to enclose literal text in single quotes.
The specifications for groups of characters are of three types: T for TEXT, A for numeric (INTEGER or DECIMAL), and S for skip or ignore. The formats for these types are shown in Table 14-1, "Character-Group Specifications for IMPORT from Text".
Table 14-1 Character-Group Specifications for IMPORT from Text
| Format | Description |
|---|---|
[mm]Tnn |
Specifies that Oracle OLAP should copy mm groups (default = 1) of nn characters (bytes) apiece as TEXT. Specifying a group (or groups) of 0 characters leaves an empty cell(s) in the corresponding position in the worksheet. Each group may consist of up to 400098 characters. Trailing blanks are stripped. |
[mm]Ann |
Specifies that Oracle OLAP should copy mm groups (default = 1) of nn characters (bytes) apiece and try to convert each group to a number. When a character group cannot be converted to a number, it is copied into a TEXT cell and any trailing blanks are stripped. A valid number includes anything you can type for a GET(DECIMAL) function except NA.
Commas embedded in a number before a period (decimal point) are ignored. This means that multiple numbers separated only by commas or two numbers separated only by a single period are treated as parts of a single number (when you want the numbers treated separately, insert spaces between them in the source file). Leading dollar signs ( Numbers preceded by a hyphen, or a hyphen and spaces, and numbers enclosed in parentheses, are treated as negative. Specifying a group (or groups) of |
[mm]Snn |
Specifies that Oracle OLAP should skip or ignore mm groups of nn characters (bytes). The limit for nn is 32,767. (You would probably only use mm to expand this limit to handle a very long record.) |
Identifies the file you want to import. File-id is a text expression that represents the name of the file. The name must be in a standard format for a file identifier.
Specifies that no more than n records should be read from the file. When STOPAFTER is omitted, Oracle OLAP will read the whole file.
The schar argument is a text expression that specifies a single character that you want Oracle OLAP to interpret as the start of a text field in a structured file. The default character is a double quote (").
The echar argument is a text expression that specifies a single character that you want Oracle OLAP to interpret as the end of a text field in a structured file. The default character is a double quote (").
The dchar argument is a text expression that specifies a single character that you want Oracle OLAP to interpret as the general field delimiter in a structured file. Oracle OLAP uses the general field delimiter to identify both numeric and text fields. The default character is a comma (,).
Specifies the character setthat Oracle OLAP will use when importing text data from the file specified by file-id. This 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 is unable to determine the character set from the file itself, then Oracle OLAP imports the data in the database character set, which is recorded in the NLS_LANG option.
Notes
The WKSROW (the default worksheet row) dimension of an Oracle OLAP worksheet corresponds to the lines of a text file. The WKSCOL (the default worksheet column) dimension of a worksheet divides each row into cells that can be used to separate data types when there are potentially several types on each line of the source file. WKSROW and WKSCOL are INTEGER dimensions with values of 1, 2, 3, and so on.
Oracle OLAP sets up a minimum-size worksheet that is 63 cells square, regardless of whether or not all the cells are used. When the source text file requires an Oracle OLAP worksheet larger than the minimum, IMPORT automatically increases the dimension values of WKSCOL and WKSROW as needed.
When importing a number from a text file, IMPORT gives it an INTEGER data type.
When the file you are importing originated on another computer, ensure that its character set is appropriate. When you transfer a text file to another computer, the communications program handling the transfer makes any necessary character translations; for example, from ASCII to EBCDIC. You should set the parameters of the transfer program so that the resulting file is in the correct character set for the receiving computer.
Examples
Example 14-17 Importing Columns Without the RULER Keyword
Suppose you have a file named abctxt in your current directory. It has 10 five-digit groups of integers, followed by a group of 20 characters of text. To import this file into an Oracle OLAP worksheet called sheet1, use the following statement.
IMPORT sheet1 FROM RULED PRN FILE 'abctxt' ruler '10a5, t20'
The actual format for the file name must follow the conventions for your operating system.
Example 14-18 Importing Columns with the RULER Keyword
Suppose a file called mix has no line delimiters, with records containing 100 characters apiece. Each record has the character distribution illustrated in the following table.
| Character | Content |
|---|---|
| 1 - 10 | To be ignored |
| 11 - 17 | Decimal number |
| 18 - 28 | To be ignored |
| 29 - 30 | Two single-character code |
| 31 - 35 | Integer |
| 36 - 100 | To be ignored |
To import this file into an Oracle OLAP worksheet called sheet2, use the following statement.
DEFINE sheet2 WORKSHEET temp IMPORT sheet2 FROM RULED PRN FILE 'mix' RULER - 's10, a7, s11, 2t1, a5'
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]
Arguments
An Oracle OLAP worksheet object. In any one IMPORT command, 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 will be imported.
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.
A workspace name that identifies the attached workspace into which data will be imported. When worksheetname exists in the specified workspace, then its data will be 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:
When you have not previously defined worksheetname in an attached workspace, IMPORT defines it automatically in the current workspace using the default dimensions WKSCOL and WKSROW.
When worksheetname already exists in any attached workspace, IMPORT overwrites the data it contains with the imported data.
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.
Specifies the character set that Oracle OLAP will use when importing text data from the file specified by file-id. This 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 is unable to 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.
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 will read 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.
Notes
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 command will fail 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.)
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.
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.
When importing any number from DIF files, IMPORT gives it a DECIMAL data type.
The CSV import option automatically performs the following functions when importing from a source file into the cells of a worksheet:
Each line of characters in the source file is copied into a single row in the target worksheet.
Each group of characters on a line in the source file is copied into a separate TEXT cell in the target worksheet row, and groups are separated by the delimiter character.
When a group of characters is inside double quotation marks:
A delimiter character found in this group is treated as a literal.
When a double quotation mark occurs within this group, it must be followed by another double quotation mark.
A linefeed (\n) found within the group is ignored.
Spaces or tabs found before a starting quotation mark and after an end quotation mark are ignored.
All imported text is rendered in the database character set in the worksheet object. The NTEXT data type is not supported in worksheets.
Examples
Example 14-19 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 command, any data in it would be overwritten with new data.
DEFINE sheet1 WORKSHEET TEMP IMPORT sheet1 FROM DIF FILE 'mortgage.dif'
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|
