This brief post illustrates how to use the API of Grapheme (and Nexus) to bulk import multiple ASCII file as Tables within the current project. Our goal is to define a macro capable of:
- opening a File Browser to to enable the User selecting one or multiple files to be imported as Tables
- creating a so-called Table Importer to read the contents of each selected file and to convert it into a project Table
- convert the imported data so that each column of the imported table is a numeric one
The JavaScript
Let’s analyze the script step by step.
First we need to create a Files Browser returning an array of string. Each array element contains the full path of the file to be imported. Note.
/* we define a simple API based dialog
* to pickup the folder containing the
* files we wish to import.
*/
var files = GUI.createFilesBrowser("./");
if( files.length==0 )
throw( "nothing to import" );
Then we create a Table importer. As we suppose the files to be imported share the same format and data structure, we can use the same single importer multiple times, once on each selected file. This is also the more computationally efficient solution as we won’t need to create and initialize Table importers multiple times.
/* create a single importer to be used
* on selected files. We set COMMA as
* column separator and NEW LINE as
* row separator.
* We also suppose that the files
* have a first header line and we
* can start reading from the first line
* in the file (which is the header line)
*/
var importer=GRAPH.createImporter("Ascii");
importer
.setScalarProperty("lineSeparator", "\n")
.setScalarProperty("columnSeparator",",")
.setScalarProperty("headers", true)
.setScalarProperty("readFrom", 1);
Finally we need to iterate the selected files and import each one. When doing that we will use few String related methods to extract from the fill file path the simple file name and we will use that name as Table name within the project. This will be on help to identify the data source of each table and will help us to track things back. Note that the Table Importer will import the table even if a duplicated name has been provided. In this latter case, a progressive integer number will be added to the user provided table name to make sure no name conflicts exist in the current project.
/* now we iterate the selected file and import
* those one by one
*/
for(var i=0; i<files.length; i++) {
print("Importing file " + files[i]);
importer.setScalarProperty("source", files[i]);
// prepare a Table name based on file name.
var endName = files[i].lastIndexOf(".");
var bgnName = Math.max(
files[i].lastIndexOf("/"),
files[i].lastIndexOf("\\") );
var tabName = files[i].substr(bgnName, endName-bgnName );
var table = importer.importTable(tabName);
if( table==null ) {
API.logWarning(
"Unable to import table '" + tabName + "'. " +
"Check importer options match table source file.");
continue;
}
}
This is good as far as you don’t run the same macro on the same file multiple times as this will cause multiple copy of the same table data to be imported in the project. If that’s the case, few more controls could be implemented to prevent tables are loaded multiple times.
What we have done so far works perfectly and enable us to import multiple files within the current project. However imported tables turn out to have Textual based columns. This is because the AsciiImporter has been designed to blindly import data as string and relies on the user for further data conversions.
Let’s suppose that in our specific case we know data are numbers. Then we could add few lines to the script to automatically attempt the column conversion. We add the following code block at line 45 to be called on each newly imported table
/* now, we attempt to convert each column (by default
* imported as a text) into a numeric one. Note we need
* to iterate columns through the default Table View
*/
var columns = table.getView().listColumns();
for(var i=0; i<columns.length; i++)
columns[i].convert("DOUBLE");
}
The Python Script
The same functionalities easily translate in Python, with the main difference that we explicitly need to import the required iChrome’s packages within the macro. This is done at row 1 to 3.
Once the required API classes have been imported, the macro follows step by step the JavaScript one, now with a Python based syntax and with Python based loops.
Main code differences are observed between line 31 and 34 as JavaScript and Python provide slightly different utilities and functions to operate on Strings.
It is worthy noticing that Grapheme (or Nexus) APIs behave exactly in the same way and require exactly the same calling sequences and data entry
from ichrome import API
from ichrome import GRAPH
from ichrome import GUI
# we define a simple API based dialog to pickup the folder
# containing the files we wish to import.
#
files = GUI.createFilesBrowser("./");
if files.count == 0:
exit
# create a single importer to be reused on selected files.
# We set COMMA as column separator and NEW LINE as row
# separator. We also suppose files have a first header
# line and we can start reading from the first line in
# the file (which is the header line)
#
importer = GRAPH.createImporter("Ascii")
importer.setScalarProperty("lineSeparator", "\n")
importer.setScalarProperty("columnSeparator", ",")
importer.setScalarProperty("headers", True)
importer.setScalarProperty("readFrom", 1);
# now we iterate the selected file and import
# those one by one
#
for file in files:
print( "Importing file " + file )
importer.setScalarProperty("source", file)
# prepare a Table name based on file name.
endName = file.rfind(".")
bgnName = max( file.rfind("/"), file.rfind("\\") )
tabName = file[bgnName:endName]
table = importer.importTable(tabName);
if table==None:
API.logWarning( "Unable to import table " + tabName )
continue
# now, we attempt to convert each column (by default
# imported as a text) into a numeric one. Note we need
# to iterate columns through the default Table View
#
columns = table.getView().listColumns();
for column in columns:
column.convert("DOUBLE");
}
