9.3.1 Import Data from a Database

ImpData-from-DB

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.
   
   
2. Now we are going to create the connection to the AdventureWorks2008R2 database. Click 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.
   
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.
   
5. Click OK button. All tables in database AdventureWorks2008 are listed in the left panel. The Connection string is in the Message tab.
   
   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 to preview the data. If the preview is fine, click the Import data to worksheet button 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.
   
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 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. 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.

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 to preview 50 rows of the data or click the Import data button to import from database. If you need to modify the query, with the worksheet active, click the SQL Editor button .

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 to open the SQL Editor.
3. To add a Labtalk string variable, select Query: LabTalk... to open the LabTalk Support Setting dialog.
   
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.
   
6. At the end of SQL Script on right panel, change WHERE statement into

    WHERE sp.name = '%(strCond$)'

7. Click the button to preview the SQL query string in with labtalk variable substituted the SQL Editor box.
   
8. Click the Import data to worksheet button 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.