8 Import Data
Other supported import mechanisms:
- OriginLab continues support for Origin's long-standing X-Function + filter-based routines for ASCII, CSV, Excel, binary files, and many third-party formats, including MATLAB, pClamp, NI TDM and NetCDF. If you are a long-time user and you have import filters and Themes that you have long relied on, they should still work as they always have.
- Copy-paste for quick import of simply-structured data (mostly useful for quickly adding data to worksheets).
- For complex ASCII, binary or User-defined file types, you can use the graphical interface of the Import Wizard to parse your file and save settings to an import filter file. The Import Wizard supports power features such as saving file metadata to variables and use of custom Origin C functions for data processing and import.
- If you work with SQL databases, you can use Origin's Database Connector and SQL Editor interface to create connection strings and SQL queries.
A Note to Upgrade Users
Longtime Origin users who are upgrading from older versions should note that import routines have moved from the File menu to a new Data menu. Here, you will also find the new Data Connectors.
Data Connectors are a general mechanism for importing data to your Origin projects from local or from web-based files and pages. Data Connectors support a number of common data formats, including:
- HTML Table
- Origin File (project, workbook, matrix)
- MATLAB (v7 .mat files)
To connect to SQL database, see Database.
To import data with a Data Connector:
- With your workbook active, click the Data menu.
- If you are connecting to a local or network file, click Connect to File and pick a file type.
- To connect to multiple files, click Connect Multiple Files.
- If you are connecting to web data, click Connect to Web and enter a URL.
Data: Connect Multiple Files now lets you pick Files in a Specified Folder, as opposed to having to select individual files.
Things to Know about Data Connectors
- By default, when import data file size exceeds 500kB, the imported data and graphs made from that data are NOT saved with the Origin file (see LabTalk system variable @FSE). All analysis output resulting from imported data ARE saved with the Origin file. Not saving raw data with your project file can significantly reduce file size and improve performance when working with large projects.
- IMPORTANT: Since, by default, connector-imported data is not saved with the project file, users should always ask themselves whether they need to preserve imported data. This is particularly true if the data source is web-based and stored in a place beyond your control. If you prefer to save all data imported via a Data Connector, with your Origin file, click the Data Connector icon and clear the check mark beside Exclude Imported When Saving prior to saving your file†. Note that each Data Connector-containing book will have its own Exclude... check mark. Alternately, if you decide the Data Connectors are not needed, you can click the Data Connector icon and choose Remove Data Connector (from this book) or Remove Data Connectors from All Books (in the project).
- Data can be re-imported at any time by clicking the Data Connector icon ( or ) in the upper left corner of the workbook and choosing Import (Ctrl+4) or Import All.
- By default, imported via a Data Connector is locked from editing. You can graph it and analyze it but you cannot modify it. While this ensures against accidental modification, some users may find this too restrictive. See this FAQ for information on removing editing restrictions from Connector-imported data.
- A workbook can have only one type of Connector (e.g. Text/CSV). For files with a simple structure such as Text/CSV, you may have multiple sheets connecting to multiple files as long as they are of the same type. For local Excel files with multiple sheets and MATLAB files containing multiple variables, you can connect to one sheet/variable, then use the Data Navigator's Add and Connect Sheet(s) shortcut menu command to add other sheets/variables in the file, one-by-one. You can only connect a single Excel or MATLAB file to a single Origin workbook or matrixbook.
- Connectors to web files (Data: Connect to Web) do not support the Data Navigator panel.
- If you right-click on a sheet tab and add a new sheet, the sheet is added with an empty Connector. The unconnected state is denoted by a pale-yellow Connector icon . Click on the Connector icon to open a shortcut menu where you can connect the sheet to a data source.
- Data Connectors that you do not use can be hidden by choosing Data: Connect to File: Customize... and clearing the checkbox next to unused Connectors.
- Data Connectors for formats not shipped with Origin, are available by clicking Data: Connect to File: Add New.... New Data Connectors are added to the Data menu.
|Note: The Data Connector icon appears in four states:
- When the data source is viable, whether data have been imported or not, the icon looks like this:
- When a data file that was previously imported via Connect to File is modified, the next time you open the Origin file, the icon will look like this†:
- When a new sheet is added to a Data Connector-containing book, an empty Connector is added to the sheet. When the Connector is empty, the icon looks like this:
- When the data source is no longer viable (e.g. the data source has been deleted or renamed), the icon looks like this:
†Origin caches data imported by Data: Connect to Web, for a period of time determined by LabTalk system variable @CFDT, plus file size. By default, a 10MB file will use cached data for ~24 hrs. In other words, if you re-import (i.e. reconnect to the web file) within 24 hrs, you will be using data that was cached on first import. If you re-import to this 10MB file after 24 hrs passes, the cache is cleared and the data are freshly imported. To learn how to control this caching behavior see, these topics:
Data Source File Path
Data Connectors require a viable connection to the source data; if a file is renamed or deleted, the Connector will be broken and upon re-opening your project, you will see the red Connector icon .
In any state, you can click the Connector icon, then use the Data Source File Path and Common Data Path to help you maintain the connection between your Origin files and your source data.
- Relative to Project Path: If your data are stored in folders under the project file, you could set Data Source File Path to Relative to Project path. You can then move the project and data sources together, and their relative paths are preserved. No path corrections are then needed when re-opening the project file from a different location on your network.
- Relative to Common Data Path: If data sources are under some common path, you could set Data Source File Path to Relative to Common Data Path. If the files are subsequently moved, you can re-establish the Common Data Path and then Import All Books.
The Data Navigator is a collapsible panel that is automatically added to the workbook when you connect to locally-stored, multi-page/multi-variable files such as Excel, MATLAB or Origin Projects.
- Origin will import data from the first sheet and list other sheets in dimmed type.
- Right-click on a dimmed sheet -- or press CTRL or SHIFT and select multiple sheets -- then choose Add and Connect Sheets.
- The file name is assigned to the book Long Name and sheets are the original sheet name is assigned to sheet Name.
- Connected sheets can be deleted from the current Origin workbook.
Data Connectors and Drag-and-Drop
Origin 2021 includes better drag-and-drop support for DAT file types when no import filter is specified. Previously, this would trigger a prompt to open the Import Wizard. Now, the file is imported using a Connector.
There is drag-and-drop support for the file types listed under Data: Connect to File. If you are using an older import filter for drag-and-drop import (see below), Origin will give priority to that filter as long as it is saved to the template, to the data folder or to the User Files\Filters folder (see this FAQ).
Note, however, that Data Connectors offer some advantages for drag-and-drop such as automatic column formatting of date-time data in CSV files and automatic import of MATLAB variables to Origin matrix objects and the addition of a slider control for viewing images in sequence.
For more information, see these topics:
ASCII files such as TXT, CSV and DAT can be imported by Data Connect to File or the older Data: Import from File methods. Data Connector import of CSV is easier, particularly when the file contains date-time data as date-time columns are automatically set to date-time Format. More details on filter-based ASCII import methods are covered below.
- The Text/CSV Connector (Data: Connect to File: Text/CSV) is supported by multi-threading.
- For large files, there is a checkbox to Import first 50 lines as preview.
- You can define a Data Block Marker ( e.g. [Data], >>>>Start<<<<<<, etc.) to mark the start and end of the data portion of your file that you wish to import.
- When you opt for partial file import, both Data: Connect to File and Data: Import from File share a flexible syntax for defining only those rows and columns that you wish to import. There is in-dialog assistance (expand the Partial Import branch) or you can visit this page in the Origin Help file.
To import locally-stored Text or Comma-Separated (CSV) data:
- Choose Data: Connect to File: Text/CSV.
- Choose Data: Import From File: Comma Delimited (CSV)....
To import web-stored CSV data:
- Choose Data: Connect to Web, set File Type to Text/CSV (helpful for filtering URLs by file type) and either (a) click on Recent URL and pick from the list or (b) paste a copied link into the URL box.
You can use the CSV Import Options dialog to specify a partial import of web-stored files just as you can for locally-stored files. Check the Partial Import box for guidance on the syntax used to define Column and Row import.
There are five basic options for working with Excel data in Origin:
- You can copy Excel data to the Clipboard and paste or paste link that data into Origin.
- You can import a local Excel file by clicking Data: Import From File (or click the button located in the Standard toolbar).
- You can use a Data Connector to import a local Excel file by clicking Data: Connect to File: Excel or by dropping your Excel file onto the Origin workspace.
- You can use a Data Connector to import Excel data from the web by clicking Data: Connect to Web: Excel (see below).
- You can also open your Excel workbook in Origin as an Excel workbook (must have MS Excel installed). To do this, use the File: Open Excel... menu command.
When you paste or import your Excel workbook data into an Origin workbook (including import by Data Connector), you have full access to Origin's powerful graphing and analysis features. When you open your Excel workbook as an Excel workbook, you are actually running an OLE instance of Excel inside of the Origin workspace. In this case, you have access to Excel's menus and features, but very limited access to Origin's graphing and analysis features. For most users, choosing one of the Excel import options (first four options) will be preferable to opening the Excel workbook in Origin as an Excel workbook.
Things to know:
- To open an Excel file in Origin as an Excel workbook, you need to have Office 97 or later installed on your computer. However, you can import an Excel file into Origin without having Excel installed.
- Origin imports files of type .xls, .xlsx or .xlsm.
- You can link your Origin project file to an external Excel file. That way, updates to the Excel file will trigger updates to dependent graphs etc. when you open the Origin project file.
- Origin does not support dynamically linking a formula in an Excel workbook to source data in another Excel workbook.
- Certain file import information is saved to the workbook on import. Having once imported an Excel file to an Origin workbook, you can use the Re-import feature to re-import an updated Excel file -- including those that contains multiple sheets -- to your Origin workbook.
You can do partial import of your Excel data by delineating one or more row and column ranges. A flexible syntax is provided for including or excluding data ranges. For information, choose Data: Import From File: Excel (XLS, XLSX, XLSM), check Show Options Dialog and choose a file for import. In the impMSExcel dialog, expand the Partial Import branch and note the in-dialog syntax hints under Custom.
Connecting to Excel Files
Click Data: Connect to File or Connect to Web to connect to local or web-stored Excel files.
- You can only connect one Excel file per Origin workbook.
- If the file is a multi-sheet file, you must first connect to a single sheet. This opens the the Data Navigator panel (described above) where you can connect to additional pages, as needed.
- If all sheets are identically structured, taking the time to setup the connection to the first sheet, will allow you to easily connect additional sheets.
Connecting to MATLAB Files
While Origin continues to support older X-Function based MAT file import, Data Connectors offer some distinct advantages for import of both locally-stored and web-stored MATLAB files:
- Connecting to a MATLAB file at the root level will add all variables as Origin matrix objects in the matrixsheet.
- A slider is added so that you can flip through Origin matrix objects in the sheet. You can toggle between slider and thumbnails using the Slider or Thumbnails Mini Toolbar button.
- The Connector also adds a collapsible Data Navigator panel to the window. If you wish to add variables as individual sheets, you can right-click on the Data Navigator list and add them.
Origin 2021 includes these improvements to the MATLAB Connector:
- Support for opaque variables (Date, Time, Duration, Table, etc.) in v6/v7 files.
- Support for importing tables from .MAT files.
Import from File > ASCII
Data Connectors can handle import of ASCII file types, but some users may be invested in the older import methods (e.g. you use an import filter to import data and have no need for protecting data). In such cases, you may choose to import data using the Data: Import From File menu commands.
The Data: Import From File: Single ASCII... and Data: Import From File: Multiple ASCII... methods are generally satisfactory for importing text files having a simple structure. These methods are also accessible from the and buttons located in the Standard toolbar. These methods will typically handle files with commonly-used delimiters and a simple header structure, without adjustments.
If a file imports with only minor complications, select the Show Options Dialog checkbox when selecting your files in the ASCII dialog (Data: Import From File: Single ASCII... and Data: Import From File: Multiple ASCII...). This will open the impASC dialog box prior to file import, allowing you to tweak the way that the files are handled.
Once you've adjusted import settings, you can save them in one of two ways: (1) with the worksheet template (including saving for drag-and-drop) or (2) as a Theme file from within the impASC dialog box. Saving import settings with the worksheet template offers portability (e.g. templates are easily shared with colleagues). Saving import settings as a Theme offers the advantage of being able to recall any number of import options combinations when importing via the impASC dialog box.
For more information on Themes and templates, see the Customizing Origin chapter later in this User Guide. Additionally, see topics listed under "Topics for Further Reading", below.
|Note: For a more in-depth discussion of simple ASCII import, see the help topic:
Help: Origin: Origin Help > Importing and Exporting Data > Importing Simple ASCII Data Files
For import of complex ASCII files -- those not satisfactorily handled by the methods previously mentioned -- you can use the interactive Origin Import Wizard (Data: Import From File: Import Wizard...). The Wizard is also accessible from the button located in the Standard toolbar. Apart from its advantages for import of complex files, the Wizard also offers the following:
- Using wildcards, you can create import filters that are targeted to a group of similarly-named data files. This filter can then be used for drag-and-drop import operations: When you drop the appropriate files onto the workspace, Origin looks at the file name, matches it with the correct custom import filter and imports the file according to your specifications.
- Import data from the web pages using the Clipboard.
- Extract variables from the import file name and header.
- Specify custom delimiters and date formats to parse your data.
- The ability to post-process your imported data using a custom LabTalk script.
|Note: For more information, view the help topic:
Help: Origin: Origin Help > Importing and Exporting Data > The Import Wizard
You can quickly load your custom import theme from the list at the bottom of the Data menu. You can also use the Data: Import from File: Add/Remove File Types ... menu to open the Import Menu Customization dialog box where you can select and add User Themes to the Data: Import From File menu.
Third Party Formats
Origin 2021 adds a couple of new Connectors for import of third-party files:
- New TDMS Connector for import of National Instruments TDMS files.
- New DIFFRAC Connector for Import of Bruker DQL/RAW files.
Origin imports many widely-used third party data formats, including pClamp, NI TDM, and NetCDF. As with ASCII import, you can customize third-party file import options and save your settings with the worksheet template or as a dialog Theme for later use.
As with other supported file types, you can import third party files by drag-and-drop.
| File Type
|| To Learn More, see
Help: Origin: Origin Help > Importing and Exporting Data
Thermo (SPC, CGM)
pCLAMP (ABF, DAT, AB?)
Princeton Instruments (SPE)
HDF5 (H5, HE, HDF5)
NI DIAdem (DAT)
NI TDM (TDM, TDMS)
Famos (DAT, RAW)
ETAS INCA MDF (DAT, MDF)
IgorPro (PXP, IBW)
Prism (PZFX, XML)
Minitab (MTW, MPJ)
MZXML(mzData, mzXML, mzML, imzML)
EDF (EDF, BDF, REC, HYP)
Somat SIE (SIE)
JCAMP-DX (DX, DX1, JDX, JCM)
Data Translation (DCF, HPF)
Binary 2D Array (IMG, B2D)
| Data: Import from File:
|| > Importing Third Party Files
|Note: Some of these 3rd party file formats are hidden by default. To add, choose Data: Import From File: Add/Remove File Types.... This opens the Import Menu Customization dialog box where you can choose which file types to show in the Import From File menu. To insert a separator after a menu command, right-click on the command in the right-hand panel and choose Add Separator. To remove a separator, double-click on it in the right-hand panel.
Drag-and-drop support is managed by file extension, using the Filter Manager dialog. From the main menu, click Preferences: Import Filters Manager....
Data: Import From File: Import Wizard...).
Files with an optional header section followed by a sequential binary structure, can be imported using the Origin Import Wizard (
If the binary file format is commonly used in your field of work, please contact your Origin representative so we can consider adding support for such files in future versions.
Copy/Paste the Clipboard Data
One obvious way to get data into Origin is simply to copy data to the Clipboard and paste it into Origin. This can be as easy as selecting a block of data in an Excel workbook and pasting it into an Origin workbook. However, Clipboard data may not be formatted, arrayed or delimited in a way that makes it easy to paste your data into Origin. Here are some things you should know:
- Data copied from Excel and pasted to Origin will be brought over with full precision, regardless of the number of displayed decimal digits in Excel.
- When working with Excel files with header information, paste the Clipboard data into data row 1 in Origin (not into the column label rows), then select a row and use the (right-click) shortcut key commands Set as Long Name, Set as Units, Set as Comment, etc. to tag your header information.
- Data copied from the web can be formatted in various ways. You may have to experiment a bit to find a method that brings the data over cleanly. If a simple copy and paste doesn't work, try using the Import Wizard. The Import Wizard provides an interactive interface for parsing Clipboard data, with the added benefit that you can save your procedure to an import filter file (.oif) for reuse with identically formatted data. For more information, see the tutorial Using the Import Wizard to Import Clipboard Data.
Selectively Import Data by Querying an Origin File
You can selectively import columns of data from an external Origin file by specifying an SQL-like Query in the Data Connector Browser prior to import.
To selectively query Origin files using the Origin Connector:
- With the worksheet active, choose Data: Connect to File or Connect to Web.
- Browse to your Origin file and click Open. This opens the Data Connector Browser.
- Click the Select Query button .
- Enter your Query in the Select Query dialog, then click OK.
Data will be selectively imported into the active workbook as specified by your query.
||short name, e.g. _SName, Sheet_SName
||long name, e.g. _LName, Book_LName
||comments, e.g. _Comments, Sheet_Comments
||column user-defined parameter, where 1 is the parameter index
||column user-defined parameter, where [Name] is a parameter name containing spaces
Select Column from Project where _SName == "A";
Select Column from Project where _Comments like "S*3";
Select Column from Project where (Column_P1 like "*y*" and Column_D1 > 3);
Select Column from Project where Sheet_SName like "Trial*";
Select Column from Project where (Book_SName like "Book*" or Book_LName like "*fit*")
For more information, see Examples of using SQL query condition in Origin Connector.
Origin supports connecting to databases by ODBC and OLE DB. Additionally, Origin includes a simple SQL Editor to add connection strings and generate SQL queries within Origin.
To import SQL data, start with the Data: Connect to Database menu command:
- Open lets you browse for and open a saved connection (ODS) or query (ODQ) file.
- New opens the Database Connector dialog.
- Connection string: Select this if you already have a text-based connection string for a given data source (ODBC or OLE DB).
- ODBC data source: Select this if you have defined an ODBC data source via the Windows ODBC Data Source Administrator applet.
- ODBC driver: Select this if you have an ODBC driver and want to create a data source applicable to this data connector instance.
- Windows Data Link Properties dialog: Select this if you are using an OLE DB-based data source such as some SQL Server or Access client libraries.
Other things to note:
- Use the LabTalk system variable @IPDB to control protection of imported range (0 = no protection, 1 = protected but allow reorder, 2 = protected).
- Similar to other Connectors, you can opt to Exclude Imported (data) When Saving as OPJU/OGWU.
Digitizer tool may prove handy. Use the Digitizer to import an image file, then digitize the plots, converting your image to numeric data that you can study and re-plot using Origin's analysis and graphing tools.
At times, you may only have a graphic representation of some data that you would like to explore further. In such cases, Origin's
- Import most common image formats.
- Rotate the imported image, remove noise and calibrate scales by picking start and end values.
- Pick points on curves manually or automatically.
- Manually add curve inflection points, as needed.
- Interactively remove points that you do not want digitized.
- Support for Cartesian, polar and ternary coordinate systems.
To open the Digitizer:
- Click the Digitize Image button on the Standard toolbar or choose Tools: Digitizer... from the main menu.
Saving Origin Files without Imported Data
Users have long had the option of saving Origin files without data. Save Template As..., Save Workbook As Analysis Template and Save Project without Data, were three options for saving Origin files without data while preserving settings and operations with the file.
Users now have the option to save Origin files without imported data. This is a particularly attractive option for those who work with large data files, where saving of that data with the file would hinder performance or waste disk space.
- By default, data imported using a Data Connector, will not be saved with the book when the import file size exceeds 500kB. Each book has its own Exclude Imported When Saving check mark and clearing the check mark on any page in the book will cause all imported data to be saved with the book.
- Any book in the project -- not only those with Data Connectors -- can be configured to exclude imported data by right-clicking on the window title bar, selecting Properties, then checking the Exclude imported data when saving project box at the bottom of the dialog.
The act of excluding data when saving differs from saving a template file in a couple of important ways:
- When you exclude imported data, you are still saving all output resulting from analysis operations on imported data -- fitting results, statistical analyses, Set Values calculations, etc. -- you just don't save imported data or graphs of that imported data.
- Books which you have saved without imported data retain a "live" link to the original data source. For Data Connector-containing books, click the Data Connector icon and click Import (active sheet) or Import All (all sheets in book). For books used for standard file import, activate the empty book and click Data: Re-import Directly; or click the Re-Import no dialog button on the Import toolbar (see next).
Re-Importing Data Files
Data menu that are specifically intended for repeat import of the same file (i.e. the file path and name must be the same):
There are two import commands in the
- Re-Import Directly. When you import a file into an Origin worksheet or matrixsheet, by default a number of parameters, including file path and name, are saved in the sheet. If you have an external data file that is periodically updated or you want to reload a standard file into the worksheet, you can re-import directly without opening an import dialog.
- Re-Import.... This menu command opens the corresponding X-Function import dialog box for the file type so that you can adjust your import options before re-importing.
- Only one file can be re-imported at a time. When you use either of these two menu commands, only the file associated with the active worksheet or matrixsheet, will be imported. If the selected data are imported from multiple files, re-importing will fail.
- If you import an ASCII file via the Import Wizard and select Data: Re-Import..., the Import Single ASCII dialog will open instead of the Import Wizard.
- Re-import does not work for Binary or User Defined data types brought in using the Import Wizard.
Clone import works by "modeling" an import -- and optionally, an associated analysis operation -- and then applying what was done to the initial file, to a series of similar files. As long as the active book contains imported data and associated operations, it can be cloned. In this sense, cloning is similar to creation of an Analysis Template but is somewhat more limited in power (e.g cannot be used with the Batch Process tool).
Clone Import gets Data Connector support in Origin 2021.
Clone import is supported by both Data Connector and X-Function-based import methods. A brief tutorial will show you the basics of cloning import and analysis operations.
- Click the New Workbook button to open a new workbook.
- Using Window's File Explorer, browse to your <Origin Program>\Samples\Curve Fitting folder and find a file called Sensor01.dat. Drag-and-drop the file onto the new workbook. The .dat file is imported using the Text/CSV Connector.
- Click the column B header to select the column, then click the Scatter button to create a scatter plot.
- Click on the X or Y graph axis scale and in the Axis dialog box that opens, click the Scale tab, then press Ctrl and select both Horizontal and Vertical axis icons in the left panel. Set Rescale to Auto and click OK.
- With the graph active, click Analysis: Fitting: Linear Fit and open the dialog box. Set Recalculate = Auto, accept other default settings and click OK. Answer No to the Reminder Message and click OK.
- Right-click on one of your workbook tabs and click Add Graph as Sheet. This will add your scatter plot with the linear fit results, as a page in your workbook.
- Make sure the sheet with data imported from Sensor01.dat is active and click the Clone Import button on the Import toolbar.
- Browse, once again, to your <Origin Program>\Samples\Curve Fitting folder, press Ctrl or Shift and select files Sensor02.dat - Sensor07.dat. Click Add Files and then click OK. What you should see is that using the original book as a model for import and analysis operations, the Sensor files are imported using the Text/CSV Connector and, one-by-one, they are processed (it may take a few seconds for all operations to go to completion).
Topics for Further Reading