8 Import Data

Import grand image4 650px.png

Origin 2019b supports an expanded list of options for data import. In addition to the many file types that you could import with previous versions -- ASCII, CSV, Excel, binary files, and many third-party formats, including Matlab, pClamp, NI TDM and NetCDF -- Origin 2019b includes a new, generalized data import mechanism we call Data Connectors. Data Connectors support import of previously-unavailable data sources, including HTML Tables and JavaScript Object Notation (JSON) data from the web.

Thus, for Origin 2019b, 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. Some complex ASCII, binary or User-defined file types may require use of an Origin Import Filter (OIF), created with 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.
  • Two options exist for connection to SQL databases: SQL Editor is a simple SQL editor to create connection strings and SQL queries with an option to include LabTalk substitution; and Query Builder, a graphical dialog that helps users create complex queries (32-bit Origin only).

A Note to Upgrade Users

In addition to Data Connectors, longtime Origin users will notice another significant change to data import for Origin 2019b. All data import operations have been moved from the File menu to a new Data menu, dedicated to data import. The menu reorganization should not affect any of your previously-saved Themes or import filters and your familiar import routines are to be found under Data: Import From File.

Data Connectors

Data Connector General Concept.png

Data Connectors, new in Origin 2019b, 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:

  • CSV
  • HTML Tables
  • JavaScript Object Notation (JSON)
  • Origin File (project, workbook, matrix)

... and more


Regardless of data format, the following are true for data imported via one of Origin's Data Connectors:

  • Imported data and graphs of that data are NOT saved with the Origin file, by default. 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 projects.
  • IMPORTANT: Since connector-imported data are NOT saved with the project file, by default, users should ALWAYS ask themselves whether they need to preserve the 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 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.
  • Data can be re-imported at any time by clicking the green Data Connector icon DC Icon Live.png 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 reorganize it, 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 (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 Connector Shortcut Menu.png
  • Data Connectors for other formats will be added over time. Support for new file formats will be made available as Add-on Apps, so can periodically check the Add New... menu item to see what has been added. Data Connector Apps that you download will be added to the menu automatically.
UG data connector add new.png
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: DC Icon Live.png
  • 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: DC Icon DataMod.png
  • 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: DC Icon Empty.png
  • When the data source is no longer viable (e.g. the data source has been deleted or renamed), the icon looks like this: DC Icon Broken.png

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, you should click the Data Connector icon and choose Import or Import All. This will open a connection to the source data and update 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.

UG Common Data Path Dialogs.png
  • 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.

ASCII Import

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 Button Import Single ASCII.png and Button Import Multiple ASCII.png 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.

Import data 1.png

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 Import Wizard.png 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

Tutorial: Importing Multiple Files into Worksheets

  1. Click on the New Workbook button Button New Workbook.png on the Standard toolbar to open a new workbook.
  2. With the new workbook active, click on the Import Multiple ASCII button Button Import Multiple ASCII.png on the same toolbar. This opens the ASCII dialog.
  3. In the ASCII dialog, browse to the \Samples\Curve Fitting subfolder of your Origin Program Folder and select the files Sensor01.dat, Sensor02.dat, and Sensor03.dat. Click the Add File(s) button and then check the Show Options Dialog check box and press OK.
  4. In the impASC dialog box, under the Import Options node change Multi-File (except 1st) Import Mode to Start New Sheets. Expand the Rename Sheets and Books node and clear Rename Book with (Partial) Filename.
  5. Press the arrow button Button Save Theme.png at the top right of the dialog box, then select Save As... from the shortcut menu. In the dialog box that opens, enter a name such as Import to sheets and press OK. This will save your settings as a Theme that you can later use with other files.
  6. Press OK to close the impASC dialog.


The three files will be imported, each into a worksheet, and the sheets will be renamed with the file name.

GSBTutorial Files imported to sheets.png

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.

CSV Import

To import locally-stored Comma Delimited (CSV) data:

  1. Choose Data: Import From File: Comma Delimited (CSV)....

or

  1. Choose Data: Connect to File: CSV.


The Data: Import From File option opens the impCSV dialog box allowing you greater control over data import handling. Additionally, impCSV offers the chance to save import settings as a Theme file for later use. Therefore, most will prefer to Import From File as opposed to Connect to File.

You can skip columns or rows, when doing a partial file import of CSV files. For information, choose Data: Import From File: Comma Delimited (CSV)..., check Show Options Dialog and choose a file for import. In the impCSV dialog, expand the Partial Import branch and note the in-dialog syntax hints under Custom.

To import web-stored CSV data:

  1. Choose Data: Connect to Web: CSV.

Excel

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 Import Excel.png 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.
  • 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, importing your Excel data into an Origin workbook will be the best option.


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.
  • To import an Excel file in Origin, the file needs to be of file type .xls, .xlsx or .xlsm.
  • Starting with Origin 2017, you can import Excel files without having MS Excel installed (open as Excel still requires Office 97 or later).
  • 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.
Note: To learn more about working with Excel data, see the Origin Help:

Help: Origin: Origin Help > Working with Microsoft Excel

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.

Binary Import

Files with an optional header section followed by a sequential binary structure, can be imported using the Origin Import Wizard (Data: Import From File: 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.
Import data wizard composite resample office 600px.png

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 Method To Learn More, see

Help: Origin: Origin Help > Importing and Exporting Data


Thermo (SPC, CGM)
pCLAMP (ABF, DAT, AB?)
Princeton Instruments (SPE)
Sound (WAV)
NetCDF (NC)
CDF (CDF)
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)
MATLAB (Mat)
Minitab (MTW, MPJ)
KaleidaGraph (QDA)
SigmaPlot (JNB)
MZXML(mzData, mzXML, mzML, imzML)
EarthProbe (EPA)
EDF (EDF, BDF, REC, HYP)
Somat SIE (SIE)
JCAMP-DX (DX, DX1, JDX, JCM)
HEKA (DAT)
SPSS (SAV)
BRUKER OPUS
SAS (sas7bdat)
Tektronix (ISF)
Data Translation (DCF, HPF)
Binary 2D Array (IMG, B2D)
Image

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....


Database

You can import data from a database using the Data: From Database menu command or using the Database Access toolbar .


There are two ways to build the query:

  • SQL Editor: A simple SQL editor to setup database connect and enter SQL query. It's available in both Origin 32-bit and 64-bit.
  • Query Builder: A graphical query builder that assists you in creating an SQL query. It's only available in Origin 32-bit. The query built by it can be loaded and run in Origin 64-bit.


Note: For more information, see the Origin Help

Help: Origin: Origin Help > Importing and Exporting Data > Importing Data from a Database

Digitizer

At times, you may only have a graphic representation of some data that you would like to explore further. In such cases, Origin's 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.

Digitizer data import chapter 2.png
  • 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:

  1. Click the Digitize Image Button Digitize Image.png 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.

Beginning with Origin 2019b, 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. 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.
Exclude imported when saving.png
  • 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.
Exclude imported when saving Properties dialog.png


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

There are two import commands in the Data menu that are specifically intended for repeat import of the same file (i.e. the file path and name must be the same):

  • Re-Import Directly. When you import a file into an Origin worksheet or matrix sheet, 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.

Notes:

  • 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 matrix sheet, 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