setoptions

Customize import options for database data

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

Customize Options When Importing Numeric Data

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)

Customize Options When Importing Text Data

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)

Customize Options When Importing Date and Time Data

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)

Customize Options When Importing Categorical Array Data

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)

Customize Options When Importing Logical Data

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)

Customize Options to Omit Missing Data

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

`opts` — Database import options
`SQLImportOptions` object

Database import options, specified as an SQLImportOptions object.

`varnames` — Variable names
character vector | cell array of character vectors | string scalar | string array | numeric vector

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` — Index
numeric vector

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

`Option1,OptionValue1,...,OptionN,OptionValueN` — Import options
name-value pair arguments

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 Name	Description	Import 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 Type	Valid 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 Name	Description	Import 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 Name	Description	Import Option Values	Default 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 times	For 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 Name	Description	Import Option Values	Default Import Option Value
`'InputFormat'`	Format of the input text representing time	For valid values, see the description of the `infmt` input argument in the `duration` function.	`''`
`'DurationFormat'`	Display format of time	For 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 Name	Description	Import Option Values	Default 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

setoptions

Syntax

Description

Examples

Customize Options When Importing Numeric Data

Customize Options When Importing Text Data

Customize Options When Importing Date and Time Data

Customize Options When Importing Categorical Array Data

Customize Options When Importing Logical Data

Customize Options to Omit Missing Data

Input Arguments

`opts` — Database import options
`SQLImportOptions` object

`varnames` — Variable names
character vector | cell array of character vectors | string scalar | string array | numeric vector

`index` — Index
numeric vector

`Option1,OptionValue1,...,OptionN,OptionValueN` — Import options
name-value pair arguments

All Variables

Variables with Text Data Type

Variables with `datetime` Data Type

Variables with `duration` Data Type

Variable with `categorical` Data Type

Version History

See Also

Topics

External Websites

setoptions

Syntax

Description

Examples

Customize Options When Importing Numeric Data

Customize Options When Importing Text Data

Customize Options When Importing Date and Time Data

Customize Options When Importing Categorical Array Data

Customize Options When Importing Logical Data

Customize Options to Omit Missing Data

Input Arguments

opts — Database import options SQLImportOptions object

varnames — Variable names character vector | cell array of character vectors | string scalar | string array | numeric vector

index — Index numeric vector

Option1,OptionValue1,...,OptionN,OptionValueN — Import options name-value pair arguments

All Variables

Variables with Text Data Type

Variables with datetime Data Type

Variables with duration Data Type

Variable with categorical Data Type

Version History

See Also

Topics

External Websites

`opts` — Database import options
`SQLImportOptions` object

`varnames` — Variable names
character vector | cell array of character vectors | string scalar | string array | numeric vector

`index` — Index
numeric vector

`Option1,OptionValue1,...,OptionN,OptionValueN` — Import options
name-value pair arguments

Variables with `datetime` Data Type

Variables with `duration` Data Type

Variable with `categorical` Data Type