2.10.1 Importing Data
The following examples demonstrate the use of X-Functions for importing data from external files. The examples import ASCII files, but the appropriate X-Function can be substituted based on your desired filetype (i.e., CSV, 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
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\";
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";
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
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
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
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 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
// 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
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
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"
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;
// The dbimport function is all that's needed to complete the import
Two additional functions allow you to retrieve the details of your connection and query strings and execute a Preview/Partial import.
|| Brief Description
Create, Edit, Load or Remove a query in a worksheet.
Execute the database query stored in a specific worksheet.
Read the sql string and the connection string contained in a database query in a worksheet.
Execute a limited import (defaults to 50 rows) of a query. Useful in testing to verify that your query is returning the information you want.