Programming Data Connectors using LabTalk

Introduction

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 and.
  • Colon(:) separates from and 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$ + "Samples\Import and Export\S15-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$ + "Samples\Import and Export\Partial 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")

Conclusion

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

About Chris Drozdowski

Chris Drozdowski is a Product Support Engineer at OriginLab. He loves to talk to customers and educate them. He particularly relishes diagnosing and solving difficult, edge-case issues. As well, he contributes code to help solve problems or enhance user experience. In his down time at work, he likes to research and write about esoteric product features. Outside of work, he enjoys spending time with his family, having fun with C++, working on his aquarium, and exploring craft beers.

View all posts by Chris Drozdowski →

Leave a Reply

Your email address will not be published. Required fields are marked *