10.3.1 Import Data from a Database

Summary

This tutorial is for illustrative purposes only. The connection string shown in the tutorial does not connect to an installed database. If you wish to set up an AdventureWorks database on your own server so that you can work through the process discussed in the tutorial, see this GitHub page.

In Origin, you can use the SQL Editor to import data from a database.

SQL Editor is an editor to directly write and edit SQL query. It is very convenient for skilled database users and allows users to define LabTalk variables in the SQL script.

In this tool, Database Connection can be saved as ODS file and Database Connection and Query can be saved as ODQ file for future use.

This tutorial will show you how to build the connection to a SQL server and extract desired data from specified tables using SQL Editor.

What you will learn

This tutorial will show you how to:

  • Import data from database using the SQL Editor.
  • Reimport data.
  • LabTalk support in the SQL Editor.

Steps

Suppose we have already set up a SQL server named AdventureWorks2008 on a server machine noho.

Import data using SQL Editor

  1. Start a new project. Open SQL Editor by clicking the Open SQL Editor button on the Database Access toolbar.
    ImportDataDatabase 1.png
    SQL Editor Dialog1.png
  2. Now we are going to create the connection to the AdventureWorks2008R2 database. Click Db editor connect.png button or select File: New...menu to bring up the Data Link Properties dialog.
  3. Select Microsoft OLE DB Provider for SQL Server on the Provider tab. Click Next>> button.
    SQL Editor Dialog2.png
  4. On the Connection tab that is active, specify the server information, including server name, log-in user name and password (if needed, in this example labtalk2015), and database name. Click Test Connection to make sure the connection is successful.
    SQL Editor Dialog3.png
  5. Click OK button. All tables in database AdventureWorks2008 are listed in the left panel. The Connection string is in the Message tab.
    SQL Editor Dialog4.png
    Alternatively if you already have connection string, you can select File: Edit Connection String... menu to open the Connection String Editor dialog. Enter the following SQL string and then click OK button to create the connection.
    Provider=SQLOLEDB.1;
    Password=labtalk2015;
    Persist Security Info=TRUE;
    USER ID=CONNECT;
    Initial Catalog=AdventureWorks2008;
    DATA SOURCE=noho
  6. Select File: Save Connection As... to save the data source file as MyDataSource.ods.
  7. Now we are going to extract data from 9 tables to construct the employee list. You can write SQL script from scratch. Double click node on left panel will help you add the table and field name in the editor. For now, just copy following SQL scripts in the right panel.
    SELECT 
       e.BusinessEntityID, p.Title, p.FirstName, p.MiddleName, p.LastName, 
       p.Suffix, e.JobTitle, pp.PhoneNumber, pnt.Name AS PhoneNumberType, ea.EmailAddress, 
       p.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName,
       a.PostalCode, cr.Name AS CountryRegionName,  
       p.AdditionalContactInfo
    FROM 
       HumanResources.Employee AS e INNER JOIN 
       Person.Person AS p ON p.BusinessEntityID = e.BusinessEntityID INNER JOIN 
       Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = e.BusinessEntityID INNER JOIN 
       Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN 
       Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID INNER JOIN 
       Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode LEFT OUTER JOIN 
       Person.PersonPhone AS pp ON pp.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN 
       Person.PhoneNumberType AS pnt ON pp.PhoneNumberTypeID = pnt.PhoneNumberTypeID LEFT OUTER JOIN 
       Person.EmailAddress AS ea ON p.BusinessEntityID = ea.BusinessEntityID
    WHERE sp.Name='Washington'
    ORDER BY e.BusinessEntityID
  8. Click the Preview the result data button SQL editor preview.png to preview the data. If the preview is fine, click the Import data to worksheet button Button db Import Data.png to import these data. Once imported, the worksheet is connected to the database, and a yellow icon will appear at the top-left of the worksheet.
    SQL Editor Dialog19.png
  9. Select File: Save Connection and Query As... from menu to save the connection and query as MyQuery.odq. Close the SQL Editor.

Reimport from Database

After using SQL Editor to import data from database, the connection and the query are automatically saved in the worksheet. You can click the Import data Button db Import Data.png button on Database Access toolbar anytime to import the data from the database again. Try the following steps.

  1. Delete some data in the worksheet with database connection.
  2. Click the Import data Button db Import Data.png button. The data should be back.
  3. To import the database in a new workbook, choose File: Database Import menu. All saved ODQ files are listed here.
  4. Choose MyQuery.ODQ. A new workbook will be created with data from database.
  1. If your database is big, the saved workbook file will be big. You can choose Worksheet: Clear Worksheet to clear the data and then save the workbook. Later you can load the workbook and click the Preview the result data button SQL editor preview.png to preview 50 rows of the data or click the Import data Button db Import Data.png button to import from database.
  2. If you need to modify the query, with the worksheet active, click the SQL Editor button Button Open SQL Editer.png.

LabTalk Support in SQL Editor

In above examples, we only imported data for Washington state. But it's harded coded so if you want to do a query of another State, you need to change the query. This section will show you how to define a Labtalk string variable for state name so it's easier to change the query in the future.

  1. Start with a new project. Choose File: Database Import: MyQuery.ODQ to import data directly into worksheet.
  2. Click the Open SQL Editor button Button Open SQL Editer.png to open the SQL Editor.
  3. To add a Labtalk string variable, select Query: LabTalk... to open the LabTalk Support Setting dialog.
    SQL Editor Dialog8.png
  4. Check the Enable LabTalk (%,$) substitution checkbox.
  5. Type the script below to define a LabTalk string variable strCond to represent the Washington State.
    string strCond$ = "Washington";
    in the Before Query Script box. Click OK.
    SQL Editor Dialog9.png
  6. At the end of SQL Script on right panel, change WHERE statement into
     WHERE sp.name = '%(strCond$)'
  7. Click the Db editor labtalk.png button to preview the SQL query string in with labtalk variable substituted the SQL Editor box.
    SQL Editor Dialog10.png
  8. Click the Import data to worksheet button Button db Import Data.png to import these data. Close the dialog and choose Yes to resaved the SQL Query into worksheet.
  9. From now on, you only need to change the strCond$ value in LabTalk Support Settings dialog to change the State name. No need to change the SQL Query.