MATLAB: How to use datainsert/fastinsert functions for an Access database

accessconnectiondatadatabaseDatabase ToolboxdatainsertexportfastinsertMATLABodbcsql commandstabletoolbox

Hello,

So I've been working on a project for awhile now and through the hiccups, I've finally gotten close to creating the script/function to do what I need. My problem now is that I can't seem to get those functions to work. Below is the command and the error I keep getting.

curs2 = fastinsert(conn,tablename,columns,tabledata);
Error using database.odbc.connection/fastinsert
Too many output arguments.

I've also tried datainsert, but I get the same thing. Anyways, conn is my connection, tablename is the name of the table in the Access database, columns is a cell of the column names in the database (that were extracted from an excel file (excelfile(1,1:end)), and tabledata is the data I wish to store in the Access database (also extracted from an excel file (excelfile(2:end,1:end)).

I've built this script to be used for similar data sets but with varying rows and/or columns. I would greatly appreciate any help as I can't seem to get this data to be easily inserted into the database I've made.

Thank you in advanced!

-L

Best Answer

The error is likely due to the assignment of fastinsert to curs2. That is not a supported syntax. Instead, try the following to view the database:

fastinsert(conn,tablename,columns,tabledata);
curs = exec(conn,'select * from tablename');
curs = fetch(curs);
curs.Data

Related Solutions

MATLAB: Do i receive the error ‘Too many output arguments’ when using sqlite/exec command

Hi Markus, I ran your code on my own SQLite database and got the same error as you did and noticed something curious that is not entirely clear on the SQLite or exec documentation pages. In the Exec documentation page

https://www-integ3.mathworks.com/help/releases/R2018a/database/ug/exec.html?searchHighlight=exec&s_tid=doc_srchtitle

it says the following

“exec(conn,sqlquery) performs database operations on a SQLite database file by executing the SQL statement sqlquery for the SQLite connection conn using the MATLAB® interface to SQLite.”

However it does not mention SQLite in the examples below where it sets the result of exec to the variable curs. I set up another connection to a database and it looks like the SQLite connection is a different type of object than a normal database connection object in MATLAB. It doesn’t look like it is possible to set the value of exec to be a cursor object for a SQLite connection made using the sqlite function, however you should be able to get a proper connection object if you instead establish the connection with the JDBC driver and database function.

https://www.mathworks.com/help/database/ug/sqlite-jdbc-windows.html#bt8kopk-3

You can interface with it from the command line as they show at the bottom of the doc page and this object should let you assign the result of exec to be a cursor object.

MATLAB: How to create a table in Access with Matlab that allows for different names of columns and rows without being hard coded

Note that you can link Access directly to an Excel file. There's no need to go through matlab through that. Maybe you should explore that first.

I find your question odd and it doesn't look you fully understand how databases work. You can't name rows of a database, it's simply not even a concept. As for naming columns, that is exactly what you do in your CREATE TABLE statement, you give a name to each column. In your example these are 'Item Id', 'Item number', 'Location', and 'Order'. So you already know how to name these.

If you want to be able to build your CREATE TABLE dynamically from entries in a cell array, then you're going to have to do something to what I showed in your previous question. In your statement, you not only need to dynamically put the names of the row, that's easy to do, but also the types of the rows, that's a lot more complicated if you need to infer that from the cell array itself.

One very rough solution, untested (there may be bugs / typos):

function sql = maketablefromcell(tablename, c)
    %tablename: the name of the table to create
    %c: cell array, whose first row contains the names of the columns
    %The type of the columns is infered from the content of rows 2 onward and is either integer, varchar, double or boolean
      colsql = cell(1, size(c, 2));
      for col = 1:size(c, 2)
          if ischar(c{2, col})
              coltype = 'varchar';  %assume all other rows are char
          elseif islogical(c{2, col})
              coltype = 'bit';     %assume all other rows are logical. BIT is SQL data type for logical
          elseif isnumeric(c{2, col})
              allnums = cell2mat(cellfun(@(m) m(:), c, 'UniformOutput', false)); %reshape row 2:end to column vector and concatenate into one matrix
              if all(mod(allnums, 1) == 0)
                  coltype = 'integer';
              else
                  coltype = 'float';   %SQL float is matlab double
              end
          else
              error('Unrecognised data type for column %d', col);
          end
          colsql{col} = sprintf('%s %s', c{1, col}, coltype);
      end
      sql = sprintf('CREATE TABLE %s (%s)', tablename, strjoin(colsql, ','));
  end

Related Question