Programming Data Connectors using LabTalk

はじめに

Data Connectors are Origin’s new and preferred method of importing data. While they are accessible from the Origin GUI, they are also accessible via LabTalk, Origin’s built-in scripting language.

In this blog post, I’ll show you how to use two popular Data Connectors (CSV and Excel)  to import data via LabTalk. I’ll take it a step further and include partial import in case this is important for some people. And, I’ll get to the point with code samples while keeping the exposition to a minimum.

But before we begin, we need to take a look at Partial Import syntax.

Partial Import Syntax

Partial Import syntax is used to build partial import “strings”.  The syntax can seem a bit confusing at first, but in 90% of cases, you’ll only need simple strings . Here is a break down of the syntax followed by a few examples.

Use the following characters in building your partial import string:

• Space ( ) means 、.
• Colon(:) separates from 、 to.
• Vertical bar(|) means read n cols or rows.
• Dash(–) means skip.
• Tilde(~) means except.

Here are example strings:

Type	String	Meaning
Column	1 3 7	Import columns 1, 3, and 7
Column	1:4	Import columns 1 to 4
Column	1 2:10|1-1	Import columns 1 and 2 to 10, reading 1 and skipping 1 (1,2,4,6,8,10)
Column	1 3:0|1-2	Import columns 1 and 3 to end, reading 1 and skipping 2 (1,3,6,9,…,end)
Column	1:0~2~4	Import columns 1 to end but skip columns 2 and 4 (1,4,5,6,…,end)
Row	1:99	Import rows 1 to 99
Row	300:0	Import rows 300 to end
Row	1:10 20:30	Import rows 1 to 10 and 20 to 30
Row	1:0|1-1	Import every other row starting at 1 (1,3,5,…,end)
Row	1:30 ~15:20	Import rows 1 to 30, skipping 15 to 20

As you can see, you can build complex partial import strings. But really, most of the time you won’t get into anything fancy.

Now let’s move on to actual code!

CSV Connector Code

Below is well-annotated example code using the CSV Connector to import data via LabTalk. Note that it only works in Origin 2021b or later.

// Create a new, blank workbook
newbook;

// Add CSV Data Connector to book
wbook.dc.add("CSV");

// Set the source file for data
wks.dc.source$ = System.path.program$ + "SamplesImport and ExportS15-125-03.dat";

// Need to extract current import options into a LabTalk Tree variable
Tree tr=wks.dc.optn$;

// Now, using the Tree variable, we will specify the settings we want for our import
// I've annotated which settings corresponding to the import dialog

// Header and label handling
tr.settings.mainheader = -1; // Main header lines: -1=Auto, otherwise number of lines
tr.settings.headertolabel= 0; // Save file header to: 0=Worksheet label area, 1=Book organizer 

tr.settings.heading = 1; // Data has column name: 0=No, 1=Yes
tr.settings.unit = 1; // Data has units: 0=No, 1=Yes
tr.settings.commentpos = 0; // Data has Comments: 0=None, 1=Before Names, 2=After Names and Units, 3=Between Names and Units
tr.settings.comments = 0; // Comment lines: number of comment lines in file

// Partial Import handling
tr.settings.partial.setattribute("Use", 1); // Required for partial import- turns on controls node
tr.settings.partial.col$ = "1 3"; // Column partial import string. Import columns 1 and 3
tr.settings.partial.row$ = "101:200"; // Row partial import string. Import rows 101-200

// This puts our modified Tree variable back into the import options
tr.tostring(wks.dc.optn$);

// Perform the import
wks.dc.import();

 

Excel Connector Code

Below is well-annotated example code using the Excel Connector to import data via LabTalk. In this example, two sheets are imported. both sheets have the same structure. Note that it only works in Origin 2021b or later.

// Create a new, blank workbook
newbook;

// Add Excel Data Connector to book
wbook.dc.add("Excel");

// Set the source file for data
wks.dc.source$ = System.path.program$ + "SamplesImport and ExportPartial Import.xlsx";

// Specify, by name, which Excel sheet to import
wks.dc.sel$ = "expt1";

// Need to extract current import options into a LabTalk Tree variable
Tree tr=wks.dc.optn$;

// Now, using the Tree variable, we will specify the settings we want for our import
// I've annotated which settings corresponding to the import dialog

// Header and label handling
tr.settings.mainheader = 0; // Main header lines: rows in file
tr.settings.headertolabel = 0; // Save file header to: 0=Worksheet label area, 1=Book organizer

tr.settings.labels.setattribute("Use", 1); // Required for labels- turns on controls node
tr.settings.labels.longname = 1; // Long Names: row in file
tr.settings.labels.unit = 2; // Units: row in file
tr.settings.labels.commentfrom = 0; // Comments From: row in file
tr.settings.labels.commentto = 0; // Comments To: row in file

// Partial Import handling
tr.settings.partial.setattribute("Use", 1); // Required for partial import- turns on controls node
tr.settings.partial.col$ = "1:3"; // Column partial import string. Import columns 1 to 3
tr.settings.partial.row$ = "1:99"; // Row partial import string. Import rows 1 to 99

// This puts our modified Tree variable back into the import options
tr.tostring(wks.dc.optn$);

// Perform the import
wks.dc.import();

// Now import the second sheet - with one line of code
// New sheet source should have same structure as first sheet

wbook.dc.newsheet("expt2")

Origin Connector Code

Below is an example of importing data from another Origin project by running a query, using the Origin Connector:

// Origin project file location:
string path$ = "C:tempmydata.opju";
// Create new workbook in current project
newbook;
// Connect to the desired Origin project using the Origin Data Connector
wbook.dc.ADD("Origin");
wks.dc.source$ = path$;
// Specify query string to pull all X columns that have long name of "alpha"
wks.dc.sel$=Select Column from Project where (Column_LName like "alpha" AND Column_Type = X);
wks.dc.import();

More information on the SQL query language supported by Origin connector can be fond here.

まとめ

Thanks for reading this post. I hope it will help you if you need to access Data Connector functionality via LabTalk.