Importing Data Using Scripts

 Home   Back   Next  

Accessing and Saving Data > Importing and Exporting Data > Importing Data Using Scripts

 

At times, it can useful to create a script to transform and import an external data set instead of using the other options for opening, connecting to or importing data.  This section provides a very brief sample script and related notes to get you started with an import.  Please see the scripting section for further information on using scripts.

 

Below is a sample script that defines the fields of an external data set and then imports the table into Strata:

 

 

var def =

{

    type: "fixed",

    row_width: 15,

 

    fields:

    [

        {source_offset:  0, source_width:  2, name: "FIELD1", type: DbType.Character, width:  2, scale: 0 },

        {source_offset:  2, source_width:  2, name: "FIELD2", type: DbType.Character, width:  2, scale: 0 },

        {source_offset:  4, source_width:  4, name: "FIELD3", type: DbType.Character, width:  4, scale: 0 },

        {source_offset:  8, source_width:  6, name: "FIELD4", type: DbType.Character, width:  6, scale: 0 },

        {source_offset: 14, source_width:  1, name: "FIELD5", type: DbType.Character, width:  1, scale: 0 },

        {source_offset: 14, source_width:  1, name: "FIELD6", type: DbType.Character, width:  1, scale: 0, formula: "UPPER(FIELD5)" }

    ]

};

 

 

// assign a layout to the file

HostServices.data.assignDefinition("c:\\users\\myname\\sample.txt", def);

 

 

// optionally import this file into a table called "tbl"

var db = HostApp.getDatabase();

db.execute("SELECT * INTO TABLE tbl FROM file://c:/users/myname/sample.txt");

HostApp.refresh();

 

alert("done");

 

 

When creating your script, please note the following definitions:

 

•Source Offset:  Each external data set is considered a string or byte stream; the source offset defines where each field begins.  The source offset is zero-based, meaning that the first character is letter 0, the second 1, and so on.
•Source Width:  This defines the length of the input field.
•Name:  This is the field name you wish to create in the output table.
•Type: This is formatted as DbType.____, where the blank can be Character, WideCharacter, Binary, Numeric, Double, Integer, Date, DateTime or Boolean.  This refers to the type of the output field.  The type of the input field is always considered a string or a byte stream.
•Width/Scale:  This defines the field width and decimal places (if applicable) for your output field. Source width and output width can be different, as it may be desirable to have an output width that is greater than the input width.
•Formula:  You can use database expressions to perform an optional type conversion.  For example, if you had a date in the YYYYMMDD format in your data, you could write the formula: "DATE($SRCFIELD,'YYYYMMDD')", and then specify "type" as a DbType.Date.  This would auto convert the type for you.
•Source_Encoding:  This is formatted as DbEncoding._____, where the blank can be filled with ASCII, EBCDIC, COMP, or COMP3 (if you require mainframe conversions).  This is only necessary if you are transforming from a non-ASCII file and is added into each field definition line.