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$ + "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.
Conclusion
Thanks for reading this post. I hope it will help you if you need to access Data Connector functionality via LabTalk.