Import data into MATLAB from database table (2024)

Import data into MATLAB from database table

collapse all in page

Syntax

data = sqlread(conn,tablename)

data = sqlread(conn,tablename,opts)

data = sqlread(___,Name,Value)

[data,metadata] = sqlread(___)

Description

example

data = sqlread(conn,tablename) returns a table by importing data into MATLAB^® from a database table. Executing this function is the equivalent of writing a SELECT * FROM tablename SQL statement in ANSI SQL.

example

data = sqlread(conn,tablename,opts) customizes options for importing data from a database table using the SQLImportOptions object.

example

data = sqlread(___,Name,Value) specifies additional options using one or more name-value arguments with any of the previous input argument combinations. For example, specify Catalog = "cat" to import data from a database table stored in the "cat" catalog.

example

[data,metadata] = sqlread(___) also returns the metadata table, which contains metadata information about the imported data.

Examples

collapse all

Import Data from Database Table

Open Script

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

Open Live Script

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'})

Import Data from Database Table in Specific Schema

Open Script

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

Open Script

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

Open Live Script

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

Open Live Script

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

collapse all

`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 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

`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

collapse all

`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
`VariableType`	Data type of each variable in the imported data	Cell array of character vectors
`FillValue`	Value of missing data for each variable in the imported data	Cell array of missing data values
`MissingRows`	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 the VariableNamingRule name-value 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.
The sqlread function returns an error if you specify the RowFilter name-value argument with the SQLImportOptions object opts. It is ambiguous which of the RowFilter object to use in this case, especially if the filter conditions are different.

Version History

Introduced in R2018a

expand all

R2023a: 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.

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:

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
- Deutsch
- English
- Français
United Kingdom (English)

Asia Pacific

Australia (English)
India (English)
New Zealand (English)
中国
- 简体中文
- English
日本 (日本語)
한국 (한국어)

Contact your local office

Import data into MATLAB from database table (2024)

Syntax

Description

Examples

Import Data from Database Table

Import Data from Database Table Using Import Options

Import Data from Database Table in Specific Schema

Import Specific Number of Rows from Database Table

Preserve Variable Names When Importing Data

Retrieve Metadata Information About Imported Data

Input Arguments

`opts` — Database import options
`SQLImportOptions` object

Name-Value Arguments

`Catalog` — Database catalog name
string scalar | character vector

`Schema` — Database schema name
string scalar | character vector

`MaxRows` — Maximum number of rows to return
positive numeric scalar

`VariableNamingRule` — Variable naming rule
`"modify"` (default) | `"preserve"`

`RowFilter` — Row filter condition
`<unconstrained>` (default) | `matlab.io.RowFilter` object

Output Arguments

`data` — Imported data
table

`metadata` — Metadata information
table

Limitations

Version History

R2023a: Selectively import rows of data based on filter condition

See Also

Functions

Topics

MATLAB Command

Americas

Europe

Asia Pacific

Import data into MATLAB from database table (2024)

Syntax

Description

Examples

Import Data from Database Table

Import Data from Database Table Using Import Options

Import Data from Database Table in Specific Schema

Import Specific Number of Rows from Database Table

Preserve Variable Names When Importing Data

Retrieve Metadata Information About Imported Data

Input Arguments

opts — Database import options SQLImportOptions object

Name-Value Arguments

Catalog — Database catalog name string scalar | character vector

Schema — Database schema name string scalar | character vector

MaxRows — Maximum number of rows to return positive numeric scalar

VariableNamingRule — Variable naming rule "modify" (default) | "preserve"

RowFilter — Row filter condition <unconstrained> (default) | matlab.io.RowFilter object

Output Arguments

data — Imported data table

metadata — Metadata information table

Limitations

Version History

R2023a: Selectively import rows of data based on filter condition

See Also

Functions

Topics

MATLAB Command

Americas

Europe

Asia Pacific

`opts` — Database import options
`SQLImportOptions` object

`Catalog` — Database catalog name
string scalar | character vector

`Schema` — Database schema name
string scalar | character vector

`MaxRows` — Maximum number of rows to return
positive numeric scalar

`VariableNamingRule` — Variable naming rule
`"modify"` (default) | `"preserve"`

`RowFilter` — Row filter condition
`<unconstrained>` (default) | `matlab.io.RowFilter` object

`data` — Imported data
table

`metadata` — Metadata information
table