Import Data

Click here for aggregate storage version

Import data from text or spreadsheet data files, with or without a rules file. Minimum permission required: Write.

Syntax

Syntax diagram for import data.DBS-NAMENumbers in MaxL SyntaxIMP-FILEIMP-FILEFILE-NAME

You can import data to a database in the following ways using import data.

KeywordDescription

...using max_threads INTEGER

Optionally specify a maximum number of threads to use, if this is a parallel data load.

Example:

import database Sample.Basic using max_threads 12
data from data_file '/nfshome/data/foo*.txt'
using rules_file '/nfshome/data/foo.rul'
on error write to 'nfshome/error/foo.err';

If this clause is omitted for a parallel data load, Essbase uses a number of pipelines equal to the lesser of number of files, or half the number of CPU cores.

import database <dbs-name> data from...

Specify whether the data import file(s) are local or on the server, and specify the type of import file(s).

To import from multiple files in parallel, use the wildcard characters * and/or ? in the IMP-FILE name so that all intended import files are matched.

  • * substitutes any number of characters, and can be used anywhere in the pattern. For example, day*.txt matches an entire set of import files ranging from day1.txt - day9.txt.

  • ?* substitutes one occurrence of any character, and can be used anywhere in the pattern. For example, 0?-*-2011.txt matches data source files named by date, for the single-digit months (Jan to Sept).

Example:

import database Sample.Basic
data from local data_file '/nfshome/data/foo*.txt'
using local rules_file '/nfshome/data/foo.rul'
on error abort;

...using ... rules_file

Import data into the database using a specified rules file. If you are using a rules file for a parallel data load, all the data files in the load must be able to use the same rules file.

...<data error spec> (on error...)

Required. Tell Essbase what to do in case of errors during the data load: abort the operation, or write or append to a specified error log.

...<data record spec> from data_string

Load a single data record into the selected database.

Example:

import database sample.basic data 
from data_string 
 '"Sales" "COGS" "Marketing" "Payroll" "Misc" "Opening Inventory" "Additions" 
 "Ending Inventory" "100-10" "New York" "Jan" "Actual" 
 678 271 94 51 0 2101 644 2067'
on error abort;

...<SQL connect spec> (connect as...)

If you are importing data from an SQL source, provide your SQL user name and password. You must always use a rules file when you load SQL data sources.

Notes

Example

import database sample.basic data from data_file "'$ARBORPATH\\app\\sample\\basic\\calcdat.txt'" on error abort;
import database sample.basic data
from data_file '/data/calcdat.txt'
using rules_file '/data/rulesfile.rul'
on error write to '/logs/dimbuild.log';