Import Data Using MATLAB® Interface to SQLite

Open Live Script

This example shows how to move data between MATLAB® and the MATLAB® interface to SQLite. Suppose that you have product data that you want to import into MATLAB®. You can load this data quickly into a SQLite database file. You do not need to install a database or driver. For details about the MATLAB® interface to SQLite, see Working with MATLAB Interface to SQLite. For more functionality, connect to the SQLite database file using the JDBC driver. For details, see Configuring Driver and Data Source.

To access the code for this example, enter edit SQLiteWorkflow.m.

Create SQLite Connection

Create a SQLite connection conn to a new SQLite database file tutorial.db. Specify the file name in the current working folder.

dbfile = fullfile(pwd,'tutorial.db');

conn = sqlite(dbfile,'create');

Create Tables in SQLite Database File

Create the tables inventoryTable, suppliers, salesVolume, and productTable using exec. Clear the MATLAB® workspace variables.

createInventoryTable = ['create table inventoryTable ' ...
    '(productNumber NUMERIC, Quantity NUMERIC, ' ...
    'Price NUMERIC, inventoryDate VARCHAR)'];
exec(conn,createInventoryTable)

createSuppliers = ['create table suppliers ' ...
    '(SupplierNumber NUMERIC, SupplierName varchar(50), ' ...
    'City varchar(20), Country varchar(20), ' ...
    'FaxNumber varchar(20))'];
exec(conn,createSuppliers)

createSalesVolume = ['create table salesVolume ' ...
    '(StockNumber NUMERIC, January NUMERIC, ' ...
    'February NUMERIC, March NUMERIC, April NUMERIC, ' ...
    'May NUMERIC, June NUMERIC, July NUMERIC, ' ...
    'August NUMERIC, September NUMERIC, October NUMERIC, ' ...
    'November NUMERIC, December NUMERIC)'];
exec(conn,createSalesVolume)

createProductTable = ['create table productTable ' ...
    '(productNumber NUMERIC, stockNumber NUMERIC, ' ...
    'supplierNumber NUMERIC, unitCost NUMERIC, ' ...
    'productDescription varchar(20))'];
exec(conn,createProductTable)

clear createInventoryTable createSuppliers createSalesVolume ...
    createProductTable

tutorial.db contains four empty tables.

Load Data into SQLite Database File

Load the MAT-file named sqliteworkflowdata.mat. The variables CinvTable, Csuppliers, CsalesVol, and CprodTable contain data for export. Export data into the tables in tutorial.db using insert. Clear the MATLAB® workspace variables.

load('sqliteworkflowdata.mat')

insert(conn,'inventoryTable', ...
    {'productNumber','Quantity','Price','inventoryDate'},CinvTable)

insert(conn,'suppliers', ...
    {'SupplierNumber','SupplierName','City','Country','FaxNumber'}, ...
    Csuppliers)

insert(conn,'salesVolume', ...
    {'StockNumber','January','February','March','April','May','June', ...
    'July','August','September','October','November','December'}, ...
    CsalesVol)

insert(conn,'productTable', ...
    {'productNumber','stockNumber','supplierNumber','unitCost', ...
    'productDescription'},CprodTable)

clear CinvTable Csuppliers CsalesVol CprodTable

Close the SQLite connection. Clear the MATLAB® workspace variable.

close(conn)

clear conn

Create a read-only SQLite connection to tutorial.db.

conn = sqlite('tutorial.db','readonly');

Import Data into MATLAB®

Import the product data into the MATLAB® workspace using fetch. Variables inventoryTable_data, suppliers_data, salesVolume_data, and productTable_data contain data from the tables inventoryTable, suppliers, salesVolume, and productTable. 

inventoryTable_data = fetch(conn,'SELECT * FROM inventoryTable');

suppliers_data = fetch(conn,'SELECT * FROM suppliers');

salesVolume_data = fetch(conn,'SELECT * FROM salesVolume');

productTable_data = fetch(conn,'SELECT * FROM productTable');

Display the first three rows of data in each table.

inventoryTable_data(1:3,:)
ans=3×4 cell array
    {[1]}    {[1700]}    {[14.5000]}    {'9/23/2014 9:38...'}
    {[2]}    {[1200]}    {[ 9.3000]}    {'7/8/2014 10:50...'}
    {[3]}    {[ 356]}    {[17.2000]}    {'5/14/2014 7:14...'}


suppliers_data(1:3,:)
ans=3×5 cell array
  Columns 1 through 4

    {[1001]}    {'Wonder Products'}    {'New York'}    {'United States' }
    {[1002]}    {'Terrific Toys'  }    {'London'  }    {'United Kingdom'}
    {[1003]}    {'Wacky Widgets'  }    {'Adelaide'}    {'Australia'     }

  Column 5

    {'212 435 1617' }
    {'44 456 9345'  }
    {'618 8490 2211'}


salesVolume_data(1:3,:)
ans=3×13 cell array
  Columns 1 through 6

    {[125970]}    {[1400]}    {[1100]}    {[ 981]}    {[ 882]}    {[794]}
    {[212569]}    {[2400]}    {[1721]}    {[1414]}    {[1191]}    {[983]}
    {[389123]}    {[1800]}    {[1200]}    {[ 890]}    {[ 670]}    {[550]}

  Columns 7 through 12

    {[752]}    {[654]}    {[773]}    {[809]}    {[980]}    {[3045]}
    {[825]}    {[731]}    {[653]}    {[723]}    {[790]}    {[1400]}
    {[450]}    {[400]}    {[410]}    {[402]}    {[450]}    {[1200]}

  Column 13

    {[19000]}
    {[ 5000]}
    {[16000]}


productTable_data(1:3,:)
ans=3×5 cell array
    {[9]}    {[125970]}    {[1003]}    {[13]}    {'Victorian Doll'}
    {[8]}    {[212569]}    {[1001]}    {[ 5]}    {'Train Set'     }
    {[7]}    {[389123]}    {[1007]}    {[16]}    {'Engine Kit'    }

Close SQLite Connection

close(conn)

Clear the MATLAB® workspace variable.

clear conn

See Also

| | | |

Related Topics

External Websites

Database Toolbox Documentation

Support