9.2.1 Import Data from a Database
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.
The tutorial only works in 32-bit Origin as Query Builder is only supported in 32-bit Origin
Steps in the tutorial are only illustrative of what users could be doing with your database locally. Please don't follow to test.
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.
Suppose we have already set up a SQL server named AdventureWorks2008 on a server machine noho.
Import data using SQL Editor
- Start a new project. Open SQL Editor by clicking the Open SQL Editor button on the Database Access toolbar.
- 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.
- Select Microsoft OLE DB Provider for SQL Server on the Provider tab. Click Next>> button.
- 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.
- 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.
Persist Security Info=TRUE;
- Select File: Save Connection As... to save the data source file as MyDataSource.ods.
- 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.
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,
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
ORDER BY e.BusinessEntityID
- 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.
- 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
- Start a new project. Click Query Builder button on Database Access toolbar.
- Select Query: Data Source: Open... menu to load MyDataSource.ODS. All tables in database AdventureWorks2008 will show in right panel.
- Drag and drop the following 9 tables from the right panel to the Main panel in the middle:
- 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.
- Select the checkbox in front of the following columns from the corresponding tables to add them to the lower panel.
- 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.
- 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.
- As Criteria ='Washington' on Person.StateProvince.Name row.
- 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.
- 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 on Database Access toolbar anytime to import the data from the database again. Try the following steps.
- Delete some data in the worksheet with database connection.
- Click the Import data button. The data should be back.
- To import the database in a new workbook, choose File: Database Import menu. All saved ODQ files are listed here.
- 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 or Query Builder 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.
- Start with a new project. Choose File: Database Import: MyQuery.ODQ to import data directly into worksheet.
- Click the Open SQL Editor button to open the SQL Editor.
- To add a Labtalk string variable, select Query: LabTalk... to open the LabTalk Support Setting dialog.
- Check the Enable LabTalk (%,$) substitution checkbox.
- 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.
- At the end of SQL Script on right panel, change WHERE statement into
WHERE sp.name = '%(strCond$)'
- Click the button to preview the SQL query string in with labtalk variable substituted the SQL Editor box.
- Click the Import data to worksheet button to import these data. Close the dialog and choose Yes to resaved the SQL Query into worksheet.
- 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.