Import data into MATLAB from database table
data = sqlread(conn,tablename,opts)SQLImportOptions object.
data = sqlread(___,Name,Value)'Catalog','cat' imports data from a database table stored in
the 'cat' catalog.
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 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 database table productTable. The sqlread function returns a MATLAB® table that contains the product data.
tablename = 'productTable';
data = sqlread(conn,tablename);
Display the first few products.
head(data,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'
Close the database connection.
close(conn)
Customize import options when importing data from a database table. Control the import options by creating an SQLImportOptions object. Then, customize 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 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)
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 }
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)
ans=8×10 table
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
Delete the patients database table using the execute function.
sqlquery = ['DROP TABLE ' tablename];
execute(conn,sqlquery)Close the database connection.
close(conn)
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)
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)
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 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)
conn — Database connectionconnection objectDatabase connection, specified as an ODBC connection
object or JDBC connection object created using the
database function.
tablename — Database table nameDatabase table name, specified as a character vector or string scalar denoting the name of a table in the database.
Example: 'employees'
Data Types: char | string
opts — Database import optionsSQLImportOptions objectDatabase import options, specified as an SQLImportOptions object.
Specify optional
comma-separated pairs of Name,Value arguments. Name is
the argument name and Value is the corresponding value.
Name must appear inside quotes. You can specify several name and value
pair arguments in any order as
Name1,Value1,...,NameN,ValueN.
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 nameDatabase catalog name, specified as the comma-separated pair consisting of
'Catalog' and a character vector or string scalar. A catalog
serves as the container for the schemas in a database and contains related metadata
information. A database can have numerous catalogs.
Example: 'Catalog','toy_store'
Data Types: char | string
'Schema' — Database schema nameDatabase schema name, specified as the comma-separated pair consisting of
'Schema' and a character vector or string scalar. 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: char | string
'MaxRows' — Maximum number of rows to returnMaximum 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 the sqlread function imports data.
"preserve" — Preserve most variable names when the
sqlread function imports data. For details, see
the Limitations section.
Example: 'VariableNamingRule',"modify"
Data Types: string
data — Imported dataImported 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 informationMetadata 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.
The name-value pair argument 'VariableNamingRule' has these limitations:
The sqlread function returns an error when
you use the 'VariableNamingRule' name-value pair
argument with the SQLImportOptions object
opts.
When the 'VariableNamingRule' name-value pair
argument is set to the value 'modify':
The variable names Properties,
RowNames, and
VariableNames are reserved
identifiers for the table data
type.
The length of each variable name must be less than the
number returned by namelengthmax.
close | database | databaseImportOptions | execute | fetch | getoptions | reset | select | setoptions | sqlfind | sqlinnerjoin | sqlouterjoin
You have a modified version of this example. Do you want to open this example with your edits?