2.10.1 Importing Data

Importing-Data

Import DataData ImportThe following examples demonstrate importing of data from external files. The examples import ASCII files, but the scripts can be adjusted for filetype (e.g. MATLAB). Syntax and supporting commands will be the same. Since these examples import Origin sample files, they can be typed or pasted directly into the Script or Command window and run.

Import an ASCII Data File Into a Worksheet or Matrix

Data Connector Methods

This is a simple example of single file import using a Data Connector.

string path$ = system.path.program$ + "Samples\Import and Export\ASCII CSV with Quotes.csv";
newbook;
wbook.dc.add("CSV");
wks.dc.source$ = path$;
wks.dc.import();

See complete list of wks.dc objects and wbk.dc objects

X-Function Methods

This example imports an ASCII file (in this case having a *.txt extension) into the active worksheet or matrix. Another X-Function, findfiles, is used to find a specific file in a directory (assigned to the string path$) that contains many other files. The output of the findfiles X-Function is a string variable containing the desired filename(s), and is assigned, by default, to a variable named fname$. Not coincidentally, the default input argument for the impASC X-Function is a string variable called fname$.

string path$ = system.path.program$ + "Samples\Import and Export\";
findfiles ext:=matrix_data_with_xy.txt;
impASC;

Import ASCII Data with Options Specified

This example makes use of many advanced options of the impASC X-Function. It imports a file to a new book, which will be renamed by the options of the impASC X-Function. Notice that there is only one semi-colon (following all options assignments) indicating that all are part of the call to impASC.

string fn$=system.path.program$ + "Samples\Spectroscopy\HiddenPeaks.dat"; 
impasc fname:=fn$ 
options.ImpMode:=3                        /* start with a new book */
options.Sparklines:=0                     /* turn off sparklines */
options.Names.AutoNames:=0                /* turn off auto rename */
options.Names.FNameToSht:=1               /* rename sheet to file name */
options.Miscellaneous.LeadingZeros:=1;    /* remove leading zeros */

Import Multiple Data Files

Data Connector Methods

This example demonstrates importing multiple CSV files to a new workbook, using a Data Connector. The scripts creates a new workbook for each file.

string folder$ = system.path.program$ + "Samples\Batch Processing\";
string files$;
findfiles path:=folder$ ext:=".csv" fname:=files$;
int num = files.GetNumTokens(CRLF);
for (int ii = 1; ii <= num; ii++ ) {
	newbook;wbook.dc.add("CSV");
	wks.dc.source$ = files.gettoken(ii, CRLF)$;
	wks.dc.Import();
}

This example imports the same files into a single workbook.

string folder$ = system.path.program$ + "Samples\Batch Processing\";
string files$;
findfiles path:=folder$ ext:=".csv" fname:=files$;
int num = files.GetNumTokens(CRLF);
newbook;
page.nlayers = num;
wbook.dc.add("CSV");
for (int ii = 1; ii <= num; ii++ ) {
	page.active = ii;
	wks.dc.sel$="";//needs to be before source, helps connect the sheet
	wks.dc.source$ = files.gettoken(ii, CRLF)$;
}
wbook.dc.Import();

This example partially imports CSV with wks.dc.optn$ setting by tree

wbook.dc.add("CSV");
string str1$="%@O";
str1$ = str1.GetFilePath()$;
str1$ += "Samples\Statistics\automobile.dat";
wks.dc.source$=str1$;

Tree tr1=wks.dc.optn$;
tr1.settings.partial.SetAttribute("Use", 1);
tr1.settings.partial.col$="1:3";
tr1.ToString(wks.dc.optn$);//convert tree to string
wks.dc.import();

X-Function Methods

This example demonstrates importing multiple data files to a new workbook; starting a new worksheet for each file.

string fns, path$=system.path.program$ + "Samples\Curve Fitting\"; 
findfiles fname:=fns$ ext:="step1*.dat";   // find matching files in 'path$'
int n = fns.GetNumTokens(CRLF);      // Number of files found
string bkName$;
newbook s:=0 result:=bkName$;
impasc fname:=fns$                  // impasc has many options
options.ImpMode:=4     		    // start with new sheet 
options.Sparklines:=2 		    // add sparklines if < 50 cols
options.Cols.NumCols:=3  	    // only import first three columns 
options.Names.AutoNames:=0 	    // turn off auto rename 
options.Names.FNameToBk:=0          // do not rename the workbook 
options.Names.FNameToSht:=1         // rename sheet to file name 
options.Names.FNameToShtFrom:=4     // trim file name after 4th letter
options.Names.FNameToBkComm:=1      // add file name to workbook comment
options.Names.FNameToColComm:=1     // add file name to columns comments
options.Names.FPathToComm:=1        // include file path to comments
orng:=[bkName$]A1!A[1]:C[0] ;

Import an ASCII File to Worksheet and Convert to Matrix

This example shows two more helpful X-Functions working in conjunction with impASC; they are dlgFile, which generates a dialog for choosing a specific file to import, and w2m which specifies the conversion of a worksheet to a matrix. It should be noted that the w2m X-Function expects linearly increasing Y values in the first column and linearly increasing X values in the first row: test this with matrix_data_with_xy.txt in the Samples\Import and Export\ folder.

dlgfile g:=ascii; // Open file dialog
impAsc; // Import selected file
// Use the worksheet-to-matrix X-Function, 'w2m', to do the conversion
w2m xy:=0 ycol:=1 xlabel:=row1

Related: the Open Command

Another way to bring data into Origin is with the Open (Command).

Open has several options, one of which allows a file to be open for viewing in a notes window:

open -n fileName [winName]

This line of script opens the ASCII file fileName to a notes window. If the optional winName is not specified, a new notes window will be created.

To demonstrate with an existing file, try the following:

%b = system.path.program$ + "Samples\Import and Export\ASCII simple.dat";
open -n "%b";

Import with Themes and Filters

Import with a Theme

When importing from the Origin GUI, you can save your import settings to a themeImport Data Theme file. Such theme files have a *.OIS extension and are saved in the \Themes\AnalysisAndReportTable\ subfolder of the Origin User Files Folder (UFF). They can then be accessed using an X-Function with the -t option switch. The import is performed according to the settings saved in the theme file specified.

string fn$=system.path.program$ + "Samples\Spectroscopy\HiddenPeaks.dat"; 
// Assume that a theme file named "My Theme.OIS" exists 
impasc fname:=fn$ -t "My Theme";

Import with an Import Wizard Filter File

Custom importing of ASCII files and simple binary files can be performed using the Import Wizard GUI tool. This tool allows extraction of variables from file name and header, and further customization of the import including running a script segment at the end of the import, which can be used to perform post-processing of imported data. All settings in the GUI can be saved as an Import Filter File to disk. Such files have extension of .OIF and can be saved in multiple locations.

Once an import wizard filter file has been created, the impfile X-Function can be used to access the filter and perform custom importing using the settings saved in the filter file.

string fname$, path$, filtername$;
// point to file path
path$ = system.path.program$ + "Samples\Import and Export\";
// find files that match specification
findfiles ext:="S*.dat";
// point to Import Wizard filter file
string str$ = "Samples\Import and Export\VarsFromFileNameAndHeader.oif";
filtername$ = system.path.program$ + str$;  
// import all files using filter in data folder 
impfile location:=data;

Import from a Database

Origin provides four functions for Database Queries. The basic functionality of Database importing is encapsulated in two functions as shown in this example using the standard Northwind database provided by Microsoft Office:

// The dbedit function allows you to create the query and connection
// strings and attach these details to a worksheet
dbedit exec:=0
sql:="Select Customers.CompanyName, Orders.OrderDate,
[Order Details].Quantity, Products.ProductName From
((Customers Inner Join Orders On Customers.CustomerID = Orders.CustomerID)
Inner Join [Order Details] On Orders.OrderID = [Order Details].OrderID)
Inner Join Products On Products.ProductID = [Order Details].ProductID"
connect:="Provider=Microsoft.Jet.OLEDB.4.0;User ID=;
Data Source=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;
Mode=Share Deny None;Extended Properties="";
Jet OLEDB:System database="";
Jet OLEDB:Registry Path="";
Jet OLEDB:Database Password=***;
Jet OLEDB:Engine Type=5;
Jet OLEDB:Database Locking Mode=1;
Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="";
Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:SFP=False;Password="

// The dbimport function is all that's needed to complete the import
dbimport;

Two additional functions allow you to retrieve the details of your connection and query strings and execute a Preview/Partial import.