setoptions
Customize import options for database data
Syntax
Description
customizes the import options for importing data from a database into MATLAB®. The function returns the opts
= setoptions(opts
,varnames
,Option1,OptionValue1,...,OptionN,OptionValueN
)SQLImportOptions
object. To import data, you use the SQLImportOptions
object, the specified
variable names, and the import options with their corresponding values.
customizes the import options for the variables specified by a numeric index.opts
= setoptions(opts
,index
,Option1,OptionValue1,...,OptionN,OptionValueN
)
Examples
Customize Options When Importing Numeric Data
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
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
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
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
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
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
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:
The data type depends on the variable type in the database. |
'MissingRule' | Missing data rule | Value must be one of the following:
You can specify these values as a character vector or string scalar. Setting the |
'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 |
---|---|
|
The undefined floating-point numbers
For details, see The same conversion applies to all integer classes. |
logical |
|
char or string |
You can change the |
datetime |
|
duration |
|
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 |
|
'TextCaseRule' | Text case |
|
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
| 'default' |
'DatetimeLocale' | Locale to use for interpreting dates | For valid values, see the description of the
| 'en-US' |
'TimeZone' | Time zone | For valid values, see the description of the
| '' |
'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
| {} |
'Protected' | Category protection indicator | For valid values, see the description of the
| false |
'Ordinal' | Mathematical ordering indicator | For valid values, see the description of the
| false |
Data Types: char
| string
Version History
Introduced in R2018b
See Also
databaseImportOptions
| getoptions
| preview
| reset
| close
| database
| execute
| sqlwrite
| sqlread
External Websites
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)