MATLAB Examples

Import Data from Database Table Using sqlread Function

This example shows how to import data from a table in a Microsoft® Access™ database into the MATLAB® workspace using the sqlread function. The example then shows how to use an SQL script to import data from an SQL query that contains multiple joins.

Contents

Connect to Database

Create a Microsoft Access database connection with the data source name dbdemo using an ODBC driver and a blank user name and password. This database contains the table producttable.

conn = database('dbdemo','','');

If you are connecting to a database using a JDBC connection, then specify a different syntax for the database function.

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message
ans =

     []

Import Data from Database Table

Import product data from the database table producttable by using the sqlread function and the database connection. This function imports data as a MATLAB table.

tablename = 'producttable';
data = sqlread(conn,tablename);

Display the product number and description in the imported data.

data(:,[1 5])
ans =

  10×2 table

    productnumber    productdescription
    _____________    __________________

          9          'Victorian Doll'  
          8          'Train Set'       
          7          'Engine Kit'      
          2          'Painting Set'    
          4          'Space Cruiser'   
          1          'Building Blocks' 
          5          'Tin Soldier'     
          6          'Sail Boat'       
          3          'Slinky'          
         10          'Teddy Bear'      

Import Data Using Multiple Joins in SQL Query

Create an SQL script file named salesvolume.sql with the following SQL query. This SQL query uses multiple joins to join these tables in the dbdemo database:

  • producttable
  • salesvolume
  • suppliers

The purpose of the query is to import sales volume data for suppliers located in the United States.

SELECT  salesvolume.january
,   salesvolume.february
,   salesvolume.march
,   salesvolume.april
,   salesvolume.may
,   salesvolume.june
,   salesvolume.july
,   salesvolume.august
,   salesvolume.september
,   salesvolume.october
,   salesvolume.november
,   salesvolume.december
,   suppliers.country
FROM     ((producttable
INNER JOIN salesvolume
ON  producttable.stocknumber = salesvolume.stocknumber)
INNER JOIN suppliers
ON  producttable.suppliernumber = suppliers.suppliernumber)
WHERE suppliers.country LIKE 'United States%'

Run the SQL script file named salesvolume.sql by using the runsqlscript function. results is a cursor object array with the data returned from running the SQL query in the SQL script file.

results = runsqlscript(conn,'salesvolume.sql');

Display the first three rows in the Data table. Access this table as a property of the cursor object by using dot notation.

head(results(1).Data,3)
ans =

  3×13 table

    january    february    march    april    may     june    july    august    september    october    november    december        country    
    _______    ________    _____    _____    ____    ____    ____    ______    _________    _______    ________    ________    _______________

     5000        3500      2800     2300     1700    1400    1000     900        1600        3300       12000       20000      'United States'
     2400        1721      1414     1191      983     825     731     653         723         790        1400        5000      'United States'
     1200         900       800      500      399     345     300     175         760        1500        5500       17000      'United States'

Close Database Connection

close(conn)