4.3.1 General Importing Examplesgeneral-Import
Using X-Functions to Import Data
Origin provides a collection of X-Functions for importing data of various file formats including ASCII, CSV, Excel, National Instruments DIAdem, pCLAMP and more. The X-Function for each file format provides options relevant to the format, in addition to common settings such as setting the name of the target book or sheet to be the name of the imported file.
All X-Functions pertaining to importing have names that start with the letters imp. Detailed inforamtion on these X-Functions can be found here
Users can write their own import routines in the form of X-Functions. If the name of the user-created X-Function begins with imp and it is placed in the \X-Functions\Import and Export subfolder of the EXE, UFF or Group paths, then such functions will appear in the Data: Import from File menu
All import functions and file system browser functions (dlgfile and findfiles) share a common parameter : fname. fname is a string variable which can hold a single file name and path or a list of files.
The import functions automatically handle multiple files. Each function varies in its default handling of multiple files, creating either new Books, new Sheets or new Columns for additional files. This handling can be overridden by options in the import functions.
X-Function Themes
When using the import related X-Functions from GUI, user can save custom settings to a theme file on disk. Such theme files have file extension of .OIS and are saved in the \Themes\AnalysisAndReportTable subfolder of the User Files Folder (UFF). Such theme files can then be accessed using the -t option switch for the corresponding X-function, to perform the import using the settings saved in the file.
Example:
string fn$=system.path.program$ + "Samples\Spectroscopy\HiddenPeaks.dat";
// Following assumes that a theme file named "My Theme.OIS" exists
impasc fname:=fn$ -t "My Theme";
ASCII Import
Controlling Header and Long Name
Get the US GDP data from Fed website and import by skipping the header block. The header is then put back to Comments cell of the first column.
string fname$="%YUSGDP.txt";
url$="https://research.stlouisfed.org/fred2/data/GDP.txt";
web2file;
if( exist(fname$) <= 0)
{
type "failed to get " + url$;
return 0;
}
//clean the project to start with empty space
doc -s;doc -n;{win -cd};
newbook s:=1;
// specify file header structure to import
impASC options.sparklines:=0
options.FileStruct.DateFormat:="yyyy'-'MM'-'dd"
options.Hdr.MainHdrLns:=19
options.Hdr.AutoSubHdr:=0
options.Hdr.SubHdrLns:=1
options.Hdr.Units:=0;
// header string is saved to a system string variable __HEADER, we will
// put this into 1st col's comments
range aa=1;
aa[C]$=__HEADER$;
wks.labels(LC);//show Long Name and Comments
Partial Import
The options tree for the impASC X-Function provides access to Name options and Partial Import options.
In this example, we use a dialog to gather our file names, then import a portion of each file (10 rows) and do a statistical test of the column 3 data. If the data meets our criteria, we import the whole file, otherwise we skip it.
The Filenames of the successfully imported files are stored in the Comments of the Workbook.
If you try this code with the twelve STEP##.DAT files found in the Samples\Curve Fitting folder, the code will reject seven files and import five.
newbook;
dlgfile gr:=*.dat mu:=1;
string file$;
loop(ii,1,fname.GetNumTokens(CRLF))
{
file$ = fname.GetToken(ii,CRLF)$;
impasc fname:=file$
options.Names.AutoNames:= 0
options.Names.FNameToSht:= 0
options.Names.FNameToBk:= 0
options.Names.FNameToBkComm:= 0
options.PartImp.Partial:= 1
options.PartImp.LastRow:= 10;
stats 3;
wclear msg:=0;
if( stats.mean > .01 && stats.mean < .4 )
{
impasc fname:=file$
options.Names.AutoNames:=0
options.Names.FNameToSht:=1
options.Names.FNameToBk:=0
options.Names.FNameToBkComm:= 1
options.PartImp.Partial:=0;
if( ii != fname.GetNumTokens(CRLF) ) newsheet;
}
else
{
type Skipping %(file$);
if( ii == fname.GetNumTokens(CRLF) ) layer -d;
}
}
Delimiters and Numeric Separators
The options tree for the impASC X-Function provides access to Delimiter and Numeric Separator options. These two options are in the FileStruct node of the options tree.
The options.FileStruct.Delimiter option can be one of the following values:
- 0 = Unknown
- 1 = Comma
- 2 = Tab
- 3 = Tab/Space
- 4 = Semicolon
- 5 = Other (Set the options.FileStruct.OtherDelimiter option to the character)
- 7 = Space
The options.FileStruct.NumericSeparator option can be one of the following values:
- 0 = 1,000.00
- 1 = 1.000,00
- 2 = 1 000,00
- 3 = 1'000.00
The following is a simple tab delimited data file for use in the example that follows it.
1 1,010.00 1,240.00
2 3,020.01 2,562.01
3 4,030.00 3,680.00
4 6,040.01 4,002.01
5 7,050.00 5,440.00
6 7,060.01 6,002.01
7 8,982.99 3,456.23
Before executing the following example save the sample data above to C:\tempData.txt or change the file name in the example to refer to one of your data files using the same file format.
impASC fname:=c:\tempData.txt
options.FileStruct.Delimiter:=2
options.FileStruct.NumericSeparator:=0;
The next example will demonstrate how to use the Other Delimiter option. If the sample data above was delimited with the pipe, or vertical bar, character then you would set options.FileStruct.Delimiter to 5 and then set options.FileStruct.OtherDelimiter to "|".
impASC fname:=d:\tempData.txt
options.FileStruct.Delimiter:=5
options.FileStruct.OtherDelimiter:="|"
options.FileStruct.NumericSeparator:=0;
Import Mode
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 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 */
Importing Multiple Files
Example 1
This example shows how to import multiple data files in the specified path to an Origin workbook and start new sheets for each file.
string fns, path$=system.path.program$ + "Samples\Curve Fitting\";
// findFiles will default to get from path$
findfiles fname:=fns$ ext:="step1*.dat";
int n = fns.GetNumTokens(CRLF);
string bkName$;
newbook s:=0 result:=bkName$;
impasc fname:=fns$
options.ImpMode:=4 /* start with new sheet */
options.Sparklines:=2 /* add sparklines if < 50 */
options.Cols.NumCols:=3 /* only import the first 3 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 from 4th letter */
options.Names.FNameToBkComm:=1 /* add file name to book comment */
options.Names.FNameToColComm:=1 /* and to columns comments */
options.Names.FPathToComm:=1 /* include file path to comments */
orng:=[bkName$]A1!A[1]:C[0] ;
Example 2
This example shows how to find all files with similar name sample*.csv in all subfolders and recursive folders and import into Origin worksheet, append as new rows for each file.
newbook test;
dlgPath;// Open dialog to navigate to main folder
type -a "You have choosen " + path$;
findfolders addpath:=1;
if(folder.GetLength() < 1)
{
type "No subfolders found";
break 1;
}
else
{
int nn = folder.GetNumTokens(CRLF);
type "$(nn) subfolders found";
string strTemp$;
for(int ii = 1; ii <=nn; ii++) // Run for each found folder
{
strTemp$ = folder.GetToken(ii, CRLF)$;
findFiles path:=strTemp$ ext:="sample*.csv" fname:=results;
int mm = results.GetNumTokens(CRLF);
type "A Total of $(mm) files found";
impasc fname:=results$ menu:=2 options.Impmode:=2 Options.Hdr.SNames:= 4;
}
}
After Import Convert Worksheet to Matrix
The first line generates a file dialog to let you select an ASCII file and import data into a Worksheet.
The second line directly converts from Worksheet to Matrix and sets the Matrix XY coordinates.
The options for the conversion are such that the X-Function expects linearly increasing Y values in the first column and linearly increasing X values in the first row.
(Note that 'matrix_data_with_xy.txt' in the Samples\Import and Export\ folder fulfills these requirements.)
Example:
dlgfile g:=ascii;
impAsc;
w2m xy:=xcol xlabel:=row1 ycol:=1;
Import Filters
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, to perform custom importing using the settings saved in the filter file.
Example:
string fname$, path$, filtername$;
// point to file path
path$ = system.path.program$ + "Samples\Import and Export\";
findfiles ext:="S*.dat"; // find files that match specification
// point to Import Wizard filter file
filtername$ = system.path.program$ + "Samples\Import and Export\
VarsFromFileNameAndHeader.oif";
impfile location:=data; // import all files using filter
impFile X-Function with Sample Filter
This example imports a series of data files into the project using a given file filter.
For each data file, a new sub-folder will be created in the project.
// Point to where the data files are
string path$ = system.path.program$ + "Samples\Signal Processing\";
// Find all files with wild card
findfiles ext:="TR*.dat";
int numFiles = fname.GetNumTokens(LF);
// Loop over all files
int nFirst = 1;
for(int ifile = 1; ifile <= numFiles; ifile++)
{
string filepath$, filename$;
// Get next file name
filepath$=fname.gettoken(ifile,LF)$;
// Parse out just file name without path and without extension
filename$ = filepath.GetFileName(1)$;
// Make a new PE subfolder using the file name and set it active
pe_mkdir filename$ cd:=1;
// Add a new book and import data file
// Filter for file exists in data folder
// The filter does some post processing of the data by creating
// frequency column and setting values etc.
newbook;
// Note how variable names can be truncated to unique characters
impfile fn:=filepath$ fi:="TR Data Files.oif" l:=0;
// Set PE folder to previous level and go to next file
pe_cd ..;
}
This example imports single or multiple files into the project using a given import filter. The filter has the setting
saved to determine whether each file is imported as a new sheet or new book. Before you run the code, you need to
create a filter file and save it to the same folder where the data is. It is then just two lines of code to bring in
data. The code below uses two X-Functions: dlgFile and impFile
// Open a dialog for the user to select one or more files
dlgfile group:=*.csv multi:=1;
// The filter file, myFilter.oif needs to be in the same folder as the data
impFile filtername:=myFilter.oif location:=data;
Internet Data Access
A new X-Function web2file was added that allows getting text file off the internet.
Import A Web ASCII File Into A Worksheet
The following example downloads a file from the internet using http and place it in the UFF and then import that into the active sheet.
// Copy web file to local file.
string fname$="%Y_tmp_webdata.txt";
string url$="http://hadobs.metoffice.com/hadcrut3/diagnostics/global/nh+sh/annual";
web2file;
if( exist(fname$) > 0 )
{
impASC;
del -f "%(fname$)"; // remove file once done.
}
else
ty "Failed to copy web file to local file.";
Import A Web Image Into A New Matrix
The following example downloads the OriginLab logo image from the OriginLab website and places it in the UFF and then
imports that image into a new matrix sheet.
string fname$="%Y_tmp_logo.png";
string url$="https://www.originlab.com/images/header_logo.png";
web2file;
if( exist(fname$) > 0 )
{
win -t matrix;
impImage;
del -f "%(fname$)"; // remove file once done.
}
else
ty "Failed to copy web file to local file.";
Database Access
Import from Included Sample Access Database
We have included a small Access database in the Samples folder called stars.mdb and the following code shows how to control the database access X-Functions to import into active worksheet. Please read the dbEdit X-Function for more information.
// Construct the connection string.
// Please use the Query Builder dialog to construct your connection string
string strdb$ = system.path.program$ +
"Samples\Import and Export\stars.mdb";
string strConn$="Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=%(strdb$); User ID=; Password=;";
// The SQL
string strSQL$="SELECT Stars.Index, Stars.Name, Stars.LightYears, Stars.Magnitude
FROM Stars
WHERE Stars.LightYears<=100
ORDER BY Stars.Magnitude, Stars.LightYears";
// Connect database and submit the SQL
dbEdit change conn:=strConn$ sql:=strSQL$;
// Import data
dbImport;
// Disconnect the database
dbEdit remove;
Instrument Data Import
Importing and Plotting Multiple SPC Files
The following example shows how to import multiple SPC files into separate sheets of a new book and plot all datasets
into a new graph. The code also shows how to customize the legends on these graphs.
// Use new open dialog X-Function
dlgfile m:=1 g:=*.spc; // Specify multiple files from group of *.SPC
int count = fname.GetNumTokens(CRLF); // How many files?
newbook; // Create a new book
loop(ii,1,count) {
string strFile$ = fname.GetToken(ii,CRLF)$; // Next filename
impSPC f:=strFile$; // Import
%N = %H; // Remember it's name for later use
range ss = [%H]$(ii)!; // The Sheet Range can access sheet properties
// More than one column, assume first is X
if(ss.col2.nrows > 0)
// Plot
plotxy [%H]$(ii)!2:end p:=200 o:=[<new>];
// If one column, assume it's a Y
else
// Plot
plotxy [%H]$(ii)!1 p:=200 o:=[<new>];
legendupdate u:=0 m:=custom c:="@WS"; // Update the Legend with new options
win -a %N; // Make the Workbook active
if (ii<count) newsheet; // Add a sheet if more files
}
Specify Channel Names for Import
Minimum Origin Version Required: 9.0 SR0
You can use the impFileSel x-function to get the channel structure as a tree variable before import, and modify the tree variable to specify channel names for import. This function works with the imppClamp, impMDF, impMatlab, impNITDM, impNIDIAdem and impPrism Import X-Functions.
The following example shows how to specify channels when importing multiple pClamp data.
newbook;
// Set fname string variable so function knows which file to examine
fname$ = system.path.program$ + "Samples\Import and Export\pClamp\93310C08.DAT";
// Use the x-function to store the file structure in the Labtalk tree variable "tt"
impFileSel xfname:=imppClamp trFiles:=tt;
// View the channel names and labels for each channel
//@tnd=2;
//tt.=;
// Change the file structure by excluding the first channel.
// "ChannelName1" is the channel name for the first channel.
// Changing the value to 0 will block the import of this channel
// The default value of 1 allows import
for(int ii = 1; ii<=2; ii++)
{
if (ii == 1)
continue;
tt.Channels.ChannelName$(ii) = 0;
}
// Chosse two files include 93310C08.DAT and 93311C01.DAT
folderpath$=system.path.program$ + "Samples\Import and Export\pClamp";
dlgfile group:="9331*C0*.dat" multi:=1 init:=folderpath$;
int numFiles = fname.GetNumTokens(LF);
// Loop over all files
int nFirst = 1;
for(int ifile = 1; ifile <= numFiles; ifile++)
{
string filepath$, filename$;
// Get next file name
filepath$=fname.gettoken(ifile,LF)$;
newbook;
// Import with the modified file structure
imppClamp fname:=filepath$ trFiles:=tt;
};
Image Files
Import Images
ImpImage can import image format files to Origin.
It supports multiple files importing. By default, multiple images will be appended to the target Page by creating new Layers. And each MatrixLayers' will be rename to the file name.
Example1:
This example imports a series of TIF Images to a new Matrixbook.
newbook mat:=1;
string fns, path$=system.path.program$ +"Samples\Image Processing and Analysis\";
findfiles fname:=fns$ ext:="myocyte*.tif"; // findFiles will default to get from path$
int n = fns.GetNumTokens(CRLF);
impimage options.Mode:=4 fname:=fns$
Example2:
This exmaple imports a folder of JPG images to different Matrixbooks.
string fns, path$="C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\";
findfiles fname:=fns$ ext:="*.jpg"; // findFiles will default to get from path$
int n = fns.GetNumTokens(CRLF);
string bkName$;
string fname$;
for(int ii = 1; ii<=n; ii++)
{
fname$ = fns.GetToken(ii, CRLF)$;
// Create a new matrix page
newbook s:=0 mat:=1 result:=bkName$;
// Import image to the first layer of the matrix page,
// Default file name is fname$
impimage orng:=[bkName$]msheet1;
}
Convert Imported Image into Data
This example imports a bitmap and conver the image into a byte matrix so you can do further processing:
dlgfile g:=*.bmp; // Opens dialog to select a file and put into fname$
newbook m:=1; // Create a new matrixbook
impImage; // Import image into active matrixbook using fname$
img2m o:=<input> t:=1; // Convert image into byte data
Text Files
Open a Text File Into a Notes Window
- open -n fileName [winName]
This script will open an ASCII file to a notes window. If the winName is not specified, a new note window will be created.
Example:
%b = system.path.program$ + "Samples\Import and Export\ASCII simple.dat";
open -n "%b";
Import Video
Minimum Origin Version Required: 9.1 SR2
This example will create a new matrixbook and load frames from a user specified video file into multiple matrix objects in the active sheet.
You can easily modify the code for your own file, as well as to specify either to load specific number of frames, or by an increment in time.
string vfilename$ = "d:\test.avi";
//If set ByCount to 0 then read by increment
//1 will read TotalFrames to the end
int ByCount = 1;
int TotalFrames = 100;
double StepSize = 15; // Seconds
// Create a new matrixbook.
string matbk$;
newbook mat:=1 result:=matbk$;
int skip;
// Open the video file.
int err = vr.Open(%(vfilename$));
if( 0 == err )
{
if( ByCount ) // Read TotalFrames frames to the end by skipping.
{
skip = vr.FrameCount/TotalFrames;
type "Read total of $(TotalFrames) frames,
Skip every $(skip) frames or $(skip/vr.FPS) sec.
File has $(vr.FrameCount) frames";
}
else // Read 1 frame then skip StepSize sec.
{
skip = StepSize * vr.FPS;
type "Read total of $(TotalFrames) frames,
Skip every $(skip) frames or $(StepSize) sec.
File has $(vr.FrameCount) frames";
}
// Read from current pos (beginning)
// to the end of the file (frameCount=0) into matrix.
vr.ReadFrames(%h, TotalFrames, skip);
vr.Close();// Close the video file.
}
page.cntrl=1; // Image view mode.
matrix -it 1; // Show thumbnails.
Reimport
Using Reimport to Bring back all Data
This example brings back all data that was last imported into the active workbook.
To try this, use "Data: Import from File: multiple ASCII" and bring in some data to a workbook.
Be sure to use Show Options and set the Import Mode to 'Start New Sheets'.
Use the Worksheet : Clear Worksheet menu option on each sheet.
Run the following script:
loop (i, 1, page.nlayers)
{ // loop over all sheets in the book
page.active = i; // active sheet
reimport; // reimport to bring back data in active sheet
}
Watching for a File change for reimport
This example imports a file then watches for changes to that file to reimport. The script makes use of a programmable macro named timerproc which can be set to run at specified intervals.
// Bring in some data and plot it
string fname$ = D:\Sample.dat;
impasc;
range raIn = !; // For reimport
plotxy (1,2) 202;
double olddate = exist(%(fname$),5); // Note the file date-time
// Now program the macro to reimport if file changes
def timerproc
{
double newdate = exist(%(fname$),5); // Current file date-time
if(newdate != olddate)
{
reimport or:=raIn; // reimport if the file has changed
olddate = newdate;
layer -a; // Update the graph (assumed active)
}
else ty Nothing new ...; // Delete this line if not needed
}
// And start the timer to watch the file
timer 1800; // Run timerproc every 1800 seconds (every 30 minutes)
Should you have to stop the process, execute this command:
timer -k;
|