Tutorial (Database Toolbox User's Guide)

Database Toolbox User's Guide

Exporting Multiple Records from MATLAB

In this example, multiple records are imported, manipulated in MATLAB, and then exported to a database. Specifically, you import sales figures for all products, by month, into MATLAB. Then you compute the total sales for each month. Finally, you export the monthly totals to a new table.

You use this Database Toolbox command:

insert

If you want to see or copy the commands for this part of the tutorial, or if you want to run the set of commands, use the M-file matlab\toolbox\database\dbdemos\dbinsert2demo.m.

1.: Set up a data source for the Microsoft Access tutorial database that shipped with the Database Toolbox. It is in the matlab\toolbox\database\dbdemos directory. Refer to the directions in "Setting Up a Data Source". Name the data source tutorial and for the description, type Tutorial for inserting multiple records.

2.: Connect to the database - type:

conn = database('tutorial', '', '')

: You define the returned connection structure as conn. You do not need a user name or password to access the tutorial database.

3.: Import the sales figures. Specifically, import all data from the salesVolume table. Type:

curs = exec(conn, 'select * from salesVolume');
curs = fetch(curs)

4.: To get a sense of the data you imported, view the column names in the fetched data set - type:

columnnames(curs)

ans =
'Stock Number', 'January', 'February', 'March', 'April', 'May', 
'June', 'July', 'August', 'September', 'October', 'November', 
'December'

5.: To get a sense of what the data is, view the data for January, which is in column two - type:

curs.data(:,2)

ans =
        5000
        3000
        2500
        4300
        1200
        3500
        1800
        2400
        1400
        1375

6.: Get the size of the cell array containing the fetched data set, assigning the dimensions to m and n. In a later step, you use these values to compute the monthly totals.

[m,n] = size(curs.data)

m =
    10
n =
    13

7.: Compute the monthly totals - type:

for i = 2:n
    monthly(i-1,1) = sum(curs.data(:,i))
end

: This creates a column of data, monthly, in which each row is the total sales volume of all products for that month. For example, when i is 2, row 1 of monthly is the total of all rows in column 2 of curs.data, column 2 being the sales volume for January.

: The result is:

8.: In order to export the column of data, you must first convert it to a cell array - type:

monthlyTotals = num2cell(monthly)

: num2cell takes the data in monthly and assigns each row to a row in a new cell array, monthlyTotals, which you will export in a later step.

9.: Create a string array containing the column names into which you are inserting the data. In a later step, we will insert the data into the salesTotal column of the yearlySales table; here we assign the variable colNames to the array. Type:

colNames{1,1} = 'salesTotal'

10.: Insert the data into the yearlySales table - type:

insert(conn, 'yearlySales', colNames, monthlyTotals)

11.: View the yearlySales table in the tutorial database in Access to be sure the data was imported correctly.

12.: Close the cursor and disconnect from the database.

close(curs)
close(conn)

[ Previous | Help Desk | Next ]