Customize Options for Importing Data from Database into MATLAB

This example shows how to customize import options when importing data from a database table. Control the import options by creating an SQLImportOptions object. Then, customize the import options for multiple database columns. Import data using the sqlread function.

The example uses the patients.xls spreadsheet, which contains patient information. Also, the example uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.

Create Database Connection

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 Example Data

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 SQLImportOptions Object

Create an SQLImportOptions object using the patients database table and the databaseImportOptions function.

opts = databaseImportOptions(conn,tablename);

Display the default data types of the variables by accessing the VariableNames and VariableTypes properties of the SQLImportOptions object using dot notation.

disp([opts.VariableNames' opts.VariableTypes'])

    'LastName'                    'char'  
    'Gender'                      'char'  
    'Age'                         'double'
    'Location'                    'char'  
    'Height'                      'double'
    'Weight'                      'double'
    'Smoker'                      'double'
    'Systolic'                    'double'
    'Diastolic'                   'double'
    'SelfAssessedHealthStatus'    'char'  

Customize Import Options

Change the data types of multiple variables. Convert the data type for all text variables to string. Also, convert the data type for all numeric variables to single.

textvars = {'LastName','Gender','Location','SelfAssessedHealthStatus'};
opts = setoptions(opts,textvars,'Type','string');

numvars = {'Age','Height','Weight','Systolic','Smoker','Diastolic'};
opts = setoptions(opts,numvars,'Type','single');

Display the updated data types of the variables.

disp([opts.VariableNames' opts.VariableTypes'])

    'LastName'                    'string'
    'Gender'                      'string'
    'Age'                         'single'
    'Location'                    'string'
    'Height'                      'single'
    'Weight'                      'single'
    'Smoker'                      'single'
    'Systolic'                    'single'
    'Diastolic'                   'single'
    'SelfAssessedHealthStatus'    'string'

Set the import options to replace missing data in the specified variables with the fill value unknown.

varnames = {'LastName','Location'};
opts = setoptions(opts,varnames,'FillValue',"unknown");

Set the import options to change the text of the SelfAssessedHealthStatus variable to lowercase.

varname = {'SelfAssessedHealthStatus'};
opts = setoptions(opts,varname,'TextCaseRule',"lower");

Set the import options to omit rows with missing data in the LastName variable.

varname = {'LastName'};
opts = setoptions(opts,varname,'MissingRule',"omitrow");

Preview Data Before Importing

Before importing the data, preview it by using the customized import options.

T = preview(opts)

T=8×10 table
     LastName      Gender     Age             Location              Height    Weight    Smoker    Systolic    Diastolic    SelfAssessedHealthStatus
    __________    ________    ___    ___________________________    ______    ______    ______    ________    _________    ________________________

    "Smith"       "Male"      38     "County General Hospital"        71       176        1         124          93              "excellent"       
    "Johnson"     "Male"      43     "VA Hospital"                    69       163        0         109          77              "fair"            
    "Williams"    "Female"    38     "St. Mary's Medical Center"      64       131        0         125          83              "good"            
    "Jones"       "Female"    40     "VA Hospital"                    67       133        0         117          75              "fair"            
    "Brown"       "Female"    49     "County General Hospital"        64       119        0         122          80              "good"            
    "Davis"       "Female"    46     "St. Mary's Medical Center"      68       142        0         121          70              "good"            
    "Miller"      "Female"    33     "VA Hospital"                    64       142        1         130          88              "good"            
    "Wilson"      "Male"      40     "VA Hospital"                    68       180        0         115          82              "good"            

Import Data Using Import Options

Import the variables with the customized data types by using the sqlread function, and display the first eight rows of imported data.

T = sqlread(conn,tablename,opts);
head(T)

ans=8×10 table
     LastName      Gender     Age             Location              Height    Weight    Smoker    Systolic    Diastolic    SelfAssessedHealthStatus
    __________    ________    ___    ___________________________    ______    ______    ______    ________    _________    ________________________

    "Smith"       "Male"      38     "County General Hospital"        71       176        1         124          93              "excellent"       
    "Johnson"     "Male"      43     "VA Hospital"                    69       163        0         109          77              "fair"            
    "Williams"    "Female"    38     "St. Mary's Medical Center"      64       131        0         125          83              "good"            
    "Jones"       "Female"    40     "VA Hospital"                    67       133        0         117          75              "fair"            
    "Brown"       "Female"    49     "County General Hospital"        64       119        0         122          80              "good"            
    "Davis"       "Female"    46     "St. Mary's Medical Center"      68       142        0         121          70              "good"            
    "Miller"      "Female"    33     "VA Hospital"                    64       142        1         130          88              "good"            
    "Wilson"      "Male"      40     "VA Hospital"                    68       180        0         115          82              "good"            

Delete Example Data and Close Database Connection

Delete the patients database table using the execute function.

sqlquery = ['DROP TABLE ' tablename];
execute(conn,sqlquery)

Close the database connection.

close(conn)