Execute SQL SELECT statement and import data into MATLAB (2024)

Execute SQL SELECT statement and importdata into MATLAB

collapse all in page

Syntax

data = select(conn,selectquery)

data = select(conn,selectquery,Name,Value)

[data,metadata]= select(___)

Description

example

data = select(conn,selectquery) returnsimported data from the database connection conn forthe specified SQL SELECT statement selectquery.

example

data = select(conn,selectquery,Name,Value) specifies additional options using one or more name-value pair arguments. For example, 'MaxRows',10 sets the maximum number of rows to return to 10 rows.

example

[data,metadata]= select(___) returns information about theimported data using any of the input argument combinations in theprevious syntaxes. Use this information to change missing values inthe imported data and view data types for each variable.

Examples

collapse all

Import and Access Data Immediately

Open Script

Import data from a database in one step using the select function. You can access data and perform immediate data analysis.

The code assumes that you have a database table Patients stored on a Microsoft® SQL Server® database. This table contains patient data in 10 columns and rows. The table definition is:

CREATE TABLE Patients( LastName VARCHAR(50), Gender VARCHAR(10), Age TINYINT, Location VARCHAR(300), Height SMALLINT, Weight SMALLINT, Smoker BIT, Systolic FLOAT, Diastolic NUMERIC, SelfAssessedHealthStatus VARCHAR(20))

This 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,'','');

Import all data from the Patients table by executing the SQL SELECT statement using the select function. data is a table that contains the imported data.

selectquery = 'SELECT * FROM Patients';data = select(conn,selectquery)
data = 10×10 table LastName Gender Age Location Height Weight Smoker Systolic Diastolic SelfAssessedHealthStatus __________ ________ ___ __________________________ ______ ______ ______ ________ _________ ________________________ 'Smith' 'Male' 38 'Country General Hospital' -32768 176 true 124 93 'Excellent' 'Johnson' 'Male' 43 'VA Hospital' 69 163 false NaN 77 'Fair' 'Williams' 'Female' 38 '' 64 131 false 125 83 'Good' 'Jones' 'Female' 0 'VA Hospital' 67 133 false 117 75 'Fair' 'Broen' 'Female' 49 'Country General Hospital' 64 119 false 122 80 'Good' 'Davis' 'Female' 46 'St Mary's Medical Center' 68 142 false 121 NaN 'Good' 'Miller' 'Female' 33 'VA Hospital' 64 142 true 130 88 'Good' 'Wilson' 'Male' 40 'VA Hospital' -32768 180 false 115 82 'Good' 'Moore' 'Male' 28 'St Mary's Medical Center' 68 -32768 false 115 78 'Excellent' 'Taylor' 'Female' 31 'Country General Hospital' 68 132 false NaN 86 'Excellent'

Determine the number of male patients by immediately accessing the data. Use the count function to find occurrences in the gender data of the character vector that represents a male. Determine the total number of occurrences.

males = count(data.Gender,'Male');sum(males)
ans = 4

Close the database connection.

close(conn)

Limit Number of Rows in Imported Data

Open Script

Import a limited number of rows from a database in one step using the select function. Database Toolbox™ imports the data using MATLAB® numeric data types that correspond to data types in the database table. After importing data, you can access data and perform immediate data analysis.

The code assumes that you have a database table Patients stored on a Microsoft® SQL Server® database. This table contains patient data in 10 columns and rows. The table definition is:

CREATE TABLE Patients( LastName VARCHAR(50), Gender VARCHAR(10), Age TINYINT, Location VARCHAR(300), Height SMALLINT, Weight SMALLINT, Smoker BIT, Systolic FLOAT, Diastolic NUMERIC, SelfAssessedHealthStatus VARCHAR(20))

Here, connect to a Microsoft® SQL Server® Version 11.00.2100 database using 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,'','');

Import data from the Patients table by executing the SQL SELECT statement using the select function. Limit the number of imported rows using the name-value pair argument 'MaxRows'.

data is a table. The MATLAB® data types in the table correspond to the data types in the database. Here, Age has data type uint8 that corresponds to TINYINT in the table definition.

metadata is a table that contains additional information about each variable in data.

  • VariableType -- MATLAB® data type

  • MissingValue -- NULL value representation

  • MissingRows -- Vector of row indices that contain a missing value

selectquery = 'SELECT * FROM Patients';[data,metadata] = select(conn,selectquery,'MaxRows',5)
See Also
Import data into MATLAB from database tableFind information about all table types in databaseInsert MATLAB data into database table
data = 5×10 table LastName Gender Age Location Height Weight Smoker Systolic Diastolic SelfAssessedHealthStatus __________ ________ ___ __________________________ ______ ______ ______ ________ _________ ________________________ 'Smith' 'Male' 38 'Country General Hospital' -32768 176 true 124 93 'Excellent' 'Johnson' 'Male' 43 'VA Hospital' 69 163 false NaN 77 'Fair' 'Williams' 'Female' 38 '' 64 131 false 125 83 'Good' 'Jones' 'Female' 0 'VA Hospital' 67 133 false 117 75 'Fair' 'Broen' 'Female' 49 'Country General Hospital' 64 119 false 122 80 'Good' metadata = 10×3 table VariableType MissingValue MissingRows ____________ ____________ ____________ LastName 'char' '' [0×1 double] Gender 'char' '' [0×1 double] Age 'uint8' [ 0] [ 4] Location 'char' '' [0×1 double] Height 'int16' [-32768] [ 1] Weight 'int16' [-32768] [0×1 double] Smoker 'logical' [ 0] [0×1 double] Systolic 'single' [ NaN] [ 2] Diastolic 'double' [ NaN] [0×1 double] SelfAssessedHealthStatus 'char' '' [0×1 double]

Determine the number of male patients by immediately accessing the data. Use the count function to find occurrences in the gender data of the character vector that represents a male. Determine the total number of occurrences.

males = count(data.Gender,'Male');sum(males)
ans = 2

Close the database connection.

close(conn)

View Information About Imported Data

Open Script

Import data from a database in one step using the select function. Database Toolbox™ imports the data using MATLAB® numeric data types that correspond to data types in the database table. You can view data type information in the imported data. You can also access data and perform immediate data analysis.

The code assumes that you have a database table Patients stored on a Microsoft® SQL Server® database. This table contains patient data in 10 columns and rows. The table definition is:

CREATE TABLE Patients( LastName VARCHAR(50), Gender VARCHAR(10), Age TINYINT, Location VARCHAR(300), Height SMALLINT, Weight SMALLINT, Smoker BIT, Systolic FLOAT, Diastolic NUMERIC, SelfAssessedHealthStatus VARCHAR(20))

Here, connect to a Microsoft® SQL Server® Version 11.00.2100 database using 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,'','');

Import all data from the Patients table by executing the SQL SELECT statement using the select function.

data is a table. The MATLAB® data types in the table correspond to the data types in the database. Here, Age has the MATLAB® data type uint8 that corresponds to TINYINT in the table definition.

metadata is a table that contains additional information about each variable in data.

  • VariableType -- MATLAB® data type

  • MissingValue -- Null value representation

  • MissingRows -- Vector of row indices that contain a missing value

selectquery = 'SELECT * FROM Patients';[data,metadata] = select(conn,selectquery)
data = 10×10 table LastName Gender Age Location Height Weight Smoker Systolic Diastolic SelfAssessedHealthStatus __________ ________ ___ __________________________ ______ ______ ______ ________ _________ ________________________ 'Smith' 'Male' 38 'Country General Hospital' -32768 176 true 124 93 'Excellent' 'Johnson' 'Male' 43 'VA Hospital' 69 163 false NaN 77 'Fair' 'Williams' 'Female' 38 '' 64 131 false 125 83 'Good' 'Jones' 'Female' 0 'VA Hospital' 67 133 false 117 75 'Fair' 'Broen' 'Female' 49 'Country General Hospital' 64 119 false 122 80 'Good' 'Davis' 'Female' 46 'St Mary's Medical Center' 68 142 false 121 NaN 'Good' 'Miller' 'Female' 33 'VA Hospital' 64 142 true 130 88 'Good' 'Wilson' 'Male' 40 'VA Hospital' -32768 180 false 115 82 'Good' 'Moore' 'Male' 28 'St Mary's Medical Center' 68 -32768 false 115 78 'Excellent' 'Taylor' 'Female' 31 'Country General Hospital' 68 132 false NaN 86 'Excellent' metadata = 10×3 table VariableType MissingValue MissingRows ____________ ____________ ____________ LastName 'char' '' [0×1 double] Gender 'char' '' [0×1 double] Age 'uint8' [ 0] [ 4] Location 'char' '' [0×1 double] Height 'int16' [-32768] [2×1 double] Weight 'int16' [-32768] [ 9] Smoker 'logical' [ 0] [0×1 double] Systolic 'single' [ NaN] [2×1 double] Diastolic 'double' [ NaN] [ 6] SelfAssessedHealthStatus 'char' '' [0×1 double]

View data types of each variable in the table.

metadata.VariableType
ans = 10×1 cell array 'char' 'char' 'uint8' 'char' 'int16' 'int16' 'logical' 'single' 'double' 'char'

Determine the number of male patients by immediately accessing the data. Use the count function to find occurrences in the gender data of the character vector that represents a male. Determine the total number of occurrences.

males = count(data.Gender,'Male');sum(males)
ans = 4

Close the database connection.

close(conn)

Change Missing Values in Imported Data Using for Loop

Open Script

Import data from a database in one step using the select function. During import, the select function sets default values for missing data in each row. Use the information about the imported data to change the default values.

The code assumes that you have a database table Patients stored on a Microsoft® SQL Server® database. This table contains patient data in 10 columns and rows. The table definition is:

CREATE TABLE Patients( LastName VARCHAR(50), Gender VARCHAR(10), Age TINYINT, Location VARCHAR(300), Height SMALLINT, Weight SMALLINT, Smoker BIT, Systolic FLOAT, Diastolic NUMERIC, SelfAssessedHealthStatus VARCHAR(20))

Here, connect to a Microsoft® SQL Server® Version 11.00.2100 database using 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,'','');

Import all data from the Patients table by executing the SQL SELECT statement using the select function.

data is a table that contains the imported data.

metadata is a table that contains additional information about each variable in data.

  • VariableType -- MATLAB® data type

  • MissingValue -- NULL value representation

  • MissingRows -- Vector of row indices that indicate the location of missing values

selectquery = 'SELECT * FROM Patients';[data,metadata] = select(conn,selectquery)
data = 10×10 table array LastName Gender Age Location Height Weight Smoker Systolic Diastolic SelfAssessedHealthStatus __________ ________ ___ __________________________ ______ ______ ______ ________ _________ ________________________ 'Smith' 'Male' 38 'Country General Hospital' -32768 176 true 124 93 'Excellent' 'Johnson' 'Male' 43 'VA Hospital' 69 163 false NaN 77 'Fair' 'Williams' 'Female' 38 '' 64 131 false 125 83 'Good' 'Jones' 'Female' 0 'VA Hospital' 67 133 false 117 75 'Fair' 'Broen' 'Female' 49 'Country General Hospital' 64 119 false 122 80 'Good' 'Davis' 'Female' 46 'St Mary's Medical Center' 68 142 false 121 NaN 'Good' 'Miller' 'Female' 33 'VA Hospital' 64 142 true 130 88 'Good' 'Wilson' 'Male' 40 'VA Hospital' -32768 180 false 115 82 'Good' 'Moore' 'Male' 28 'St Mary's Medical Center' 68 -32768 false 115 78 'Excellent' 'Taylor' 'Female' 31 'Country General Hospital' 68 132 false NaN 86 'Excellent' metadata = 10×3 table array VariableType MissingValue MissingRows ____________ ____________ ____________ LastName 'char' '' [0×1 double] Gender 'char' '' [0×1 double] Age 'uint8' [ 0] [ 4] Location 'char' '' [0×1 double] Height 'int16' [-32768] [2×1 double] Weight 'int16' [-32768] [ 9] Smoker 'logical' [ 0] [0×1 double] Systolic 'single' [ NaN] [2×1 double] Diastolic 'double' [ NaN] [ 6] SelfAssessedHealthStatus 'char' '' [0×1 double]

Retrieve indices that indicate the location of missing values in the Height variable using the metadata output argument.

values = metadata.MissingRows{'Height'}
values = 1 8

Change the default value for missing data from -32768 to 0 using a for loop. Access the imported data using the indices.

for i = 1:length(values) data.Height(values(i)) = 0;end

View the imported data.

data.Height
ans = 10×1 int16 column vector 0 69 64 67 64 68 64 0 68 68

Missing values appear as 0.

Close the database connection.

close(conn)

Change Missing Values in Imported Data Using Vector Indexing

Open Script

Import data from a database in one step using the select function. During import, the select function sets default values for missing data in each row. Use the information about the imported data to change default values by indexing into the vector.

The code assumes that you have a database table Patients stored on a Microsoft® SQL Server® database. This table contains patient data in 10 columns and rows. The table definition is:

CREATE TABLE Patients( LastName VARCHAR(50), Gender VARCHAR(10), Age TINYINT, Location VARCHAR(300), Height SMALLINT, Weight SMALLINT, Smoker BIT, Systolic FLOAT, Diastolic NUMERIC, SelfAssessedHealthStatus VARCHAR(20))

Here, connect to a Microsoft® SQL Server® Version 11.00.2100 database using 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,'','');

Import all data from the Patients table by executing the SQL SELECT statement using the select function.

data is a table that contains the imported data.

metadata is a table that contains additional information about each variable in data.

  • VariableType -- MATLAB® data type

  • MissingValue -- NULL value representation

  • MissingRows -- Vector of row indices that indicate the location of missing values

selectquery = 'SELECT * FROM Patients';[data,metadata] = select(conn,selectquery)
data = 10×10 table array LastName Gender Age Location Height Weight Smoker Systolic Diastolic SelfAssessedHealthStatus __________ ________ ___ __________________________ ______ ______ ______ ________ _________ ________________________ 'Smith' 'Male' 38 'Country General Hospital' -32768 176 true 124 93 'Excellent' 'Johnson' 'Male' 43 'VA Hospital' 69 163 false NaN 77 'Fair' 'Williams' 'Female' 38 '' 64 131 false 125 83 'Good' 'Jones' 'Female' 0 'VA Hospital' 67 133 false 117 75 'Fair' 'Broen' 'Female' 49 'Country General Hospital' 64 119 false 122 80 'Good' 'Davis' 'Female' 46 'St Mary's Medical Center' 68 142 false 121 NaN 'Good' 'Miller' 'Female' 33 'VA Hospital' 64 142 true 130 88 'Good' 'Wilson' 'Male' 40 'VA Hospital' -32768 180 false 115 82 'Good' 'Moore' 'Male' 28 'St Mary's Medical Center' 68 -32768 false 115 78 'Excellent' 'Taylor' 'Female' 31 'Country General Hospital' 68 132 false NaN 86 'Excellent' metadata = 10×3 table array VariableType MissingValue MissingRows ____________ ____________ ____________ LastName 'char' '' [0×1 double] Gender 'char' '' [0×1 double] Age 'uint8' [ 0] [ 4] Location 'char' '' [0×1 double] Height 'int16' [-32768] [2×1 double] Weight 'int16' [-32768] [ 9] Smoker 'logical' [ 0] [0×1 double] Systolic 'single' [ NaN] [2×1 double] Diastolic 'double' [ NaN] [ 6] SelfAssessedHealthStatus 'char' '' [0×1 double]

Retrieve indices that indicate the location of missing values in the Height variable using the metadata output argument.

values = metadata(5,3)valuesindex = values.MissingRows{1}
values = table MissingRows ____________ Height [2×1 double]valuesindex = 1 8

Change the default value for missing data from -32768 to 0 using vector indexing.

data.Height(valuesindex) = 0;

View the imported data.

data.Height
ans = 10×1 int16 column vector 0 69 64 67 64 68 64 0 68 68

Missing values appear as 0.

Close the database connection.

close(conn)

Input Arguments

collapse all

selectquery — SQL SELECT statement
character vector | string

SQL SELECT statement, specified as a charactervector or string. The select function only executesSQL SELECT statements. To execute other SQL statements,use the exec function.

Example: 'SELECT * FROM inventoryTable'

Data Types: char | string

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: 'MaxRows',100,'QueryTimeOut',5 returns 100 rows of data and waits 5 seconds to execute the SQL SELECT statement.

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

QueryTimeOut — SQL query timeout
positive numeric scalar

SQL query timeout, specified as the comma-separated pair consistingof 'QueryTimeOut' and a positive numeric scalar.By default, the select function ignores the timeoutvalue. Use this name-value pair argument to specify the number ofseconds to wait for executing the SQL query selectquery.

Example: 'QueryTimeOut',15

Output Arguments

collapse all

data — Imported data
table

Imported data, returned as a table. The rows of the table correspondto the rows of data returned from the executed SQL query selectquery.The variable names of the table specify the columns in the SQL query.

The select function returns date or time data as character vectors in the table. This function returns text as character vectors or a cell array of character vectors. Strings are not supported in the table.

If no data to import exists, then data isan empty table.

metadata — Information about imported data
table

Information about imported data, returned as a table. The row names of metadata are variable names in data. This function stores each variable name in the metadata table as a cell array. metadata has these variable names:

  • VariableType — Data typesof each variable in data

  • MissingValue — Representationof missing value for each variable in data

  • MissingRows — Vector ofrow indices that indicate locations of missing values for each variablein data

This table shows how MATLAB represents NULL valuesin the database by default after data import.

Database Data Type Default NULL Value

SIGNED TINYINT

-128

UNSIGNED TINYINT

SIGNED SMALLINT

-32768

UNSIGNED SMALLINT

SIGNED INT

-2147483648

UNSIGNED INT

SIGNED BIGINT

-9223372036854775808

UNSIGNED BIGINT

REAL

NaN

FLOAT

NaN

DOUBLE

NaN

DECIMAL

NaN

NUMERIC

NaN

Boolean

false

Date, time, or text

''

To change the NULL value representation inthe imported data, replace the default value by looping through theimported data or using vector indexing.

Limitations

  • You cannot customize missing values in the outputargument data using the select function.Index into the imported data using the metadata outputargument instead.

  • The output argument data doesnot support cell and struct datatypes. The select function only supports table.

Alternative Functionality

Use the exec and fetch functions for full functionalitywhen importing data. For differences between the select functionand this alternative, see Data Import Using Database Explorer App or Command Line.

Version History

Introduced in R2017a

See Also

exec | fetch | database | close | count

Topics

  • Data Import Using Database Explorer App or Command Line
  • Data Import Memory Management
  • Import Data from Database Table Using sqlread Function

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.
 
 
 
 
 
 
 
 Execute SQL SELECT statement and importdata into MATLAB (1) 
 
 
 
 
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)
    •  
  • 中国  
    •  
  • 日本 (日本語)
    •  
  • 한국 (한국어)
    •  
 
 
 
Contact your local office
 
 
 
 
 

                                                        Execute SQL SELECT statement and import
data into MATLAB (2024)
                        

                            
                        

                    

                                                                                                            
Top Articles

                            

                                                                    



    
    

        Diversity is the key to any garden
    


                                                                    



    
    

        20 Pink Flowers That Will Add Timeless Elegance to Your Garden
    


                                                                                                    



    
    

        Z Spars International - Conception des produits
    


                



    
    

        The 8 Best Winter Gloves of 2024
    


                                                                

                                                                
Latest Posts

                        

                                                            



    
    

        Low-Maintenance Outdoor Plants | Online Plant Nursery
    


                                                            



    
    

        Create Your Own Backyard Bbq Oasis: A Step-By-Step Guide To Building An Outdoor Grill With Cinder Blocks
    


                                                    

                    
                    

        

    
    
    



                    

    
Article information

    
Author:  Nathanial Hackett 

    
Last Updated:   

    
Views: 5722

    
Rating: 4.1 / 5 (72 voted)

    
Reviews: 95% of readers found this page helpful





    
Author information

    
Name: Nathanial Hackett

    
Birthday: 1997-10-09

    
Address: Apt. 935 264 Abshire Canyon, South Nerissachester, NM 01800

    
Phone: +9752624861224

    
Job: Forward Technology Assistant

    
Hobby: Listening to music, Shopping, Vacation, Baton twirling, Flower arranging, Blacksmithing, Do it yourself

    
Introduction: My name is Nathanial Hackett, I am a lovely, curious, smiling, lively, thoughtful, courageous, lively person who loves writing and wants to share my knowledge and understanding with you.