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 an SQLite database file. You do not need to install a database or driver. For details about the MATLAB interface to SQLite, see Interact with Data in SQLite Database Using MATLAB Interface to SQLite.

Create SQLite Connection

Create an 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 execute. Clear the MATLAB workspace variables.

createInventoryTable = strcat("CREATE TABLE inventoryTable ", ...
    "(productNumber NUMERIC, Quantity NUMERIC, ", ...
    "Price NUMERIC, inventoryDate VARCHAR)");
execute(conn,createInventoryTable)

createSuppliers = strcat("CREATE TABLE suppliers ", ...
    "(SupplierNumber NUMERIC, SupplierName VARCHAR(50), ", ...
    "City VARCHAR(20), Country VARCHAR(20), ", ...
    "FaxNumber VARCHAR(20))");
execute(conn,createSuppliers)

createSalesVolume = strcat("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)");
execute(conn,createSalesVolume)

createProductTable = strcat("CREATE TABLE productTable ", ...
    "(productNumber NUMERIC, stockNumber NUMERIC, ", ...
    "supplierNumber NUMERIC, unitCost NUMERIC, ", ...
    "productDescription VARCHAR(20))");
execute(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 sqlwrite. Clear the MATLAB workspace variables.

load('sqliteworkflowdata.mat')

tablename = "inventoryTable";
varnames = ["productNumber" "Quantity" "Price" "inventoryDate"];
data = cell2table(CinvTable); % convert data from cell array to table
data.Properties.VariableNames = varnames; % set variable names
sqlwrite(conn,tablename,data)

tablename = "suppliers";
varnames = ["SupplierNumber" "SupplierName" "City" "Country" "FaxNumber"];
data = cell2table(Csuppliers); % convert data from cell array to table
data.Properties.VariableNames = varnames; % set variable names
sqlwrite(conn,tablename,data)

tablename = "salesVolume";
varnames = ["StockNumber" "January" "February" "March" "April" "May" "June" ...
    "July" "August" "September" "October" "November" "December"];
data = cell2table(CsalesVol); % convert data from cell array to table
data.Properties.VariableNames = varnames; % set variable names
sqlwrite(conn,tablename,data)

tablename = "productTable";
varnames = ["productNumber" "stockNumber" "supplierNumber" "unitCost" ...
    "productDescription"];
data = cell2table(CprodTable); % convert data from cell array to table
data.Properties.VariableNames = varnames; % set variable names
sqlwrite(conn,tablename,data)

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.

head(inventoryTable_data,3)

    productNumber    Quantity    Price        inventoryDate     
    _____________    ________    _____    ______________________

          1            1700      14.5     "9/23/2014 9:38:34 AM"
          2            1200       9.3     "7/8/2014 10:50:45 PM"
          3             356      17.2     "5/14/2014 7:14:28 AM"

head(suppliers_data,3)

    SupplierNumber      SupplierName          City           Country            FaxNumber   
    ______________    _________________    __________    ________________    _______________

         1001         "Wonder Products"    "New York"    "United States"     "212 435 1617" 
         1002         "Terrific Toys"      "London"      "United Kingdom"    "44 456 9345"  
         1003         "Wacky Widgets"      "Adelaide"    "Australia"         "618 8490 2211"

head(salesVolume_data,3)

    StockNumber    January    February    March    April    May    June    July    August    September    October    November    December
    ___________    _______    ________    _____    _____    ___    ____    ____    ______    _________    _______    ________    ________

      125970        1400        1100       981      882     794    752     654      773         809         980        3045       19000  
      212569        2400        1721      1414     1191     983    825     731      653         723         790        1400        5000  
      389123        1800        1200       890      670     550    450     400      410         402         450        1200       16000

head(productTable_data,3)

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

          9            125970            1003            13        "Victorian Doll" 
          8            212569            1001             5        "Train Set"      
          7            389123            1007            16        "Engine Kit"