This is machine translation

Translated by Microsoft
Mouseover text to see original. Click the button below to return to the English version of the page.

Note: This page has been translated by MathWorks. Click here to see
To view all translated materials including this page, select Country from the country navigator on the bottom of this page.

sqlread

Import data into MATLAB from database table

Syntax

data = sqlread(conn,tablename)
data = sqlread(conn,tablename,opts)
data = sqlread(___,Name,Value)

Description

example

data = sqlread(conn,tablename) returns a table by importing data into MATLAB® from a database table. Executing this function is the equivalent of writing a SELECT * FROM tablename SQL statement in ANSI SQL.

example

data = sqlread(conn,tablename,opts) customizes options for importing data from a database table using the SQLImportOptions object.

example

data = sqlread(___,Name,Value) specifies additional options using one or more name-value pair arguments and any of the previous input argument combinations. For example, 'Catalog','cat' imports data from a database table stored in the 'cat' catalog.

Examples

collapse all

Use an ODBC connection to import product data from a database table into MATLAB® using a Microsoft® SQL Server® database. Then, perform a simple data analysis.

Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

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

conn.Message
ans =

     []

Import data from the database table productTable. The sqlread function returns a MATLAB® table that contains the product data.

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

Display the first few products.

head(data,3)
ans =

  3×5 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

          9          1.2597e+05          1003            13        'Victorian Doll' 
          8          2.1257e+05          1001             5        'Train Set'      
          7          3.8912e+05          1007            16        'Engine Kit'     

Close the database connection.

close(conn)

Customize import options when importing data from a database table. Control the import options by creating an SQLImportOptions object. Then, customize import options for different database columns. Import data using the sqlread function.

This example uses the patients.xls file, which contains the columns Gender, Location, SelfAssessedHealthStatus, and Smoker. The example also uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.

Create a database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Load patient information into the MATLAB® workspace.

patients = readtable('patients.xls');

Create the patients database table using the patient information.

tablename = 'patients';
sqlwrite(conn,tablename,patients)

Create an SQLImportOptions object using the patients database table and the databaseImportOptions function.

opts = databaseImportOptions(conn,tablename)

Display the current import options for the variables selected in the SelectedVariableNames property of the SQLImportOptions object.

vars = opts.SelectedVariableNames;
varOpts = getoptions(opts,vars)
varOpts = 
    1x10 SQLVariableImportOptions array with properties:

   Variable Options:
                    (1) |      (2) |      (3) |        (4) |      (5) |      (6) |      (7) |        (8) |         (9) |                       (10)  
       Name: 'LastName' | 'Gender' |    'Age' | 'Location' | 'Height' | 'Weight' | 'Smoker' | 'Systolic' | 'Diastolic' | 'SelfAssessedHealthStatus'
       Type:     'char' |   'char' | 'double' |     'char' | 'double' | 'double' | 'double' |   'double' |    'double' |                     'char'
  FillValue:         '' |       '' |    [NaN] |         '' |    [NaN] |    [NaN] |    [NaN] |      [NaN] |       [NaN] |                         ''

	To access sub-properties of each variable, use getoptions

Change the data types for the Gender, Location, SelfAssessedHealthStatus, and Smoker variables using the setoptions function. Because the Gender, Location, and SelfAssessedHealthStatus variables indicate a finite set of repeating values, change their data type to categorical. Because the Smoker variable stores the values 0 and 1, change its data type to logical. Then, display the updated import options.

opts = setoptions(opts,{'Gender','Location','SelfAssessedHealthStatus'}, ...
    'Type','categorical');
opts = setoptions(opts,'Smoker','Type','logical');

varOpts = getoptions(opts,{'Gender','Location','Smoker', ...
    'SelfAssessedHealthStatus'})
varOpts = 
    1x4 SQLVariableImportOptions array with properties:

   Variable Options:
                       (1) |           (2) |       (3) |                        (4)  
       Name:      'Gender' |    'Location' |  'Smoker' | 'SelfAssessedHealthStatus'
       Type: 'categorical' | 'categorical' | 'logical' |              'categorical'
  FillValue:   <undefined> |   <undefined> |         0 |                <undefined>

	To access sub-properties of each variable, use getoptions

Import the patients database table using the sqlread function, and display the last eight rows of the table.

data = sqlread(conn,tablename,opts);
tail(data)
ans=8×10 table
     LastName      Gender    Age            Location             Height    Weight    Smoker    Systolic    Diastolic    SelfAssessedHealthStatus
    ___________    ______    ___    _________________________    ______    ______    ______    ________    _________    ________________________

    'Foster'       Female    30     St. Mary's Medical Center      70       124      false       130          91               Fair             
    'Gonzales'     Male      48     County General Hospital        71       174      false       123          79               Good             
    'Bryant'       Female    48     County General Hospital        66       134      false       129          73               Excellent        
    'Alexander'    Male      25     County General Hospital        69       171      true        128          99               Good             
    'Russell'      Male      44     VA Hospital                    69       188      true        124          92               Good             
    'Griffin'      Male      49     County General Hospital        70       186      false       119          74               Fair             
    'Diaz'         Male      45     County General Hospital        68       172      true        136          93               Good             
    'Hayes'        Male      48     County General Hospital        66       177      false       114          86               Fair             

Display a summary of the imported data. The sqlread function applies the import options to the variables in the imported data.

summary(data)
Variables:

    LastName: 100×1 cell array of character vectors

    Gender: 100×1 categorical

        Values:

            Female       53   
            Male         47   

    Age: 100×1 double

        Values:

            Min        25  
            Median     39  
            Max        50  

    Location: 100×1 categorical

        Values:

            County General Hospital          39    
            St. Mary s Medical Center        24    
            VA Hospital                      37    

    Height: 100×1 double

        Values:

            Min          60   
            Median       67   
            Max          72   

    Weight: 100×1 double

        Values:

            Min          111  
            Median     142.5  
            Max          202  

    Smoker: 100×1 logical

        Values:

            True        34   
            False       66   

    Systolic: 100×1 double

        Values:

            Min          109    
            Median       122    
            Max          138    

    Diastolic: 100×1 double

        Values:

            Min            68    
            Median       81.5    
            Max            99    

    SelfAssessedHealthStatus: 100×1 categorical

        Values:

            Excellent                34            
            Fair                     15            
            Good                     40            
            Poor                     11            

Delete the patients database table using the execute function.

sqlquery = ['DROP TABLE ' tablename];
execute(conn,sqlquery)

Close the database connection.

close(conn)

Use an ODBC connection to import product data from a database table into MATLAB® using a Microsoft® SQL Server® database. Specify the schema where the database table is stored. Then, sort and filter the rows in the imported data and perform a simple data analysis.

Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

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

conn.Message
ans =

     []

Import data from the table productTable. Specify the database schema dbo. The data table contains the product data.

tablename = 'productTable';
data = sqlread(conn,tablename,'Schema','dbo');

Display the first few products.

data(1:3,:)
ans =

  3×5 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

          9          1.2597e+05          1003            13        'Victorian Doll' 
          8          2.1257e+05          1001             5        'Train Set'      
          7          3.8912e+05          1007            16        'Engine Kit'     

Display the first few product descriptions.

data.productDescription(1:3)
ans =

  3×1 cell array

    {'Victorian Doll'}
    {'Train Set'     }
    {'Engine Kit'    }

Sort the rows in data by the product description column in alphabetical order.

column = 'productDescription';
data = sortrows(data,column);

Display the first few product descriptions after sorting.

data.productDescription(1:3)
ans =

  3×1 cell array

    {'Building Blocks'}
    {'Convertible'    }
    {'Engine Kit'     }

Close the database connection.

close(conn)

Use an ODBC connection to import product data from a database table into MATLAB® using a Microsoft® SQL Server® database. Specify the maximum number of rows to import from the database table.

Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

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

conn.Message
ans =

     []

Import data from the table productTable. Import only three rows of data from the database table. The data table contains the product data.

tablename = 'productTable';
data = sqlread(conn,tablename,'MaxRows',3)
data =

  3×5 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

          9          1.2597e+05          1003            13        'Victorian Doll' 
          8          2.1257e+05          1001             5        'Train Set'      
          7          3.8912e+05          1007            16        'Engine Kit'     

Close the database connection.

close(conn)

Input Arguments

collapse all

Database connection, specified as a connection object created with the database function.

Database table name, specified as a character vector or string scalar denoting the name of a table in the database.

Example: 'employees'

Data Types: char | string

Database import options, specified as an SQLImportOptions object.

Name-Value Pair Arguments

Specify optional comma-separated pairs of Name,Value arguments. Name is the argument name and Value is the corresponding value. Name must appear inside quotes. You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example: data = sqlread(conn,'inventoryTable','Catalog','toy_store','Schema','dbo','MaxRows',5) imports five rows of data from the database table inventoryTable stored in the toy_store catalog and the dbo schema.

Database catalog name, specified as the comma-separated pair consisting of 'Catalog' and a character vector or string scalar. A catalog serves as the container for the schemas in a database and contains related metadata information. A database can have numerous catalogs.

Example: 'Catalog','toy_store'

Data Types: char | string

Database schema name, specified as the comma-separated pair consisting of 'Schema' and a character vector or string scalar. A schema defines the database tables, views, relationships among tables, and other elements. A database catalog can have numerous schemas.

Example: 'Schema','dbo'

Data Types: char | string

Maximum number of rows to return, specified as the comma-separated pair consisting of 'MaxRows' and a positive numeric scalar. By default, the sqlread function returns all rows from the executed SQL query. Use this name-value pair argument to limit the number of rows imported into MATLAB.

Example: 'MaxRows',10

Data Types: double

Output Arguments

collapse all

Imported data, returned as a table. The rows of the table correspond to the rows in the database table tablename. The variables in the table correspond to each column in the database table. For columns that have numeric data types in the database table, the variable data types in data are double by default. For columns that have text, date, time, or timestamp data types in the database table, the variable data types are cell arrays of character vectors by default.

If the database table contains no data to import, then data is an empty table.

Introduced in R2018a