6.5.5 Reference of using SQL query in Origin Connector

From Origin 2020, Origin File Connector supports SQL query language to select desired data stored in an Origin project file. SQL operates through simple, declarative statements. It makes data extraction more flexible, helps to organize data in an Origin project and maintain them.

Syntax:

SELECT COLUMN FROM Range WHERE Condition

Note: only data column is supported as object used in Origin File Connector.

Range to search

FROM supports project path, workbook or sheet range notation. Following special keys are also available.

  • PROJECT,
  • ACTIVEFOLDER,
  • FAVORITEFOLDER

key words in Condition

Column_Type

Select columns with specified plot designation. 

//search the whole project and select unhidden X columns
Select Column from Project where (Column_Type = "X" and Column_Visible = True);

Column_Y

When you select a Y column, it will also import the associated X column. 

//search the whole project and select columns with long name starting by “Trial”
//and import both the selected columns and their X columns
Select Column from Project where (Column_LName like "Trial*" and Column_Y = True);

Column_Z

When you select a Z column, it will also import the associated X and Y columns. 

//search the whole project and select columns with long name starting by “Trial”
//and import the selected columns and their associated X&Y columns
Select Column from Project where (Column_LName like "Trial*" and Column_Z = True);

Column_SName

Select columns of specified short name. 

//search the active workbook and select column with short name “A”
Select Column from %H where Column_SName like "A";

Column_LName

Select columns of specified long name. Wildcard “*” and “?” are supported. “*” represents any string of characters, and “?” represents any single character 

//search the whole project and select column whose long name starting with “Trial”
Select Column from Project where Column_LName like "Trial*";

Column_Comments

Select columns whose Comment matches the specified condition. Wildcard are supported. 

//search the whole project and select column with comment starting by letter “S” and end by digit “3”
Select Column from Project where Column_Comments like "S*3";

Column_Visible

Specify to select from the visible columns or hidden columns. 

//search the whole project and select the hidden Y columns
Select Column from Project where (Column_Type = "Y" and Column_Visible = False);

Column_Pn

Select columns whose Parameters n header line matches the specified condition. Wildcard characters are supported 

//search the whole project and select columns whose Parameter 1 including character “y”.
Select Column from Project where Column_P1 like "*y*";

Column_Dn

Select columns whose User-Defined Parameter n matches the specified condition. Wildcard characters are supported 

//search the whole project and select columns whose Parameter1 including character “y” 
//and value in the 1st User-Defined Parameter larger than 3.
Select Column from Project where (Column_P1 like "*y*" and Column_D1 > 3);

Column_SampleID

Select columns whose User-Defined Parameter named SampleID matches the specified condition. If name has space characters, use [SampleID]. Wildcard characters are supported in the condition. 

//search the active folder and select columns whose parameter row “Population Mean” is larger than 100.
Select Column from ACTIVEFOLDER where Column_[Population Mean] > 100;

Sheet_SName

Select columns in worksheets of Short Name matches the specified condition. Wildcard characters are supported 

//search the Favorite folder and select columns in sheets named “Trial…”.
Select Column from FAVORITEFOLDER where Sheet_SName like "Trial*";

Sheet_Comments

Select columns from Project where sheet Comments match the specified condition. Wildcard characters are supported. 

Select Column From Project Where Sheet_Comments like "Sheet1" and Book_Comments like "Book1"

Book_Comments

Select columns from Project where book Comments match the specified condition. Wildcard characters are supported. 

Select Column From Project Where Book_Comments like "Trial Run*"

Book_SName

Select columns in workbooks of Short Name matches the specified condition. Wildcard characters are supported 

//search the whole project and select columns in books with Short Nam beginning with “Book”.
Select Column from Project where (Book_SName like "Book*");

Book_LName

Select columns in workbooks of Long Name matches the specified condition. Wildcard characters are supported 

//search the whole project and select columns in books with Short Nam beginning with “Book”,
//or books with Long Name beginning with “fit”.
Select Column from Project where (Book_SName like "Book*" or Book_LName like "*fit*");


Examples

Example 1
  1. Select Data: Connect to File: Origin Connector. Select file <OriginLab installation folder>\Samples\Tutorial Data.opj.
  2. In the "Select..." dialog, click Select Query button Select Query button.png to open Select Query dialog.
  3. Enter the following string:
    Select Column from Project where (Column_LName like "Year" AND Column_Type = X) or (Column_LName like "Value*" and Column_Type = Y)
  4. Click OK to to add it to the bottom panel.
  5. Click OK to import X columns with Long Name "Year" and Y columns with Long Name beginning with string "Value".


Example 2
  1. Select Help: Learning Center. In the dialog that opens, select to open project Statistical Graphs - Pyramid Plot of Australia Population.
  2. Run the following LabTalk script in Command Window to extract all male population data which is larger than 5000000 into a new workbook. 
    wo -qn Select Column from ["Austrialia Population from 1960 to 2017"]UnstackCols2! where (Column_Comments like "male*" and Column_N > 5000000);

    Note: for details of LabTalk command "worksheet -qn", please refer to this page.

English | Deutsch | 日本語