8 Import Data
Thus, for Origin 2020b, your data import options include:
- Copy-paste for quick import of simply-structured data (mostly useful for adding data to worksheets).
- Drag-and-drop file import is supported by most file types. Additionally, drag-and-drop support has been added for Data Connectors. When you drop a file type for which there is Connector support, you will be prompted to choose between using a Connector and using an Origin import filter (OIF). Whatever you decide, you will only be prompted at the first instance. Note that some complex ASCII, binary or User-defined file types may require use of a custom OIF created using the Import Wizard.
- Local or network-stored data files can be imported using one of Origin's X-Function based import routines. The associated import dialogs allow you to tweak import options and save them as a Theme file for later use. Themes are added to the Data: Import From File menu allowing you to import files without opening a dialog box.
- The interactive Import Wizard is useful for importing complexly-structured ASCII, Binary and User-defined files. As a bonus, you can save every step and adjustment made to data handling, to a Origin Import Filter file (OIF) that can be used to re-import similar files in the future. Import filters can be made for drag-and-drop import.
- Data Connectors allow you to establish a live link to data stored locally, in network files or in web pages; and to work with that data without having to store that imported data in the Origin project. Data brought in using a Data Connector is also locked against editing, providing improved security to those who are concerned with data integrity. Further, if your data are stored in large Origin projects and you have a need to pull out just a subset of that data for further analysis or graphing operations, you can now use an SQL-like query to pull just the most-needed data from the project.
- If you work with SQL databases, you can use Origin's 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 functions were recently moved from the File menu to a new Data menu. The menu reorganization should not affect any of your previously-saved Themes or import filters. Your familiar import routines are now found under Data: Import From File.
A new way of importing data -- including import of web-based files and tables -- was added in Origin 2019b. The new import routines are known as Data Connectors. By default, Data Connectors DO NOT store imported data with the project file when the imported data file exceeds 500kB (this threshold is adjustable using LabTalk system variable @FSE). By not saving big chunks of data redundantly, you can open and save your projects more quickly and project file sizes are much reduced. If you prefer to save all data with the project, you can do that too (see the IMPORTANT note under Overview of Data Connectors).
We will point out that multi-threaded import was recently added for CSV Connector and ASCII files (Data: Import From File Single ASCII/ Multiple ASCII/Import Wizard). This should significantly reduce import times if you are working with large data files.
Data Connectors are a general mechanism for importing data to your Origin projects from local or from web-based files and pages. At present, Data Connectors support a number of common data formats, including:
- HTML Table
- Origin File (project, workbook, matrix)
- MATLAB (v7 .mat files)
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.
You can connect to multiple CSV, Excel or MATLAB files by choosing Data: Connect Multiple Files. Check the Same Book box to import files into the same book. Open Dialog for First File to tweak import settings.
Data Connectors and Drag-and-Drop
There is drag-and-drop support for the file types listed under Data: Connect to File. If you are using older import code to for drag-and-drop import, Origin will give priority to that code as long as it is saved to one of these places. Note, however, that Data Connectors offer easy drag-and-drop import, particularly of CSV files, including those that contain date-time data.
For more information, see these topics:
Overview of Data Connectors
Regardless of Connector type, the following are true 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 connector-imported data may not be 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.
- Data imported via the Data Connectors is locked from editing. You can graph it and analyze it. You just can't modify it. This is a big plus for those concerned with data integrity.
- A workbook can have only one type of connector (e.g. HTML Table) but each sheet in the book can connect to a different data source of the same type (i.e. a different CSV file, or a different HTML page).
- When you add a new sheet to a Data Connector-containing book, 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 for formats not shipped with Origin, are available by clicking Data: Connect to File: Add New.... Other formats will be added over time. Data Connector Apps that you install are added to the Data menu automatically.
|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 does not check to see whether web-based files have been modified. Therefore, if you DO opt to save imported web data with the file, upon reopening the file you should click the Data Connector icon and choose Import or Import All. This will open a connection to the source data and refresh your Origin project. Any dependent analysis operations and graphs will be also be updated.
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. 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.
New in Origin 2020b, the Data Navigator is a collapsible panel that is automatically added to the workbook when you connect to multi-page 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.
Origin now supports multi-threaded importing for ASCII import (Data: Import From File: Single ASCII/Multiple ASCII/Import Wizard). This should significantly reduce import times for large text files.
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 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.
- The 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.
- When you opt for partial file import, both Data: Import from File and Data: Connect to 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.
- 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.
- Choose Data: Import From File: Comma Delimited (CSV)....
- Choose Data: Connect to File: Text/CSV.
- Choose Data: Connect to Web and set File Type to Text/CSV.
You can 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.
For Origin 2020b, the Text/CSV Connector adds wild-card support for data block markers. Start and End keywords now support wildcards asterisk (*) and question mark (?).
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 one sheet at a time, by clicking Data: Connect to File: Excel or by dropping your Excel file onto the Origin workspace. In addition to sheet, the Excel Connector lets you specify the location of Long Names, Units and Comments before importing.
- You can use a Data Connector to import Excel data from the web by clicking Data: Connect to Web: Excel.
- You can open your Excel workbook in Origin as an Excel workbook. 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.
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.
Third Party Formats
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....
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 now supports direct connections to an ODBC data source, including by specifying an ODBC connection string. Previously, setting up an ODBC connection required going through the Windows Data Link Properties dialog box.
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.
Topics for Further Reading