databaseImportOptions
Define import options for database data
Description
opts = databaseImportOptions(conn,source)SQLImportOptions
object, opts, from the specified database connection and source.
source can be a database table name or SQL query.
conn can be any of the following connection objects:
MySQL®
PostgreSQL®
DuckDB™
SQLite
ODBC
JDBC
Use this function to customize how you import your data into MATLAB®. For example, you can select specific columns for import or apply a row filter to limit which records are imported from a database table.
opts = databaseImportOptions(conn,source,Name=Value)source is a database table and you need to select a catalog or
schema. For example, Catalog="toystore_doc" retrieves data from the
toystore_doc database catalog.
Examples
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'})varOpts =
1x4 SQLVariableImportOptions array with properties:
Variable Options:
(1) | (2) | (3) | (4)
Name: 'Gender' | 'Location' | 'Smoker' | 'SelfAssessedHealthStatus'
Type: 'categorical' | 'categorical' | 'logical' | 'categorical'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill'
FillValue: <undefined> | <undefined> | 0 | <undefined>
To access sub-properties of each variable, use getoptions
Import the patients database table using the sqlread function, and display the last eight rows of the table.
data = sqlread(conn,tablename,opts); tail(data)
LastName Gender Age Location Height Weight Smoker Systolic Diastolic SelfAssessedHealthStatus
_____________ ______ ___ _________________________ ______ ______ ______ ________ _________ ________________________
{'Foster' } Female 30 St. Mary's Medical Center 70 124 false 130 91 Fair
{'Gonzales' } Male 48 County General Hospital 71 174 false 123 79 Good
{'Bryant' } Female 48 County General Hospital 66 134 false 129 73 Excellent
{'Alexander'} Male 25 County General Hospital 69 171 true 128 99 Good
{'Russell' } Male 44 VA Hospital 69 188 true 124 92 Good
{'Griffin' } Male 49 County General Hospital 70 186 false 119 74 Fair
{'Diaz' } Male 45 County General Hospital 68 172 true 136 93 Good
{'Hayes' } Male 48 County General Hospital 66 177 false 114 86 Fair
Display a summary of the imported data. The sqlread function applies the import options to the variables in the imported data.
summary(data)
Variables:
LastName: 100×1 cell array of character vectors
Gender: 100×1 categorical
Values:
Female 53
Male 47
Age: 100×1 double
Values:
Min 25
Median 39
Max 50
Location: 100×1 categorical
Values:
County General Hospital 39
St. Mary s Medical Center 24
VA Hospital 37
Height: 100×1 double
Values:
Min 60
Median 67
Max 72
Weight: 100×1 double
Values:
Min 111
Median 142.5
Max 202
Smoker: 100×1 logical
Values:
True 34
False 66
Systolic: 100×1 double
Values:
Min 109
Median 122
Max 138
Diastolic: 100×1 double
Values:
Min 68
Median 81.5
Max 99
SelfAssessedHealthStatus: 100×1 categorical
Values:
Excellent 34
Fair 15
Good 40
Poor 11
Now set the filter condition to import only data for patients older than 40 years and not taller than 68 inches.
opts.RowFilter = opts.RowFilter.Age > 40 & opts.RowFilter.Height <= 68
opts =
SQLImportOptions with properties:
ExcludeDuplicates: false
VariableNamingRule: 'modify'
VariableNames: {'LastName', 'Gender', 'Age' ... and 7 more}
VariableTypes: {'char', 'categorical', 'double' ... and 7 more}
SelectedVariableNames: {'LastName', 'Gender', 'Age' ... and 7 more}
FillValues: {'', <undefined>, NaN ... and 7 more }
RowFilter: Age > 40 & Height <= 68
VariableOptions: Show all 10 VariableOptions
Again, import the patients database table using the sqlread function, and display a summary of the imported data.
data = sqlread(conn,tablename,opts); summary(data)
Variables:
LastName: 24×1 cell array of character vectors
Gender: 24×1 categorical
Values:
Female 17
Male 7
Age: 24×1 double
Values:
Min 41
Median 45.5
Max 50
Location: 24×1 categorical
Values:
County General Hospital 13
St. Mary s Medical Center 5
VA Hospital 6
Height: 24×1 double
Values:
Min 62
Median 66
Max 68
Weight: 24×1 double
Values:
Min 119
Median 137
Max 194
Smoker: 24×1 logical
Values:
True 8
False 16
Systolic: 24×1 double
Values:
Min 114
Median 121.5
Max 138
Diastolic: 24×1 double
Values:
Min 68
Median 81.5
Max 96
SelfAssessedHealthStatus: 24×1 categorical
Values:
Excellent 7
Fair 3
Good 10
Poor 4
Delete the patients database table using the execute function.
sqlquery = ['DROP TABLE ' tablename];
execute(conn,sqlquery)Close the database connection.
close(conn)
Customize import options when importing data from the results of an SQL query on a database. Control the import options by creating an SQLImportOptions object. Then, customize import options for different columns in the SQL query. Import data using the fetch function.
This example uses the employees_database.mat file, which contains the columns first_name, hire_date, and DEPARTMENT_NAME. 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 employee information into the MATLAB® workspace.
employeedata = load('employees_database.mat');Create the employees and departments database tables using the employee information.
emps = employeedata.employees; depts = employeedata.departments; sqlwrite(conn,'employees',emps) sqlwrite(conn,'departments',depts)
Create an SQLImportOptions object using an SQL query and the databaseImportOptions function. This query retrieves all information for employees who are sales managers or programmers.
sqlquery = strcat("SELECT * from employees e join departments d ", ... "on (e.department_id = d.department_id) WHERE ", ... "(job_id = 'IT_PROG' or job_id = 'SA_MAN')"); opts = databaseImportOptions(conn,sqlquery)
opts =
SQLImportOptions with properties:
ExcludeDuplicates: false
VariableNamingRule: 'modify'
VariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
VariableTypes: {'double', 'char', 'char' ... and 13 more}
SelectedVariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
FillValues: { NaN, '', '' ... and 13 more }
RowFilter: <unconstrained>
VariableOptions: Show all 16 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 =
1x16 SQLVariableImportOptions array with properties:
Variable Options:
(1) | (2) | (3) | (4) | (5) | (6) | (7) | (8) | (9) | (10) | (11) | (12) | (13) | (14) | (15) | (16)
Name: 'employee_id' | 'first_name' | 'last_name' | 'email' | 'phone_number' | 'hire_date' | 'job_id' | 'salary' | 'commission_pct' | 'manager_id' | 'department_id' | 'temporary' | 'DEPARTMENT_ID' | 'DEPARTMENT_NAME' | 'MANAGER_ID' | 'LOCATION_ID'
Type: 'double' | 'char' | 'char' | 'char' | 'char' | 'char' | 'char' | 'double' | 'double' | 'double' | 'double' | 'double' | 'double' | 'char' | 'double' | 'double'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill'
FillValue: NaN | '' | '' | '' | '' | '' | '' | NaN | NaN | NaN | NaN | NaN | NaN | '' | NaN | NaN
To access sub-properties of each variable, use getoptions
Change the data types for the hire_date, DEPARTMENT_NAME, and first_name variables using the setoptions function. Then, display the updated import options. Because hire_date stores date and time data, change the data type of this variable to datetime. Because DEPARTMENT_NAME designates a finite set of repeating values, change the data type of this variable to categorical. Also, change the name of this variable to lowercase. Because first_name stores text data, change the data type of this variable to string.
opts = setoptions(opts,'hire_date','Type','datetime'); opts = setoptions(opts,'DEPARTMENT_NAME','Name','department_name', ... 'Type','categorical'); opts = setoptions(opts,'first_name','Type','string'); vars = opts.SelectedVariableNames; varOpts = getoptions(opts,vars)
varOpts =
1x16 SQLVariableImportOptions array with properties:
Variable Options:
(1) | (2) | (3) | (4) | (5) | (6) | (7) | (8) | (9) | (10) | (11) | (12) | (13) | (14) | (15) | (16)
Name: 'employee_id' | 'first_name' | 'last_name' | 'email' | 'phone_number' | 'hire_date' | 'job_id' | 'salary' | 'commission_pct' | 'manager_id' | 'department_id' | 'temporary' | 'DEPARTMENT_ID' | 'department_name' | 'MANAGER_ID' | 'LOCATION_ID'
Type: 'double' | 'string' | 'char' | 'char' | 'char' | 'datetime' | 'char' | 'double' | 'double' | 'double' | 'double' | 'double' | 'double' | 'categorical' | 'double' | 'double'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill'
FillValue: NaN | <missing> | '' | '' | '' | NaT | '' | NaN | NaN | NaN | NaN | NaN | NaN | <undefined> | NaN | NaN
To access sub-properties of each variable, use getoptions
Select the three modified variables using the SelectVariableNames property.
opts.SelectedVariableNames = ["first_name","hire_date","department_name"];
Set the filter condition to import only the data for the employees hired before January 1, 2006.
opts.RowFilter = opts.RowFilter.hire_date < datetime(2006,01,01)
opts =
SQLImportOptions with properties:
ExcludeDuplicates: false
VariableNamingRule: 'modify'
VariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
VariableTypes: {'double', 'string', 'char' ... and 13 more}
SelectedVariableNames: {'first_name', 'hire_date', 'department_name'}
FillValues: { NaN, <missing>, '' ... and 13 more }
RowFilter: hire_date < 01-Jan-2006
VariableOptions: Show all 16 VariableOptions
Import and display the results of the SQL query using the fetch function.
employees_data = fetch(conn,sqlquery,opts)
employees_data=4×3 table
"David" 25-Jun-2005 IT
"John" 01-Oct-2004 Sales
"Karen" 05-Jan-2005 Sales
"Alberto" 10-Mar-2005 Sales
Delete the employees and departments database tables using the execute function.
execute(conn,'DROP TABLE employees') execute(conn,'DROP TABLE departments')
Close the database connection.
close(conn)
Customize import options when importing data from a database table. Control the import options by creating an SQLImportOptions object. Specify the location of the database table by using the database catalog and schema. 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 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 in the toy_store database catalog and dbo database schema using the patient information.
tablename = 'patients'; sqlwrite(conn,tablename,patients, ... 'Catalog','toy_store','Schema','dbo')
Create an SQLImportOptions object using the patients database table and the databaseImportOptions function. Specify the toy_store database catalog and dbo database schema for the location of the database table.
opts = databaseImportOptions(conn,tablename, ... 'Catalog','toy_store','Schema','dbo');
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'})varOpts =
1x4 SQLVariableImportOptions array with properties:
Variable Options:
(1) | (2) | (3) | (4)
Name: 'Gender' | 'Location' | 'Smoker' | 'SelfAssessedHealthStatus'
Type: 'categorical' | 'categorical' | 'logical' | 'categorical'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill'
FillValue: <undefined> | <undefined> | 0 | <undefined>
To access sub-properties of each variable, use getoptions
Import the patients database table using the sqlread function, and display the last eight rows of the table.
data = sqlread(conn,tablename,opts,'Catalog','toy_store','Schema','dbo'); tail(data)
ans=8×10 table
'Foster' Female 30 St. Mary's Medical Center 70 124 0 130 91 Fair
'Gonzales' Male 48 County General Hospital 71 174 0 123 79 Good
'Bryant' Female 48 County General Hospital 66 134 0 129 73 Excellent
'Alexander' Male 25 County General Hospital 69 171 1 128 99 Good
'Russell' Male 44 VA Hospital 69 188 1 124 92 Good
'Griffin' Male 49 County General Hospital 70 186 0 119 74 Fair
'Diaz' Male 45 County General Hospital 68 172 1 136 93 Good
'Hayes' Male 48 County General Hospital 66 177 0 114 86 Fair
Display a summary of the imported data. The sqlread function applies the import options to the variables in the imported data.
summary(data)
Variables:
LastName: 100×1 cell array of character vectors
Gender: 100×1 categorical
Values:
Female 53
Male 47
Age: 100×1 double
Values:
Min 25
Median 39
Max 50
Location: 100×1 categorical
Values:
County General Hospital 39
St. Mary s Medical Center 24
VA Hospital 37
Height: 100×1 double
Values:
Min 60
Median 67
Max 72
Weight: 100×1 double
Values:
Min 111
Median 142.5
Max 202
Smoker: 100×1 logical
Values:
True 34
False 66
Systolic: 100×1 double
Values:
Min 109
Median 122
Max 138
Diastolic: 100×1 double
Values:
Min 68
Median 81.5
Max 99
SelfAssessedHealthStatus: 100×1 categorical
Values:
Excellent 34
Fair 15
Good 40
Poor 11
Delete the patients database table from the toy_store database catalog and the dbo database schema by using the execute function.
sqlquery = ['DROP TABLE toy_store.dbo.' tablename];
execute(conn,sqlquery)Close the database connection.
close(conn)
This example shows how to create an SQLImportOptions object and modify one of the import options.
Use the duckdb function to connect to the DuckDB™ database file, nyctaxi.db.
filePath = fullfile(matlabroot,"toolbox","database","dbdata","nyctaxi.db"); conn = duckdb(filePath,"ReadOnly",true)
conn =
connection with properties:
Database: "nyctaxi"
Database Properties:
ReadOnly: true
AutoCommit: "on"
Catalog and Schema Information:
DefaultCatalog: "nyctaxi"
Catalogs: "nyctaxi"
Schemas: "main"
Database and Driver Information:
DatabaseProductName: "DuckDB"
DatabaseProductVersion: "v1.3.2"
Create an SQLImportOptions object by using the databaseImportOptions function and specify the database table name "demo".
tableName = "demo";
opts = databaseImportOptions(conn,tableName)opts =
SQLImportOptions with properties:
ExcludeDuplicates: false
VariableNamingRule: 'preserve'
VariableNames: {'vendorid', 'tpep_pickup_datetime', 'tpep_dropoff_datetime' ... and 16 more}
VariableTypes: {'double', 'datetime', 'datetime' ... and 16 more}
SelectedVariableNames: {'vendorid', 'tpep_pickup_datetime', 'tpep_dropoff_datetime' ... and 16 more}
FillValues: { NaN, NaT, NaT ... and 16 more }
RowFilter: <unconstrained>
VariableOptions: Show all 19 VariableOptions
Import the database table into MATLAB® by using the sqlread function.
data = sqlread(conn,tableName)
data=10000×19 table
vendorid tpep_pickup_datetime tpep_dropoff_datetime passenger_count trip_distance pickup_longitude pickup_latitude ratecodeid store_and_fwd_flag dropoff_longitude dropoff_latitude payment_type fare_amount extra mta_tax tip_amount tolls_amount improvement_surcharge total_amount
________ ____________________ _____________________ _______________ _____________ ________________ _______________ __________ __________________ _________________ ________________ ____________ ___________ _____ _______ __________ ____________ _____________________ ____________
2 09-Jun-2015 14:58:55 09-Jun-2015 15:26:41 1 2.63 -73.983 40.73 1 "N" -73.977 40.759 2 18 0 0.5 0 0 0.3 18.8
2 09-Jun-2015 14:58:55 09-Jun-2015 15:02:13 1 0.32 -73.997 40.732 1 "N" -73.994 40.731 2 4 0 0.5 0 0 0.3 4.8
1 09-Jun-2015 14:58:56 09-Jun-2015 16:08:52 2 20.6 -73.983 40.767 2 "N" -73.798 40.645 1 52 0 0.5 10 5.54 0.3 68.34
1 09-Jun-2015 14:58:57 09-Jun-2015 15:12:00 1 1.2 -73.97 40.762 1 "N" -73.969 40.75 1 9 0 0.5 1.96 0 0.3 11.76
2 09-Jun-2015 14:58:58 09-Jun-2015 15:00:49 5 0.49 -73.978 40.786 1 "N" -73.972 40.785 2 3.5 0 0.5 0 0 0.3 4.3
2 09-Jun-2015 14:58:59 09-Jun-2015 15:42:02 1 16.64 -73.97 40.757 2 "N" -73.79 40.647 1 52 0 0.5 11.67 5.54 0.3 70.01
1 09-Jun-2015 14:58:59 09-Jun-2015 15:03:07 1 0.8 -73.976 40.745 1 "N" -73.983 40.735 1 5 0 0.5 1 0 0.3 6.8
2 09-Jun-2015 14:59:00 09-Jun-2015 15:21:31 1 3.23 -73.982 40.767 1 "N" -73.994 40.736 2 16.5 0 0.5 0 0 0.3 17.3
1 09-Jun-2015 14:59:01 09-Jun-2015 15:08:17 1 1.1 -73.949 40.788 1 "N" -73.954 40.775 1 7.5 0 0.5 0 0 0.3 8.3
2 09-Jun-2015 14:59:02 09-Jun-2015 15:19:34 6 1.79 -73.993 40.747 1 "N" -73.972 40.76 1 13.5 0 0.5 2.86 0 0.3 17.16
1 09-Jun-2015 14:59:02 09-Jun-2015 15:28:12 2 4.8 -73.984 40.756 1 "N" -73.916 40.764 1 22 0 0.5 5 0 0.3 27.8
2 09-Jun-2015 14:59:03 09-Jun-2015 15:09:21 1 0.96 -73.962 40.776 1 "N" -73.969 40.766 1 8 0 0.5 2.2 0 0.3 11
2 09-Jun-2015 14:59:03 09-Jun-2015 15:11:48 5 1.12 -73.978 40.753 1 "N" -73.99 40.747 2 9 0 0.5 0 0 0.3 9.8
1 09-Jun-2015 14:59:04 09-Jun-2015 15:04:12 1 0.7 -73.978 40.767 1 "N" -73.981 40.774 1 5.5 0 0.5 1.55 0 0.3 7.85
2 09-Jun-2015 14:59:01 09-Jun-2015 15:21:56 1 2.06 -73.969 40.786 1 "N" -73.961 40.765 1 15 0 0.5 3.95 0 0.3 19.75
1 09-Jun-2015 14:59:04 09-Jun-2015 15:17:55 1 1.6 -73.977 40.784 1 "N" -73.954 40.772 2 12.5 0 0.5 0 0 0.3 13.3
⋮
Change the name of the first variable by using the setoptions function.
opts = setoptions(opts,1,Name="VendorID");Import the data using the sqlread and specify opts as the third input argument. Confirm that name of the first variable is changed to VendorID.
data = sqlread(conn,tableName,opts)
data=10000×19 table
VendorID tpep_pickup_datetime tpep_dropoff_datetime passenger_count trip_distance pickup_longitude pickup_latitude ratecodeid store_and_fwd_flag dropoff_longitude dropoff_latitude payment_type fare_amount extra mta_tax tip_amount tolls_amount improvement_surcharge total_amount
________ ____________________ _____________________ _______________ _____________ ________________ _______________ __________ __________________ _________________ ________________ ____________ ___________ _____ _______ __________ ____________ _____________________ ____________
2 09-Jun-2015 14:58:55 09-Jun-2015 15:26:41 1 2.63 -73.983 40.73 1 "N" -73.977 40.759 2 18 0 0.5 0 0 0.3 18.8
2 09-Jun-2015 14:58:55 09-Jun-2015 15:02:13 1 0.32 -73.997 40.732 1 "N" -73.994 40.731 2 4 0 0.5 0 0 0.3 4.8
1 09-Jun-2015 14:58:56 09-Jun-2015 16:08:52 2 20.6 -73.983 40.767 2 "N" -73.798 40.645 1 52 0 0.5 10 5.54 0.3 68.34
1 09-Jun-2015 14:58:57 09-Jun-2015 15:12:00 1 1.2 -73.97 40.762 1 "N" -73.969 40.75 1 9 0 0.5 1.96 0 0.3 11.76
2 09-Jun-2015 14:58:58 09-Jun-2015 15:00:49 5 0.49 -73.978 40.786 1 "N" -73.972 40.785 2 3.5 0 0.5 0 0 0.3 4.3
2 09-Jun-2015 14:58:59 09-Jun-2015 15:42:02 1 16.64 -73.97 40.757 2 "N" -73.79 40.647 1 52 0 0.5 11.67 5.54 0.3 70.01
1 09-Jun-2015 14:58:59 09-Jun-2015 15:03:07 1 0.8 -73.976 40.745 1 "N" -73.983 40.735 1 5 0 0.5 1 0 0.3 6.8
2 09-Jun-2015 14:59:00 09-Jun-2015 15:21:31 1 3.23 -73.982 40.767 1 "N" -73.994 40.736 2 16.5 0 0.5 0 0 0.3 17.3
1 09-Jun-2015 14:59:01 09-Jun-2015 15:08:17 1 1.1 -73.949 40.788 1 "N" -73.954 40.775 1 7.5 0 0.5 0 0 0.3 8.3
2 09-Jun-2015 14:59:02 09-Jun-2015 15:19:34 6 1.79 -73.993 40.747 1 "N" -73.972 40.76 1 13.5 0 0.5 2.86 0 0.3 17.16
1 09-Jun-2015 14:59:02 09-Jun-2015 15:28:12 2 4.8 -73.984 40.756 1 "N" -73.916 40.764 1 22 0 0.5 5 0 0.3 27.8
2 09-Jun-2015 14:59:03 09-Jun-2015 15:09:21 1 0.96 -73.962 40.776 1 "N" -73.969 40.766 1 8 0 0.5 2.2 0 0.3 11
2 09-Jun-2015 14:59:03 09-Jun-2015 15:11:48 5 1.12 -73.978 40.753 1 "N" -73.99 40.747 2 9 0 0.5 0 0 0.3 9.8
1 09-Jun-2015 14:59:04 09-Jun-2015 15:04:12 1 0.7 -73.978 40.767 1 "N" -73.981 40.774 1 5.5 0 0.5 1.55 0 0.3 7.85
2 09-Jun-2015 14:59:01 09-Jun-2015 15:21:56 1 2.06 -73.969 40.786 1 "N" -73.961 40.765 1 15 0 0.5 3.95 0 0.3 19.75
1 09-Jun-2015 14:59:04 09-Jun-2015 15:17:55 1 1.6 -73.977 40.784 1 "N" -73.954 40.772 2 12.5 0 0.5 0 0 0.3 13.3
⋮
Close the database connection.
close(conn);
Input Arguments
Database connection, specified as any of the following:
MySQL
connectionobject created by using themysqlfunction.PostgreSQL
connectionobject created by using thepostgresqlfunction.DuckDB
connectionobject created by using theduckdbfunction.SQLite
connectionobject created by using thesqlitefunction.ODBC
connectionobject created by using thedatabasefunction.JDBC
connectionobject created by using thedatabasefunction.
Source, specified as a string scalar or character vector that represents a database table name or an SQL query for importing data from a database.
Example: "inventorytable"
Example: "SELECT * FROM inventorytable"
Data Types: string | char
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: opts =
databaseImportOptions(conn,"inventorytable",Catalog="toystore_doc",Schema="dbo")
defines import options for the table, catalog, schema.
Database catalog name, specified as a string scalar or character vector. A catalog serves as a container for schemas in a database which hold related metadata.
Use this name-value argument only if source is a database
table.
Example: Catalog="toy_store"
Data Types: string | char
Database schema name, specified as a string scalar or character vector. A schema defines database tables, relationships among tables, and other elements.
Use this name-value argument only if source is a database
table.
Example: Schema="dbo"
Data Types: string | char
Output Arguments
Database import options, returned as an SQLImportOptions object that contains the following properties:
ExcludeDuplicates
VariableNamingRule
VariableNames
VariableTypes
SelectedVariableNames
FillValues
RowFilter
VariableOptions
After you create an SQLImportOptions object, you can
modify an option by using setoptions. For example, opts =
setoptions(opts,1,Name="MyNewName") changes the name of the first variable
listed in VariableNames to "MyNewName".
Version History
Introduced in R2018bYou can specify DuckDB
connection objects when using the
databaseImportOptions function.
See Also
setoptions | getoptions | reset | close | database | execute | sqlwrite | sqlread | fetch | mysql | postgresql | duckdb
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.
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
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
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
- United Kingdom (English)