9.2.1 Import Data from a Database

Summary

There are two tools to import data from a database in Origin.

  • Query Builder (Origin 32-bit only) -- a graphic dialog to build the SQL query.
  • SQL Editor (both Origin 32-bit and 64-bit) -- editor to directly write and edit SQL query. It is more convenient for skilled database users. It also allows users to define LabTalk variables in the SQL script.

In both tools, Database Connection can be saved as ODS file and Database Connection and Query can be saved as ODQ file for future use. Importing from database in Origin 64-bit is faster so users can build ODS and ODQ file in Query Builder if they are not familiar with SQL language, and later run Origin 64-bit to load the ODQ in SQL Editor for the real import.


This tutorial will show you how to build the connection to a SQL server and extract desired data from specified tables using SQL Editor and Query Builder. The SQL database we use here is AdventureWorks database. For detailed information of how to attach an AdventureWorks database, please refer to CodePlex website.


Minimum Origin Version Required: Origin 8.5.1 SR0

What you will learn

This tutorial will show you how to:

  • Import data from database using the SQL Editor.
  • Import data from database using Query Builder.
  • 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
  2. SQL Editor Dialog1.png
  3. 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.
  4. Select Microsoft OLE DB Provider for SQL Server on the Provider tab. Click Next>> button.
    SQL Editor Dialog2.png
  5. 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
  6. Click OK button. All tables in database AdventureWorks2008 are listed in the left panel. The Connection string is in the Message tab.
  7. 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
  8. Select File: Save Connection As... to save the data source file as MyDataSource.ods.
  9. 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
  10. 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
  11. Select File: Save Connection and Query As... from menu to save the connection and query as MyQuery.odq. Close the SQL Editor.

Import data using Query Builder in Origin 32 bit

    If you are running Origin 32-bit, you can use Query Builder to visually build your SQL scripts
  1. Start a new project. Click Query Builder button Db editor builder.png on Database Access toolbar.
  2. Select Query: Data Source: Open... menu to load MyDataSource.ODS. All tables in database AdventureWorks2008 will show in right panel.
    SQL Editor Dialog6.png
  3. Drag and drop the following 9 tables from the right panel to the Main panel in the middle:
    HumanResources.Employee
    Person.Address
    Person.BusinessEntityAddress
    Person.CountryRegion
    Person.EmailAddress
    Person.Person
    Person.PersonPhone
    Person.PhoneNumberType
    Person.StateProvince
  4. Drag main key BusinessEntityID from table HumanResources.Employee and drop it to that key in table Person.BusinessEntityAddress to join these two table. A join line will connect between them.
    SQL Editor Dialog11.png
  5. Select the checkbox in front of the following columns from the corresponding tables to add them to the lower panel.
    Table Column
    HumanResources.Employee
    • BusinessEntityID
    • JobTitle
    Person.Person
    • Title
    • FirstName
    • MiddleName
    • LastName
    • Suffix
    • EmailPromotion
    • AdditionalContactInfo
    Person.Address
    • AddressLine1
    • AddressLine2
    • City
    • PostalCode
    Person.StateProvince
    • Name
    Person.CountryRegion
    • Name
    Person.PersonPhone
    • PhoneNumber
    Person.PhoneNumberType
    • Name
    Person.EmailAddress
    • EmailAddress
  6. In the lower panel, drag and drop the first cell of a row to re-arrange column order as needed. E.g. JobTitle is moved after Suffix.
    SQL Editor Dialog7.png
  7. Select Ascending from Sort Type drop-down list in the first row HumanResources.Employee.BusinessEntityID to sort the whole worksheet according to this column ascendingly.
  8. As Criteria ='Washington' on Person.StateProvince.Name row.
    SQL Editor Dialog12.png
  9. Click the Preview button to preview the data in bottom panel. If the preview is fine, click the Import button to import into data into worksheet. Once imported, the worksheet is connected to the database, and a yellow icon will appear at the top-left of the worksheet.
    SQL Editor Dialog5.png
  10. Select File: Save Connection and Query As... from menu to save the connection and query as a ODQ file MyQuery2.odq. Close the Query Builder.

Reimport from Database

No matter using SQL Editor or Query Builder 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 or Query Builder button Db editor builder.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.