%% Customize Options When Importing Text Data
% Customize import options when importing text data from a database table. Control the import options by creating an SQLImportOptions object. Then, customize the import options for a text database column. Import data using the sqlread function.
% This example uses the patients.xls spreadsheet, which contains the first column LastName. 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 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);

%
% Retrieve the default import options for the LastName and SelfAssessedHealthStatus variables.

varnames = ["LastName" "SelfAssessedHealthStatus"];
varOpts = getoptions(opts,varnames)

%
% Set the import options for the data type of the LastName variable to string. Specify the LastName variable by using a numeric index that finds the variable within the SelectedVariables property of the SQLImportOptions object. Also, set the import options to replace missing data in the LastName variable with the NoName fill value.

index = 1;
opts = setoptions(opts,index,'Type',"string", ...
    'FillValue',"NoName");

%
% Set the import options for the text case of the SelfAssessedHealthStatus variable to uppercase.

varname = "SelfAssessedHealthStatus";
opts = setoptions(opts,varname,'TextCaseRule',"upper");

%
% Import the text data in the selected variables and display the first eight rows. The imported data shows that the LastName variable has the string data type and the SelfAssessedHealthStatus variable text is uppercase.

opts.SelectedVariableNames = ["LastName" "SelfAssessedHealthStatus"];
T = sqlread(conn,tablename,opts);
head(T)

%
% Delete the patients database table using the execute function.

sqlstr = "DROP TABLE ";
sqlquery = strcat(sqlstr,tablename);
execute(conn,sqlquery)

% Close the database connection.

close(conn)

% Copyright 2018 The MathWorks, Inc.