%% 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'])

%
%% 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'])

%
% 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)

%
%% 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)

%
%% 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)

% Copyright 2018 The MathWorks, Inc.