setoptions

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

This example uses the patients.xls spreadsheet, which contains the column Weight. Also, the example 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);

Customize the import options for the Weight column in the patients database table. Because this column is numeric, change the data type to int64.

varnames = 'Weight';
opts = setoptions(opts,varnames,'Type','int64');

Import the numeric data in the specified column and display a summary of the imported variable. The summary shows that the variable has the int64 data type.

opts.SelectedVariableNames = {'Weight'};
data = sqlread(conn,tablename,opts);
summary(data)

Variables:

    Weight: 100×1 int64

        Values:

            Min         111   
            Median      143   
            Max         202   

Delete the patients database table using the execute function.

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

Close the database connection.

close(conn)

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

This example uses the patients.xls spreadsheet, which contains the first column LastName. Also, the example 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);

Retrieve the default import options for the LastName and SelfAssessedHealthStatus variables.

varnames = ["LastName" "SelfAssessedHealthStatus"];
varOpts = getoptions(opts,varnames)

varOpts = 
    1x2 SQLVariableImportOptions array with properties:

   Variable Options:
                      (1) |                        (2)  
         Name: 'LastName' | 'SelfAssessedHealthStatus'
         Type:     'char' |                     'char'
  MissingRule:     'fill' |                     'fill'
    FillValue:         '' |                         ''

	To access sub-properties of each variable, use getoptions

Set the import options for the data type of the LastName variable to string. Specify the LastName variable by using a numeric index that finds the variable within the SelectedVariables property of the SQLImportOptions object. Also, set the import options to replace missing data in the LastName variable with the NoName fill value.

index = 1;
opts = setoptions(opts,index,'Type',"string", ...
    'FillValue',"NoName");

Set the import options for the text case of the SelfAssessedHealthStatus variable to uppercase.

varname = "SelfAssessedHealthStatus";
opts = setoptions(opts,varname,'TextCaseRule',"upper");

Import the text data in the selected variables and display the first eight rows. The imported data shows that the LastName variable has the string data type and the SelfAssessedHealthStatus variable text is uppercase.

opts.SelectedVariableNames = ["LastName" "SelfAssessedHealthStatus"];
T = sqlread(conn,tablename,opts);
head(T)

ans=8×2 table
     LastName     SelfAssessedHealthStatus
    __________    ________________________

    "Smith"             'EXCELLENT'       
    "Johnson"           'FAIR'            
    "Williams"          'GOOD'            
    "Jones"             'FAIR'            
    "Brown"             'GOOD'            
    "Davis"             'GOOD'            
    "Miller"            'GOOD'            
    "Wilson"            'GOOD'            

Delete the patients database table using the execute function.

sqlstr = "DROP TABLE ";
sqlquery = strcat(sqlstr,tablename);
execute(conn,sqlquery)

Close the database connection.

close(conn)

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

This example uses the outages.csv file, which contains the columns OutageTime and RestorationTime. Also, the example 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 outage information into the MATLAB® workspace.

outages = readtable('outages.csv');

Create the outages database table using the outage information.

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

Retrieve the data using the sqlread function and display the first eight rows. The second row of the RestorationTime variable contains missing data.

data = sqlread(conn,tablename);
head(data)

ans=8×6 table
      Region              OutageTime             Loss     Customers          RestorationTime               Cause      
    ___________    _________________________    ______    __________    _________________________    _________________

    'SouthWest'    '2002-02-01 12:18:00.000'    458.98    1.8202e+06    '2002-02-07 16:50:00.000'    'winter storm'   
    'SouthEast'    '2003-01-23 00:49:00.000'    530.14    2.1204e+05    ''                           'winter storm'   
    'SouthEast'    '2003-02-07 21:15:00.000'     289.4    1.4294e+05    '2003-02-17 08:14:00.000'    'winter storm'   
    'West'         '2004-04-06 05:44:00.000'    434.81    3.4037e+05    '2004-04-06 06:10:00.000'    'equipment fault'
    'MidWest'      '2002-03-16 06:18:00.000'    186.44    2.1275e+05    '2002-03-18 23:23:00.000'    'severe storm'   
    'West'         '2003-06-18 02:49:00.000'         0             0    '2003-06-18 10:54:00.000'    'attack'         
    'West'         '2004-06-20 14:39:00.000'    231.29           NaN    '2004-06-20 19:16:00.000'    'equipment fault'
    'West'         '2002-06-06 19:28:00.000'    311.86           NaN    '2002-06-07 00:51:00.000'    'equipment fault'

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

opts = databaseImportOptions(conn,tablename);

Retrieve the default import options for the OutageTime and RestorationTime variables.

varnames = {'OutageTime','RestorationTime'};
varOpts = getoptions(opts,varnames)

varOpts = 
    1x2 SQLVariableImportOptions array with properties:

   Variable Options:
                      (1) |               (2)  
       Name: 'OutageTime' | 'RestorationTime'
       Type:       'char' |            'char'
  FillValue:           '' |                ''

	To access sub-properties of each variable, use getoptions

Set the import options for the data type of the specified variables to datetime. Also, set the import options to replace missing data in the specified variables with the current date and time.

opts = setoptions(opts,varnames,'Type','datetime', ...
    'FillValue',datetime('now'));

Import the date and time data in the selected variables and display the first eight rows. The imported data shows that the variables have the datetime data type. The missing value in the second row of the RestorationTime variable is filled with the current date and time.

opts.SelectedVariableNames = varnames;
T = sqlread(conn,tablename,opts);
head(T)

ans=8×2 table
         OutageTime           RestorationTime   
    ____________________    ____________________

    01-Feb-2002 12:18:00    07-Feb-2002 16:50:00
    23-Jan-2003 00:49:00    19-Jun-2018 15:30:14
    07-Feb-2003 21:15:00    17-Feb-2003 08:14:00
    06-Apr-2004 05:44:00    06-Apr-2004 06:10:00
    16-Mar-2002 06:18:00    18-Mar-2002 23:23:00
    18-Jun-2003 02:49:00    18-Jun-2003 10:54:00
    20-Jun-2004 14:39:00    20-Jun-2004 19:16:00
    06-Jun-2002 19:28:00    07-Jun-2002 00:51:00

Delete the outages database table using the execute function.

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

Close the database connection.

close(conn)

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

This example uses the outages.csv file, which contains the columns Region and Cause. Also, the example 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 outage information into the MATLAB® workspace.

outages = readtable('outages.csv');

Create the outages database table using the outage information.

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

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

opts = databaseImportOptions(conn,tablename);

Retrieve the default import options for the Region and Cause variables.

varnames = {'Region','Cause'};
varOpts = getoptions(opts,varnames)

varOpts = 
    1x2 SQLVariableImportOptions array with properties:

   Variable Options:
                  (1) |     (2)  
       Name: 'Region' | 'Cause'
       Type:   'char' |  'char'
  FillValue:       '' |      ''

	To access sub-properties of each variable, use getoptions

Set the import options for the data type of the specified variables to categorical. Also, set the import options to replace missing data in the specified variables with the fill value unknown.

opts = setoptions(opts,varnames,'Type','categorical', ...
    'FillValue','unknown');

Import the categorical array data in the selected variables and display a summary of the data. The imported data shows that the variables have the categorical data type. The missing values of both variables are filled with the value unknown.

opts.SelectedVariableNames = varnames;
T = sqlread(conn,tablename,opts);
summary(T)

Variables:

    Region: 1468×1 categorical

        Values:

            MidWest        142   
            NorthEast      557   
            SouthEast      389   
            SouthWest       26   
            West           354   
            unknown          0   

    Cause: 1468×1 categorical

        Values:

            attack                294  
            earthquake              2  
            energy emergency      188  
            equipment fault       156  
            fire                   25  
            severe storm          338  
            thunder storm         201  
            unknown                24  
            wind                   95  
            winter storm          145  

Delete the outages database table using the execute function.

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

Close the database connection.

close(conn)

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

This example uses the airlinesmall_subset.xls spreadsheet, which contains the column Cancelled. Also, the example 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 flight information in the MATLAB® workspace.

flights = readtable('airlinesmall_subset.xlsx');

Create the flights database table using the flight information.

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

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

opts = databaseImportOptions(conn,'flights');

Retrieve the default import options for the Cancelled variable.

varnames = 'Cancelled';
varOpts = getoptions(opts,varnames)

varOpts = 
  SQLVariableImportOptions with properties:

  Variable Properties :
               Name: 'Cancelled'
               Type: 'double'
          FillValue: NaN

Set the import options for the data type of the specified variable to logical. Also, set the import options to replace missing data in the specified variable with the fill value true.

opts = setoptions(opts,varnames,'Type','logical', ...
    'FillValue',true);

Import the logical data in the selected variable and display a summary of the data. The imported data shows that the variable has the logical data type.

opts.SelectedVariableNames = varnames;
T = sqlread(conn,tablename,opts);
summary(T)

Variables:

    Cancelled: 1338×1 logical

        Values:

            True          29    
            False       1309    

Delete the flights database table using the execute function.

sqlquery = 'DROP TABLE flights';
execute(conn,sqlquery)

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 the import options to omit missing data. Import data using the sqlread function.

This example uses the outages.csv file, which contains outage data. Also, the example 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 outage information into the MATLAB® workspace.

outages = readtable("outages.csv");

Create the outages database table using the outage information.

tablename = "outages";
sqlwrite(conn,tablename,outages)

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

opts = databaseImportOptions(conn,tablename);

Determine the size of outages.

size(outages)

ans = 1×2

        1468           6

Set the import options to omit rows that have missing data in the Customers variable.

varnames = "Customers";
opts = setoptions(opts,varnames,'MissingRule',"omitrow");

Import the data and display the first eight rows. The imported data contains no missing data in the Customers variable.

T = sqlread(conn,tablename,opts);
head(T)

ans=8×6 table
      Region              OutageTime             Loss     Customers          RestorationTime               Cause      
    ___________    _________________________    ______    __________    _________________________    _________________

    'SouthWest'    '2002-02-01 12:18:00.000'    458.98    1.8202e+06    '2002-02-07 16:50:00.000'    'winter storm'   
    'SouthEast'    '2003-01-23 00:49:00.000'    530.14    2.1204e+05    ''                           'winter storm'   
    'SouthEast'    '2003-02-07 21:15:00.000'     289.4    1.4294e+05    '2003-02-17 08:14:00.000'    'winter storm'   
    'West'         '2004-04-06 05:44:00.000'    434.81    3.4037e+05    '2004-04-06 06:10:00.000'    'equipment fault'
    'MidWest'      '2002-03-16 06:18:00.000'    186.44    2.1275e+05    '2002-03-18 23:23:00.000'    'severe storm'   
    'West'         '2003-06-18 02:49:00.000'         0             0    '2003-06-18 10:54:00.000'    'attack'         
    'NorthEast'    '2003-07-16 16:23:00.000'    239.93         49434    '2003-07-17 01:12:00.000'    'fire'           
    'MidWest'      '2004-09-27 11:09:00.000'    286.72         66104    '2004-09-27 16:37:00.000'    'equipment fault'

Determine the size of T. The number of rows in the imported data is smaller because the software removes all rows with missing data in the Customers variable.

size(T)

ans = 1×2

        1140           6

Delete the outages database table using the execute function.

sqlstr = "DROP TABLE ";
sqlquery = strcat(sqlstr,tablename);
execute(conn,sqlquery)

Close the database connection.

close(conn)