MATLAB Examples

Import Data Using MATLAB® Interface to SQLite

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 docid:database_ug.bu5u506. For more functionality, connect to the SQLite database file using the JDBC driver. For details, see docid:database_ug.bt41bht.

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

Contents

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,:)

suppliers_data(1:3,:)

salesVolume_data(1:3,:)

productTable_data(1:3,:)
ans =

  3x4 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...'}


ans =

  3x5 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'}


ans =

  3x13 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]}


ans =

  3x5 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