Query Reference

Owned by Echo Chu
Last updated: Jun 20, 2019 by Amanda Lu


BACKGROUND


Internal query SQL supported for Origin File Connector is designed to select desired data stored in an Origin project file. SQL operates through simple, declarative statements. This makes data extraction more flexible, helps to organize data in an Origin project and maintain the

Syntax:

SELECT COLUMN FROM Range WHERE Condition


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


Range to search


  • PROJECT,
  • ACTIVEFOLDER,
  • FAVORITEFOLDER


key words in Condition

Here’s an appendix of special key words Origin supports in Condition.

_Type

Let you select columns with specified plot designation.

Example

//search the whole project and select unhidden X columns

Select Column from Project where (_Type = "X" and _Visible = True);


Column_Y

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

Example

//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 (_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.

Example
//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 (_LName like "Trial*" and Column_Z = True);


_SName

Let you select columns of specified short name.

Example

//search the whole project and select column with short name “A”

Select Column from Project where _SName == "A";


_LName

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

Example

//search the whole project and select column whose long name starting with “Trial”

Select Column from Project where _LName like "Trial*";


_Comments

Let you select columns whose Comment matches the specified condition. Wildcard are supported. 

Examlpe

//search the whole project and select column with comment starting by letter “S” and end by digit “3”

Select Column from Project where _Comments like "S*3";


_Visible

Specify to select from the visible columns or hidden columns.

Example

//search the whole project and select the hidden Y columns

Select Column from Project where (_Type = "Y" and _Visible = False);


Column_P1

Let you select columns whose Parameters header line matches the specified condition. Wildcard characters are supported

Example

//search the whole project and select columns whose Parameter 1 including character “y”.

Select Column from Project where Column_P1 like "*y*";


Column_D1

Let you select columns whose first User-Defined Parameter matches the specified condition. Wildcard characters are supported

Example

//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_[Name]

Let you select columns whose User-Defined Parameter Name matches the specified condition. Wildcard characters are supported

Example

//search the active folder and select columns whose parameter row “Mean” is larger than 100.

Select Column from ACTIVEFOLDER where Column_[Mean] > 100;


Sheet_SName

Let you select columns in worksheets of Short Name matches the specified condition. Wildcard characters are supported

Example

/search the Favorite folder and select columns in sheets named “Trial…”.

Select Column from FAVORITEFOLDER where Sheet_SName like "Trial*";


Book_SName

Let you select columns in workbooks of Short Name matches the specified condition. Wildcard characters are supported

Example

//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

Let you select columns in workbooks of Long Name matches the specified condition. Wildcard characters are supported

Example

//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*");

integrity of that project as a database, regardless of size.