RCL Commands - ASCII Imports

There are some programs within RockWorks which do not utilize "tree-style" menus, thus you cannot quickly generate an RCL listing of DEFINE and EXECUTE commands.  This topic discusses the commands used to import data from a text file into the RockWorks database via RCL.   

Contents

• Importing CSV Data from a Single File into an I-Data, I-Text, P-Data, P-Text, or T-Data Table

  Back to RCL Imports

Importing CSV Data from a Single File into an I-Data, I-Text, P-Data, P-Text, or T-Data Table

Introduction

This set of RCL commands replicates the File | Import | Text | Single Tables menu options in the Borehole Manager.  This tool can read data from a single CSV file and map the columns to different tracks in the I-Data, I-Text, T-Data, P-Data, or P-Text database table.  The import can be for single boreholes or multiple boreholes.

• We recommend that you be familiar with the menu-based import process before attempting to do the import via RCL script.  Please refer to one of the following topics for details.  Note that the steps are the same for CSV as for Excel data files.
  • I-Data: Importing Rows and Columns from Excel into an I-Data Table
  • I-Text: Importing Rows and Columns from Excel into an I-Text Table
  • T-Data: Importing Rows and Columns from Excel into a T-Data Table
  • P-Data: Importing Rows and Columns from Excel into a P-Data Table
  • P-Text: Importing Rows and Columns from Excel into a P-Text Table
• We highly recommend that you make use of the Project | Backup Database menu option or the RCL BACKUP command before running any import scripts.
   

Defining the Import File Name and Setup

When you import from CSV using the program menus, the first step is to define the name of the CSV file to be imported. Here are the corresponding RCL DEFINE commands (yes, this command says "export" even though this is an import process - the programs share code):

DEFINE:  EXPORT_TXT   REPORT_NAME             geochem_data.csv DEFINE:  EXPORT_TXT   INCLUDE_COLUMN_TITLES   True

The second command tells RockWorks if the data contains column titles in the first row.  If set to True, then the program will assume that the data listing starts on row 2 in the file.

Borehole Name

If your data does not include a column where the borehole name is defined, you can import the data for a single borehole using this command.  This should be omitted if RockWorks will be gleaning the borehole ID from the data.

DEFINE:  GENERAL    BOREHOLE_NAME     DH01

Multiplying Depths

Another option in the import is to specify whether depths are to be multiplied by a constant or not.  Here are the corresponding RCL DEFINE commands:

DEFINE:  BM_EXCHANGE   MULTIPLE_DEPTHS   False DEFINE:  BM_EXCHANGE   DEPTH_MULTIPLIER  1.0

Negative Data Values

Use these settings to define what to do with columns that contain negative values. They can be imported as-is, discarded altogether, or replaced with a user-specified value.  For example, if the import data uses a number like "-999" to denote no-data, and if you don't have other (valid) negative values, then you might choose to discard them.

DEFINE:  GENERAL   NEGATIVE_BEHAVIOR     0 DEFINE:  GENERAL   NEGATIVE_REPLACEMENT  1.0

Here are the NEGATIVE_BEHAVIOR settings:
0 = Include negatives
1 = Discard negatives
2 = Replace negatives

The second variable denotes the value to be used to replace the negative values. It is necessary only if NEGATIVE_BEHAVIOR is set to "2".  Beware: If you are importing data into the I-Data, T-Data, or P-Data tables, these are restricted to numeric entries only, so don't choose a non-numeric replacement value.

Defining the Default Date  

If you are importing data into the T-Data table, it's required that there be a default date specified, if none are present in the input data. 

DEFINE:  BM_EXCHANGE   DEFAULT_TDATA_DATE   5/6/2011

The date format needs to be compatible to the format used by your computer (based on the Windows Regional Settings).  It is only used if no date is specified for the T-Data import.

Mapping the Data Columns

When you import from CSV using the program menus, the next step is to map the data column names to the fields in the database.  Here is the corresponding RCL DEFINE command.

DEFINE:  BM_EXCHANGE   ASSAY_FIELDS_MAP    Bore, Depth1, Depth2, TCE, Soil, PCE, , ,% Sat

After the ASSAY_FIELDS_MAP variable (don't worry about the word "ASSAY" - this pertains to all of the data types) you simply list the database fields to which the columns in the CSV files are to be mapped, in the order in which the data columns are listed, separated with commas. 
! Note that you are NOT listing the data column names here, but the "receiving" fields in the database.

"Bore" or "Name" is the borehole name; if this is left out then the value for GENERAL, BOREHOLE_NAME (discussed above) will be used and all the data will go to that borehole.

I-Data, I-Text, and T-Data must have "Depth1" and "Depth2" defined.

P-Data and P-Text must have "Depth" defined.

T-Data must have a "Date" field defined.

If you want any of the columns skipped, denote them with spaces in the list.  In the example above, the Pb and Mg columns in the CSV file shown below, would NOT be imported. 

If the Type Name does not exist yet in the Types table, it will be added.  Type names cannot contain a comma in the name.

Perform the Import

Once you've established all of the import settings for the CSV file, using the DEFINE commands listed above, you can execute the import using the following EXECUTE commands. NOTE that the comments should NOT be included - they are shown here to explain which EXECUTE command is which.

EXECUTE:  CSV_2_IDATA   {for importing to the I-Data table} EXECUTE:  CSV_2_ITEXT   {for importing to the I-Text table} EXECUTE:  CSV_2_PDATA   {for importing to the P-Data table} EXECUTE:  CSV_2_PTEXT   {for importing to the P-Text table} EXECUTE:  CSV_2_TDATA   {for importing to the T-Data table}

Table of Contents

   Back to RCL Imports

RockWare home page