In Origin 2021b, we introduced SQLite database connectivity. In this blog, I am going to show three examples:
- How to connect to a SQLite database file (.sqlite3, .sqlite, .db) using Database Connector.
- How to export data in Origin to a SQLite database file and update database with other worksheets.
- 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 、 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 、 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.