sqlread
Import data into MATLAB from database table
Syntax
Description
customizes options for importing data from a database table using the data
= sqlread(conn
,tablename
,opts
)SQLImportOptions
object.
specifies additional options using one or more name-value arguments with any of the
previous input argument combinations. For example, specify data
= sqlread(___,Name,Value
)Catalog =
"cat"
to import data from a database table stored in the
"cat"
catalog.
Examples
Import Data from Database Table
Use an ODBC connection to import product data from a database table into MATLAB® using a Microsoft® SQL Server® database. Then, perform a simple data analysis.
Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank username and password. The database contains the table productTable
.
datasource = 'MS SQL Server Auth'; conn = database(datasource,'','');
Check the database connection. If the Message
property is empty, then the connection is successful.
conn.Message
ans = []
Import data from the database table productTable
. The sqlread
function returns a MATLAB® table that contains the product data.
tablename = 'productTable';
data = sqlread(conn,tablename);
Display the first five products.
head(data,5)
productNumber stockNumber supplierNumber unitCost productDescription _____________ ___________ ______________ ________ __________________ 9 1.2597e+05 1003 13 {'Victorian Doll'} 8 2.1257e+05 1001 5 {'Train Set' } 7 3.8912e+05 1007 16 {'Engine Kit' } 2 4.0031e+05 1002 9 {'Painting Set' } 4 4.0034e+05 1008 21 {'Space Cruiser' }
Now, import the data using a row filter. The filter condition is that unitCost
must be less than 15.
rf = rowfilter("unitCost"); rf = rf.unitCost < 15; data = sqlread(conn,tablename,"RowFilter",rf);
Again, display the first five products.
head(data,5)
productNumber stockNumber supplierNumber unitCost productDescription _____________ ___________ ______________ ________ ___________________ 9 1.2597e+05 1003 13 {'Victorian Doll' } 8 2.1257e+05 1001 5 {'Train Set' } 2 4.0031e+05 1002 9 {'Painting Set' } 1 4.0034e+05 1001 14 {'Building Blocks'} 5 4.0046e+05 1005 3 {'Tin Soldier' }
Close the database connection.
close(conn)
Import Data from Database Table Using Import Options
Customize import options when importing data from a database table. Control the import options by creating an SQLImportOptions
object. Then, customize import options for different database columns. Import data using the sqlread
function.
This example uses the patients.xls
file, which contains the columns Gender
, Location
, SelfAssessedHealthStatus
, and Smoker
. The example also uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.
Create a database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank username 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)
opts = SQLImportOptions with properties: ExcludeDuplicates: false VariableNamingRule: 'modify' VariableNames: {'LastName', 'Gender', 'Age' ... and 7 more} VariableTypes: {'char', 'char', 'double' ... and 7 more} SelectedVariableNames: {'LastName', 'Gender', 'Age' ... and 7 more} FillValues: {'', '', NaN ... and 7 more } RowFilter: <unconstrained> VariableOptions: Show all 10 VariableOptions
Display the current import options for the variables selected in the SelectedVariableNames
property of the SQLImportOptions
object.
vars = opts.SelectedVariableNames; varOpts = getoptions(opts,vars)
varOpts = 1x10 SQLVariableImportOptions array with properties: Variable Options: (1) | (2) | (3) | (4) | (5) | (6) | (7) | (8) | (9) | (10) Name: 'LastName' | 'Gender' | 'Age' | 'Location' | 'Height' | 'Weight' | 'Smoker' | 'Systolic' | 'Diastolic' | 'SelfAssessedHealthStatus' Type: 'char' | 'char' | 'double' | 'char' | 'double' | 'double' | 'double' | 'double' | 'double' | 'char' MissingRule: 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' FillValue: '' | '' | NaN | '' | NaN | NaN | NaN | NaN | NaN | '' To access sub-properties of each variable, use getoptions
Change the data types for the Gender
, Location
, SelfAssessedHealthStatus
, and Smoker
variables using the setoptions
function. Because the Gender
, Location
, and SelfAssessedHealthStatus
variables indicate a finite set of repeating values, change their data type to categorical
. Because the Smoker
variable stores the values 0
and 1
, change its data type to logical
. Then, display the updated import options.
opts = setoptions(opts,{'Gender','Location','SelfAssessedHealthStatus'}, ... 'Type','categorical'); opts = setoptions(opts,'Smoker','Type','logical'); varOpts = getoptions(opts,{'Gender','Location','Smoker', ... 'SelfAssessedHealthStatus'})
varOpts = 1x4 SQLVariableImportOptions array with properties: Variable Options: (1) | (2) | (3) | (4) Name: 'Gender' | 'Location' | 'Smoker' | 'SelfAssessedHealthStatus' Type: 'categorical' | 'categorical' | 'logical' | 'categorical' MissingRule: 'fill' | 'fill' | 'fill' | 'fill' FillValue: <undefined> | <undefined> | 0 | <undefined> To access sub-properties of each variable, use getoptions
Import the patients
database table using the sqlread
function, and display the last eight rows of the table.
data = sqlread(conn,tablename,opts); tail(data)
LastName Gender Age Location Height Weight Smoker Systolic Diastolic SelfAssessedHealthStatus _____________ ______ ___ _________________________ ______ ______ ______ ________ _________ ________________________ {'Foster' } Female 30 St. Mary's Medical Center 70 124 false 130 91 Fair {'Gonzales' } Male 48 County General Hospital 71 174 false 123 79 Good {'Bryant' } Female 48 County General Hospital 66 134 false 129 73 Excellent {'Alexander'} Male 25 County General Hospital 69 171 true 128 99 Good {'Russell' } Male 44 VA Hospital 69 188 true 124 92 Good {'Griffin' } Male 49 County General Hospital 70 186 false 119 74 Fair {'Diaz' } Male 45 County General Hospital 68 172 true 136 93 Good {'Hayes' } Male 48 County General Hospital 66 177 false 114 86 Fair
Display a summary of the imported data. The sqlread
function applies the import options to the variables in the imported data.
summary(data)
Variables: LastName: 100×1 cell array of character vectors Gender: 100×1 categorical Values: Female 53 Male 47 Age: 100×1 double Values: Min 25 Median 39 Max 50 Location: 100×1 categorical Values: County General Hospital 39 St. Mary s Medical Center 24 VA Hospital 37 Height: 100×1 double Values: Min 60 Median 67 Max 72 Weight: 100×1 double Values: Min 111 Median 142.5 Max 202 Smoker: 100×1 logical Values: True 34 False 66 Systolic: 100×1 double Values: Min 109 Median 122 Max 138 Diastolic: 100×1 double Values: Min 68 Median 81.5 Max 99 SelfAssessedHealthStatus: 100×1 categorical Values: Excellent 34 Fair 15 Good 40 Poor 11
Now set the filter condition to import only data for patients older than 40 years and not taller than 68 inches.
opts.RowFilter = opts.RowFilter.Age > 40 & opts.RowFilter.Height <= 68
opts = SQLImportOptions with properties: ExcludeDuplicates: false VariableNamingRule: 'modify' VariableNames: {'LastName', 'Gender', 'Age' ... and 7 more} VariableTypes: {'char', 'categorical', 'double' ... and 7 more} SelectedVariableNames: {'LastName', 'Gender', 'Age' ... and 7 more} FillValues: {'', <undefined>, NaN ... and 7 more } RowFilter: Age > 40 & Height <= 68 VariableOptions: Show all 10 VariableOptions
Again, import the patients
database table using the sqlread
function, and display a summary of the imported data.
data = sqlread(conn,tablename,opts); summary(data)
Variables: LastName: 24×1 cell array of character vectors Gender: 24×1 categorical Values: Female 17 Male 7 Age: 24×1 double Values: Min 41 Median 45.5 Max 50 Location: 24×1 categorical Values: County General Hospital 13 St. Mary s Medical Center 5 VA Hospital 6 Height: 24×1 double Values: Min 62 Median 66 Max 68 Weight: 24×1 double Values: Min 119 Median 137 Max 194 Smoker: 24×1 logical Values: True 8 False 16 Systolic: 24×1 double Values: Min 114 Median 121.5 Max 138 Diastolic: 24×1 double Values: Min 68 Median 81.5 Max 96 SelfAssessedHealthStatus: 24×1 categorical Values: Excellent 7 Fair 3 Good 10 Poor 4
Delete the patients
database table using the execute
function.
sqlquery = ['DROP TABLE ' tablename];
execute(conn,sqlquery)
Close the database connection.
close(conn)
Import Data from Database Table in Specific Schema
Use an ODBC connection to import product data from a database table into MATLAB® using a Microsoft® SQL Server® database. Specify the schema where the database table is stored. Then, sort and filter the rows in the imported data and perform a simple data analysis.
Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable
.
datasource = 'MS SQL Server Auth'; conn = database(datasource,'','');
Check the database connection. If the Message
property is empty, then the connection is successful.
conn.Message
ans = []
Import data from the table productTable
. Specify the database schema dbo
. The data
table contains the product data.
tablename = 'productTable'; data = sqlread(conn,tablename,'Schema','dbo');
Display the first few products.
data(1:3,:)
ans = 3×5 table productNumber stockNumber supplierNumber unitCost productDescription _____________ ___________ ______________ ________ __________________ 9 1.2597e+05 1003 13 'Victorian Doll' 8 2.1257e+05 1001 5 'Train Set' 7 3.8912e+05 1007 16 'Engine Kit'
Display the first few product descriptions.
data.productDescription(1:3)
ans = 3×1 cell array {'Victorian Doll'} {'Train Set' } {'Engine Kit' }
Sort the rows in data
by the product description column in alphabetical order.
column = 'productDescription';
data = sortrows(data,column);
Display the first few product descriptions after sorting.
data.productDescription(1:3)
ans = 3×1 cell array {'Building Blocks'} {'Convertible' } {'Engine Kit' }
Close the database connection.
close(conn)
Import Specific Number of Rows from Database Table
Use an ODBC connection to import product data from a database table into MATLAB® using a Microsoft® SQL Server® database. Specify the maximum number of rows to import from the database table.
Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable
.
datasource = 'MS SQL Server Auth'; conn = database(datasource,'','');
Check the database connection. If the Message
property is empty, then the connection is successful.
conn.Message
ans = []
Import data from the table productTable
. Import only three rows of data from the database table. The data
table contains the product data.
tablename = 'productTable'; data = sqlread(conn,tablename,'MaxRows',3)
data = 3×5 table productNumber stockNumber supplierNumber unitCost productDescription _____________ ___________ ______________ ________ __________________ 9 1.2597e+05 1003 13 'Victorian Doll' 8 2.1257e+05 1001 5 'Train Set' 7 3.8912e+05 1007 16 'Engine Kit'
Close the database connection.
close(conn)
Preserve Variable Names When Importing Data
Import product data from a Microsoft® SQL Server® database table into MATLAB® by using an ODBC connection. The table contains a variable name with a non-ASCII character. When importing data, preserve the names of all the variables.
Create an ODBC database connection to an SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable
.
datasource = "MSSQLServerAuth"; conn = database(datasource,"","");
Check the database connection. If the Message
property is empty, then the connection is successful.
conn.Message
ans = []
Add a column to the database table productTable
. The column name contains a non-ASCII character.
sqlquery = "ALTER TABLE productTable ADD tamaño varchar(30)";
execute(conn,sqlquery)
Import data from the database table productTable
. The sqlread
function returns a MATLAB table that contains the product data. Display the first three rows of the data in the table.
tablename = "productTable";
data = sqlread(conn,tablename);
head(data,3)
ans=3×6 table
productNumber stockNumber supplierNumber unitCost productDescription tama_o
_____________ ___________ ______________ ________ __________________ __________
9 1.2597e+05 1003 13 {'Victorian Doll'} {0×0 char}
8 2.1257e+05 1001 5 {'Train Set' } {0×0 char}
7 3.8912e+05 1007 16 {'Engine Kit' } {0×0 char}
The sqlread
function converts the name of the new variable into ASCII characters.
Preserve the name of the variable that contains the non-ASCII character by specifying the VariableNamingRule
name-value pair argument. Import the data again.
data = sqlread(conn,tablename, ... 'VariableNamingRule',"preserve"); head(data,3)
ans=3×6 table
productNumber stockNumber supplierNumber unitCost productDescription tamaño
_____________ ___________ ______________ ________ __________________ __________
9 1.2597e+05 1003 13 {'Victorian Doll'} {0×0 char}
8 2.1257e+05 1001 5 {'Train Set' } {0×0 char}
7 3.8912e+05 1007 16 {'Engine Kit' } {0×0 char}
The sqlread
function preserves the non-ASCII character in the variable name.
Close the database connection.
close(conn)
Retrieve Metadata Information About Imported Data
Retrieve metadata information when importing data from a database table. Import data using the sqlread
function and explore the metadata information by using dot notation.
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)
Import the data into the MATLAB workspace and return metadata information about the imported data.
[data,metadata] = sqlread(conn,tablename);
View the names of the variables in the imported data.
metadata.Properties.RowNames
ans = 6×1 cell array
{'Region' }
{'OutageTime' }
{'Loss' }
{'Customers' }
{'RestorationTime'}
{'Cause' }
View the data type of each variable in the imported data.
metadata.VariableType
ans = 6×1 cell array
{'char' }
{'char' }
{'double'}
{'double'}
{'char' }
{'char' }
View the missing data value for each variable in the imported data.
metadata.FillValue
ans = 6×1 cell array
{0×0 char}
{0×0 char}
{[ NaN]}
{[ NaN]}
{0×0 char}
{0×0 char}
View the indices of the missing data for each variable in the imported data.
metadata.MissingRows
ans = 6×1 cell array
{ 0×1 double}
{ 0×1 double}
{604×1 double}
{328×1 double}
{ 29×1 double}
{ 0×1 double}
Display the first eight rows of the imported data that contain missing restoration time. data
contains restoration time in the fifth variable. Use the numeric indices to find the rows with missing data.
index = metadata.MissingRows{5,1}; nullrestoration = data(index,:); head(nullrestoration)
ans=8×6 table
Region OutageTime Loss Customers RestorationTime Cause
___________ _________________________ ______ __________ _______________ __________________
'SouthEast' '2003-01-23 00:49:00.000' 530.14 2.1204e+05 '' 'winter storm'
'NorthEast' '2004-09-18 05:54:00.000' 0 0 '' 'equipment fault'
'MidWest' '2002-04-20 16:46:00.000' 23141 NaN '' 'unknown'
'NorthEast' '2004-09-16 19:42:00.000' 4718 NaN '' 'unknown'
'SouthEast' '2005-09-14 15:45:00.000' 1839.2 3.4144e+05 '' 'severe storm'
'SouthEast' '2004-08-17 17:34:00.000' 624.1 1.7879e+05 '' 'severe storm'
'SouthEast' '2006-01-28 23:13:00.000' 498.78 NaN '' 'energy emergency'
'West' '2003-06-20 18:22:00.000' 0 0 '' 'energy emergency'
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
conn
— Database connection
connection
object
Database connection, specified as an ODBC connection
object or JDBC connection
object created using the
database
function.
tablename
— Database table name
string scalar | character vector
Database table name, specified as a string scalar or character vector denoting the name of a table in the database.
Example: "employees"
Data Types: string
| char
opts
— Database import options
SQLImportOptions
object
Database import options, specified as an SQLImportOptions
object.
Name-Value Arguments
Specify optional pairs of arguments as
Name1=Value1,...,NameN=ValueN
, where Name
is
the argument name and Value
is the corresponding value.
Name-value arguments must appear after other arguments, but the order of the
pairs does not matter.
Before R2021a, use commas to separate each name and value, and enclose
Name
in quotes.
Example: data =
sqlread(conn,'inventoryTable','Catalog','toy_store','Schema','dbo','MaxRows',5)
imports five rows of data from the database table inventoryTable
stored in the toy_store
catalog and the dbo
schema.
Catalog
— Database catalog name
string scalar | character vector
Database catalog name, specified as a string scalar or character vector. A catalog serves as the container for the schemas in a database and contains related metadata information. A database can have multiple catalogs.
Example: Catalog = "toy_store"
Data Types: string
| char
Schema
— Database schema name
string scalar | character vector
Database schema name, specified as a string scalar or character vector. A schema defines the database tables, views, relationships among tables, and other elements. A database catalog can have numerous schemas.
Example: Schema = "dbo"
Data Types: string
| char
MaxRows
— Maximum number of rows to return
positive numeric scalar
Maximum number of rows to return, specified as the comma-separated pair consisting of
'MaxRows'
and a positive numeric scalar. By default, the
sqlread
function returns all rows from the executed SQL
query. Use this name-value pair argument to limit the number of rows imported into
MATLAB.
Example: 'MaxRows',10
Data Types: double
VariableNamingRule
— Variable naming rule
"modify"
(default) | "preserve"
Variable naming rule, specified as the comma-separated pair consisting of 'VariableNamingRule'
and one of these values:
"modify"
— Remove non-ASCII characters from variable names when thesqlread
function imports data."preserve"
— Preserve most variable names when thesqlread
function imports data. For details, see the Limitations section.
Example: 'VariableNamingRule',"modify"
Data Types: string
RowFilter
— Row filter condition
<unconstrained>
(default) | matlab.io.RowFilter
object
Row filter condition, specified as a
matlab.io.RowFilter
object.
Example: rf = rowfilter("productnumber"); rf =
rf.productnumber <= 5;
sqlread(conn,tablename,"RowFilter",rf)
Output Arguments
data
— Imported data
table
Imported data, returned as a table. The rows of the table correspond to
the rows in the database table tablename
. The variables
in the table correspond to each column in the database table. For columns
that have numeric
data types in the database table, the
variable data types in data
are double
by default. For columns that have text, date
,
time
, or timestamp
data types in
the database table, the variable data types are cell arrays of character
vectors by default.
If the database table contains no data to import, then
data
is an empty table.
metadata
— Metadata information
table
Metadata information, returned as a table with these variables.
Variable Name | Variable Description | Variable Data Type |
---|---|---|
| Data type of each variable in the imported data | Cell array of character vectors |
| Value of missing data for each variable in the imported data | Cell array of missing data values |
| Indices for each occurrence of missing data in each variable of the imported data | Cell array of numeric indices |
By default, the sqlread
function imports text
data as a character vector and numeric data as a double.
FillValue
is an empty character
array (for text data) or NaN
(for numeric
data) by default. To change the missing data value to another
value, use the SQLImportOptions
object.
The RowNames
property of the metadata
table contains
the names of the variables in the imported data.
Limitations
The
sqlread
function returns an error when you use theVariableNamingRule
name-value argument with theSQLImportOptions
objectopts
.When the
VariableNamingRule
name-value pair argument is set to the value"modify"
:The variable names
Properties
,RowNames
, andVariableNames
are reserved identifiers for thetable
data type.The length of each variable name must be less than the number returned by
namelengthmax
.
The
sqlread
function returns an error if you specify theRowFilter
name-value argument with theSQLImportOptions
objectopts
. It is ambiguous which of theRowFilter
object to use in this case, especially if the filter conditions are different.
Version History
Introduced in R2018aR2023a: Selectively import rows of data based on filter condition
You can use the RowFilter
name-value argument to selectively
import rows of data from a database table.
See Also
Functions
sqlfind
|select
|fetch
|sqlinnerjoin
|sqlouterjoin
|database
|close
|databaseImportOptions
|setoptions
|getoptions
|reset
|execute
|sqlupdate
Open Example
You have a modified version of this example. Do you want to open this example with your edits?
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 bat365 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)