Main Content

setoptions

Customize import options for database data

collapse all in page

Syntax

opts = setoptions(opts,varnames,Option1,OptionValue1,...,OptionN,OptionValueN)
opts = setoptions(opts,index,Option1,OptionValue1,...,OptionN,OptionValueN)

Description

opts = setoptions(opts,varnames,Option1,OptionValue1,...,OptionN,OptionValueN) customizes the import options for importing data from a database into MATLAB®. The function returns the SQLImportOptions object. To import data, you use the SQLImportOptions object, the specified variable names, and the import options with their corresponding values.

example

opts = setoptions(opts,index,Option1,OptionValue1,...,OptionN,OptionValueN) customizes the import options for the variables specified by a numeric index.

example

Examples

collapse all

Open Live Script

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)
Open Live Script

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)
Open Live Script

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)
Open Live Script

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)
Open Live Script

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)
Open Live Script

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)

Input Arguments

collapse all

Database import options, specified as an SQLImportOptions object.

Variable names, specified as a character vector, cell array of character vectors, string scalar, string array, or numeric vector. The varnames input argument indicates the variables in the VariableNames property of the SQLImportOptions object to use for importing data.

Example: 'productname'

Data Types: double | char | string | cell

Index, specified as a numeric vector that identifies the variables in the VariableNames property of the SQLImportOptions object to use for importing data.

Example: [1,2,3]

Data Types: double

Import options, specified as one or more name-value pair arguments. Option is a character vector or string scalar that specifies the name of an import option. OptionValue specifies the value of the import option.

Example: 'FillValue',true,'Type','logical' sets the data type of the specified variable as logical and sets the fill value for missing data in the specified variable as true.

Example: 'Name',"Location" changes the name of the specified variable to Location.

All Variables

You can set import options to change the value of missing data, the name of a variable, or the data type of a variable. These import options apply to all variables specified by either the varnames or index input argument.

Import Option NameDescriptionImport Option Values
'FillValue'Missing data value

Value must be a scalar for a single variable or a cell array for multiple variables.

Valid data types are:

  • All integer classes

  • single

  • double

  • logical

  • char

  • string

  • datetime

  • missing

The data type depends on the variable type in the database.

'MissingRule'Missing data rule

Value must be one of the following:

  • 'fill' (default) — Replace missing data with the missing data value specified by the 'FillValue' import option.

  • 'omitrow' — Omit rows that contain missing data.

You can specify these values as a character vector or string scalar.

Setting the 'MissingRule' import option is the equivalent of using the IS NOT NULL SQL statement in ANSI SQL.

'Name'Variable name

Value must be a character vector or string scalar for a single variable or a cell array of character vectors or string array for multiple variables.

'Type'Data type

Value must be a character vector or string scalar for a single variable or a cell array of character vectors or string array for multiple variables.

The following table describes the valid import option values for the 'Type' import option. The first column shows the data types in the VariableTypes property of the SQLImportOptions object. The second column shows the valid data types to specify in the character vector. To use the valid data type value, enclose it in quotes (for example, 'single').

Variable Data TypeValid Data Type Values for 'Type' Import Option

  • Any integer class

  • single

  • double

  • Any integer class

  • single

  • double

  • logical

  • categorical

The undefined floating-point numbers NaN, –Inf, and +Inf are valid only for the single and double data types. When you change the data type of a floating-point number to an integer, the function that imports the data from the database converts the undefined floating-point number. For example, when you change the data type to 'int8':

  • NaN values change to 0.

  • –Inf values change to intmin('int8').

  • +Inf values change to intmax('int8').

For details, see intmin and intmax.

The same conversion applies to all integer classes.

logical

  • All integer classes

  • single

  • double

  • logical

  • categorical

char or string

  • char

  • string

  • datetime

  • duration

  • categorical

You can change the VariableTypes property to datetime only if the column in the database table has the DATETIME data type. Also, you can change the VariableTypes property to duration only if the column in the database table has the TIME data type.

datetime

  • char

  • string

  • datetime

duration

  • char

  • string

  • duration

categorical

  • All integer classes

  • single

  • double

  • char

  • string

  • logical

  • categorical

Variables with Text Data Type

You can set import options to change the value of variables with a text data type. These import options apply to variables that are either character vectors or string arrays specified by either the varnames or index input argument. You can specify the import option values as a character vector or string scalar.

Import Option NameDescriptionImport Option Values
'WhiteSpaceRule'Leading and trailing white spaces

  • 'preserve' (default) — Preserve white spaces.

  • 'trim' — Remove any leading and trailing white spaces from the text. This import option value ignores white spaces in the middle of the text.

  • 'trimleading' — Remove only the leading white spaces.

  • 'trimtrailing' — Remove only the trailing white spaces.

'TextCaseRule'Text case

  • 'preserve' (default) — Preserve the text case.

  • 'lower' — Convert text to lowercase.

  • 'upper' — Convert text to uppercase.

Variables with datetime Data Type

You can set import options to change the value of variables with the datetime data type. These import options apply to variables with the datetime data type specified by either the varnames or index input argument.

Import Option NameDescriptionImport Option ValuesDefault Import Option Value
'DatetimeFormat'Display format of dates and times

For valid values, see the description of the Format property in the datetime function.

'default'
'DatetimeLocale'Locale to use for interpreting dates

For valid values, see the description of the 'Locale' name-value pair argument in the datetime function.

'en-US'
'TimeZone'Time zone

For valid values, see the description of the TimeZone property in the datetime function.

''
'InputFormat'Format of the input text representing dates and timesFor valid values, see the description of the infmt input argument in the datetime function.'yyyy-MM-dd HH:mm:ss.SSSSSSSSS'

Variables with duration Data Type

You can set import options to change the value of variables with the duration data type. These import options apply to variables with the duration data type specified by either the varnames or index input argument.

Import Option NameDescriptionImport Option ValuesDefault Import Option Value
'InputFormat'Format of the input text representing timeFor valid values, see the description of the infmt input argument in the duration function.''
'DurationFormat'Display format of timeFor valid values, see the description of the Format property in the duration function.'hh:mm:ss'

Variable with categorical Data Type

You can set import options to change the value of variables with the categorical data type. These import options apply to variables with the categorical data type specified by either the varnames or index input argument.

Import Option NameDescriptionImport Option ValuesDefault Import Option Value
'Categories'Expected categories

For valid values, see the description of the catnames input argument in the categorical function.

{}
'Protected'Category protection indicator

For valid values, see the description of the 'Protected' name-value pair argument in the categorical function.

false
'Ordinal'Mathematical ordering indicator

For valid values, see the description of the 'Ordinal' name-value pair argument in the categorical function.

false

Data Types: char | string

Version History

Introduced in R2018b

See Also

| | | | | | | |

Topics

External Websites