SQLite Support in Origin

In Origin 2021b, we introduced SQLite database connectivity. In this blog, I am going to show three examples: 

  1. How to connect to a SQLite database file (.sqlite3, .sqlite, .db) using Database Connector.
  2. How to export data in Origin to a SQLite database file and update database with other worksheets.
  3. How to update SQLite database with Origin on changes in worksheet.

Please also refer to other blogs  to learn general database support in Origin.

Import data from SQLite database file 

1. Before connecting to a SQLite file, you need to first install ODBC driver for SQLite database. Go to http://www.ch-werner.de/sqliteodbc/ and download sqliteodbc_w64.exe. Execute it to install the driver. Make sure NOT to check the box SQLite 2 Drivers during installation.

2. Launch Origin, create a new workbook. Select from menu Data:Connec to Database:New….. In the popped-up dialog, check ODBC driver and click OK.

4. In Connect by ODBC dialog,  Select SQLite3 ODBC Driver. Select the SQLite file \Import and Export\chinook.db under Program Sample folder (menu Help:Open Folder:Sample Folder) . Click OK.

5. The connection to the database is now created. In SQL Editor dialog, double click on Album  to generate the SQL query statement which imports the entire table. 

6.  Click OK to import the table into a worksheet.  Sometimes people want the connection information to be saved and can be loaded easily to create the connection. To do so, first go back to the SQL Editor dialog by selecting SQL Editor… from database icon. 

7. Select menu File: Save Connection as  and save to E:\chinook.ods

Note:  *.ods file is an Origin-specific plain text file containing a Connection String. Used to facilitate loading a connection into SQL Editor quickly and easily.

8.  Create a new workbook, then select from menu Data: Connect to Database and select the newly created .ods file to connect to the database.

 

Create SQLite database from Origin worksheets

You can create SQLite database file from Origin. The next example exports the first worksheet to create a SQLite file and then add a second worksheet as a new table.

1. Let’s first prepare two worksheets for the database:  Click  in the toolbar to create a new project, click to add a second workbook.  In each workbook, highlight the two columns and click to fill them with  random numbers. Double click on the Sheet name tab to change sheet name to sample1 and sample2

2.  Activate worksheet Sample1, select menu File:Export:SQLite…. Specify a file name and a file path. Click the triangle button besides Dialog Theme at the top, choose Save as <default>. By doing so the settings in the dialog will be saved as default and can be reused next time the dialog is launched.  Click OK to generate the SQLite database file E:\sample.sqlite

Note: You can check All Sheets to export all worksheets within the workbook to one file.

3. Activate worksheet Sample2. Select menu File:Export:SQLite….. The file name and path is preset to E:\sample.sqlite as the default settings are loaded. Click OK. The worksheet is added as a second table. Create a new workbook and connect to E:\sample.sqlite with Database Connector. You should see two tables with name Sample1 and Sample2 . Double click Sample1 and click    button to preview the table. 

Update SQLite database with Origin

This example shows how to update a table  in SQLite file with Origin: The idea is to first connect worksheet to a database, import a table, make changes then update the changes in the database. 

1. First prepare a SQLite file with one table: Click  to create a workbook. Highlight the two columns and click to fill them with  random numbers. Change the sheet name to Sample3. Select menu File:Export:SQLite to export this worksheet to E:\NewSample.sqlite.

2. Create a new workbook. Connect to  E:\NewSample.sqlite and import table Sample3. In the worksheet click the database icon and select Unlock Imported Data. Highlight Col(A) and click  and now Col(A) is replaced by row indices. Click the database icon again and click Update Database to update the changes to Sample3 table. 
 
 
3. Create a new workbook and connect to E:\NewSample.sqlite. In the dialog double click Sample3 and click to see the updates.
 
 

Leave a Reply

Your email address will not be published. Required fields are marked *