How can I create a table in Access with Matlab that allows for different names of columns and rows without being hard coded?

Hello,

So I'm making progress in my creation of a function that creates a table in Access from Matlab, but I'm trying to make it even more robust. What I mean by this is that, lets say I have an n by m (where n can be any number of rows and m can be any number of columns) cell (imported as data from excel and turn into said cell) and I want it to be able to pick up the first row and first column of this cell as the titles to the data the is inside the n by m cell. The row and column names can change so it has to be able to detect it from the cell, I just don't know how to do that or I am close but not doing it right.

Here's what I've got so far.

CreateTable = ['CREATE TABLE Item(Item ID varchar, '...
      'Item Number varchar,Location varchar,Order int)'];

I also don't think this is complete because I believe this only does the first column of the data and misses the top first row with other indicators.

I'm think there has got to be a way to get it to where it would be something like:

 ['CREATE TABLE Item(data(1,1:end) varchar)'];

Where data is the n by m cell. Again, still working on how to grab the names given in the top row.

Is this possible?

Thank you,

L

[L]

So I've figured out half (or maybe a quarter of the problem) of it; I put together these two lines.

ColumnNames = data(1,:); % For the names I want to put in the first row of the new table in Access.

RowNames = data(2:end,1); % For the other names I need in the table that start below data(1) and proceed down the first column.

Now I just need to figure out how to export these two things into the Create Table part and I should be all set. Problem is, I'm struggling with the syntax to pull that off. Assuming it can even be done.

I appreciate everyone's help.

Thank you,

L

[John BG]

L

if you store the table in CSV format then both Excel and Access will open it and there you can display and process.

John BG

<mailto:[e-mail] [e-mail]>

Answer 1

[Guillaume]

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

[L]

Guillaume,

You have been a great help. Each time I read a post of yours, I come to understand things just a bit more.

As far as bypassing Matlab and linking Access to Excel, the reason for Matlab in the mix is to perform analysis on the data from the excel files. Access is where the data is being stored and serves as a central location for those without the excel files, a repository of sorts, to be used when information needs to be pulled again when the excel files are elsewhere.

I'm trying to come up with something that easily stores these excel files into Access, and then can also graph the data (the analysis) by using what has been stored in the Access database. So hopefully that clears up my intent.

The last part of your post sounds exactly like what I am trying to do. Dynamically insert the column names based off of the cell as well as define the type of the rows.

In these data sets they should more or less be the same, with perhaps varying lengths of columns and rows. In the first columns, starting from the second row and proceeding to the bottom row, those should likely be repeating entries of names and numbers (like ScrewDriver001). Everything else, is just numerical data, quantities, durability percent, location (in numbers), purchases, etc.; by everything else I mean everything starting from the second column and second row, down and to the right.

I'm hoping to provide this information so that perhaps you could help me apply your solutions to my problem in the right context. I've been toying with your code from the last post, but the one from this post makes more sense to me, so I'll also experiment with this one some more for now.

Again, I greatly appreciate your assistance Guillaume.

Thank you,

L

[Guillaume]

If I understood correctly, the code above already does what you want.

It may even be a bit too sophisticated in that if the value in the 2nd row is numeric, it attempts to determine if the whole column is integer maskering as double or double, and that test is designed to cope with cells containing matrices as well as scalar. Thinking back on it that last part is daft, since you won't be able to store a matrix in database column as double. So simplification:

          %...
          elseif isnumeric(c{2, col})
              if all(mod(cell2mat(c(2:end, col)), 1) == 0) %check if scalars in row 2 to end are integer
                  coltype = 'integer';
              else
                  coltype = 'float';   %SQL float is matlab double
              end
          %...

[L]

Guillaume,

After working with your code some, I can't seem to get past the first few lines. It has trouble running. Here's what I'm doing along with the error it keeps throwing.

function sql = maketable(tablename, exceldata)

%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(exceldata,2));

for col = 1:size(exceldata, 2)
    if ischar(exceldata{2, col})
        coltype = 'varchar';  %assume all other rows are char
    elseif islogical(exceldata{2, col})
        coltype = 'bit';     %assume all other rows are logical. BIT is SQL data type for logical
    elseif isnumeric(exceldata{2, col})
        if all(mod(cell2mat(exceldata(2:end, col)), 1) == 0) %check if scalars in row 2 to end are integer
            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', exceldata{1, col}, coltype);
end
sql = sprintf('CREATE TABLE %s (%s)', exceldata, strjoin(colsql, ','));
end

>> maketable
Not enough input arguments.

Error in maketable (line 7)
colsql = cell(1,size(exceldata,2));

It then prompts me to edit the command and add inputs, but I'm not sure what inputs it is asking for. If I understand this snippet of code correctly, it should have all the inputs it needs right? Is there an input I need to specify?

Thank you,

L

[John BG]

L

if you store the table in CSV format then both Excel and Access will open it and there you can display and process.

John BG

[L]

John,

Although what you're saying makes sense, that is not the goal I am trying to achieve.

Guillaume,

I think I've just about got things figured out. With your help I've been able to get the SQL statement setup with all the names of the columns. I then define the statement and put it into this:

exec(conn,sql)

It then returned this:

cursor with properties:

         Data: 0
     RowLimit: 0
     SQLQuery: 'CREATE TABLE Example (Item ID varchar,Location (position in meters) integer,Color2 (bl) float,Height1 …'
      Message: 'ODBC Driver Error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in field …'
         Type: 'ODBCCursor Object'
    Statement: [1×1 database.internal.ODBCStatementHandle]

I think it might be an error because it doesn't have '' on each column name. But that is just a guess. I'm going to try and mess with it to see if all it needs are the '' to make it work. Any additional help would be much appreciated. I believe It is very close to working.

Thank you again!

-L

EDIT: After rereading the link you posted about the syntax of the "Create Table" SQL statement, I don't think the '' are the problem and after messing with it for the last few hours I hope you will be able to help provide some input. Let me know what you'd like me to provide and I'll be happy to help you help me.

[Guillaume]

It's fine to have spaces in column names but in that case the name must be wrapped in the proper characters. With the driver I was using, these are [], so changing the code to:

      colsql{col} = sprintf('[%s] %s', exceldata{1, col}, coltype);
  end
  sql = sprintf('CREATE TABLE [%s] (%s)', exceldata, strjoin(colsql, ','));

should fix this. Other drivers may require different wrapping characters, such as `. See which one works for you, it's not normally ".

Note that in any case there are limitations to the name of columns (aka fields in the link): no more than 64 characters, no .![]` and for table names and query names (aka views), no "

[L]

Guillaume,

Success!! It created the table just as it was needed. Now I just have to use datainsert to put everything from Column one to the right and row two to the bottom. That has to be much simpler then everything else right? Is it similar to the Create Table syntax? Or is there a more intuitive way to basically drop the exceldata right into the database when the table is created?

Thank you so much for your help Guillaume!

-L

[Guillaume]

I would assume that the database toolbox provides tools for inserting rows into tables easily, but I have no idea. As I said, I've never used it/don't have it.

Otherwise, you have to build an INSERT query the same way as the CREATE TABLE query. You do need to know the table schema for the insert query, so I would actually change the whole code to:

1. function to get the table schema, to then be used by create table and insert queries:

function tableschema = gettableschema(exceldata)
   %exceldata: cell array, whose first row contains the names of the columns
   %The type of the columns is inferred from the content of rows 2 onward and is either integer, varchar, double or boolean
   %tableschema: a two column cell array whose rows describe the columns of the table. column 1 is the name of the columns, column 2 is their sql types
   tableschema = cell(2, size(exceldata,2)); 
   for colidx = 1:size(exceldata, 2)
      tableschema{colidx, 1} = exceldata{1, col});
      if ischar(exceldata{2, col})
         tableschema{colidx, 2} = 'varchar';  
      elseif islogical(exceldata{2, col})
         tableschema{colidx, 2} = 'bit'; 
      elseif isnumeric(exceldata{2, col})
         if all(mod(cell2mat(exceldata(2:end, col)), 1) == 0) %check if scalars in row 2 to end are integer
         tableschema{colidx, 2} = 'integer';
         else
         tableschema{colidx, 2} = 'float';   %SQL float is matlab double
         end
      else
         error('Unrecognised data type for column %d', col);
      end
   end
end

2. function to create a table given a schema

function createtable(connection, tablename, tableschema)
   %connection: connection object that is going to execute the create query
   %tablename: the name of the table to create
   %tableschema: cell array which describes the table schema.
   %See gettableschema for details
   colssql = arrayfun(@(r) sprintf('[%s] %s', tableschema{r, 1}, tableschema{r, 2}), 1:size(tableschema, 1), 'UniformOutput', false);
   sql = sprintf('CREATE TABLE [%s] (%s), tablename, strjoin(colssql, ', '));
   exec(conn, sql);
end

3. function to insert rows given a schema

function insertrows(connection, tablename, tableschema, rows)
   %connection: connection object that is going to execute the insert query
   %tablename: the name of the table to create
   %tableschema: cell array which describes the table schema.
   %See gettableschema for details
   %rows: cell array of rows to insert. the number of columns must match the table schema 
   assert(size(rows, 2) == numel(tableschema), 'mismatch between number of columns and table schema)
   colnames = strjoin(cellfun(@(n) sprintf('[%s]', n), tableschema(:, 1), 'UniformOutput', false), ', ');
   for r = 1:size(rows, 1)
       colvalues = strjoin(cellfun(@formatvalue, tableschema(:, 2), rows(r, :).', 'UniformOutput', false), ', ');
       sql = sprintf('INSERT INTO [%s] (%s) VALUES (%s)', tablename, colnames, colvalues);
       exec(connection, sql);
   end
end

4. function to format column values into string depending on the column type

function strvalue = formatvalue(coltype, colvalue)
   %coltype: one of {'varchar', 'bit', 'integer', 'float'}
   %value: value to format
   if isempty(value)
      strvalue = 'NULL';
   else
      switch coltype
         case 'integer'
              strvalue = sprintf('%i', colvalue);
         case 'float'
              strvalue = sprintf('%f', colvalue);
         case 'bit'
              strvalue = sprintf('%u', logical(colvalue));
         case 'varchar'
              strvalue = sprinf('"%s"', strrep(colvalue, '"', '""');
         otherwise
              error('Invalid table schema');
      end
   end
end

And an example of the main function/script:

%exceldata: cell array
%conn: connection
schema = gettableschema(exceldata);
createtable(conn, 'somename', schema);
insertrows(conn, 'somename', schema, exceldata(2:end, :));

[L]

Guillaume,

Wow. Just curious how long did it take you to piece all that together, or are you drawing from some previous work? Either way it is truly impressive and I am very grateful for all of your help. I wish there was more that I could do to provide feedback on your excellence aside from accepting the answer.

Anyways, so I am using the database toolbox and there is a tool/function called datainsert that is supposed to do what I need, but I can't get it to work right.

The structure of it is datainsert(connection,tablename,col,data), where connection is conn in my case, tablename is the name of the table I've created, col is colsql in my case and data is the information corresponding to each column previously identified.

The error I keep receiving is one that states that there are too many outputs. If you want I could post the details here but I was thinking I would simply ask another question in regards to the datainsert function and how to use it in my case.

At least if I can't figure it out with the datainsert in a simpler, more ergonomic way then I believe what you've posted will be what I'll need. At the very least it will allow those without database toolbox the capability to do the things I am trying to do.

Thank you very much!

-L

[L]

Guillaume,

Working through your script I keep getting the columns mismatch error. I had been trying the datainsert function with the toolbox and I was also getting an error similar stating the columns and number of fields didn't match. I'm not sure what to do at this point, its like I've almost got it but for some reason it doesn't take.

Thank you,

L

[Guillaume]

What is the exact SQL string you're sending (or the datainsert arguments).

As I said, I don't have the database toolbox so can't help much with datainsert.

[L]

datainsert(conn,tablename,columns,tabledata);
curs = exec(conn,'select * from tablename');
curs = fetch(curs);
curs.Data;
Error using database.odbc.connection/insert (line 247)
ODBC Driver Error: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'Range' in expression.

Error in database.odbc.connection/datainsert (line 40)
insert( connect,tableName,fieldNames,data );

The above is the latest attempts I've been making. I was simply trying datainsert(conn,tablename,columns,tabledata) and it wasn't working, someone then suggested I try to us the above syntax, but now it is getting caught up on one of the column names. One of the columns in my data set is 'Range (ft)' and it omits the (ft) part and things that Range should be a function or something. I remember reading somewhere that there is something you're supposed to do if your column names are similar to things like 'Date', 'Time'. Perhaps 'Range' is one of them. I'm just not sure how to correct this and I've spent hours searching.

EDIT: Forgot this. columns = exceldata(1,1:end) (the first row of my data containing the column names and tabledata = exceldata(2:end,1:end) (the data that needs to be inserted).

Thanks again!

[Guillaume]

The driver thinks Range is a function because of the (ft) that follows it makes it look like a function call. The fix is simple: enclose the name in the proper escape character for the driver you're using (possibly [] or ''). If datainsert doesn't do that for you, you'll have to do it yourself.

Hence, why I asked for the exact SQL sent to the driver, or the contents of the arguments sent to datainsert

[L]

Ok great. Thank you, I figured it was something like that but messing with it I couldn't get it to stick. I supposed it is similar to modifying the SQL statement in one of the above posts. So columns should equal something like (and I'm guessing right now) sprintf{'[%s]',strjoin(tabledata,',')}. Along those lines, I think. Thank you for your continued support Guillaume, I apologize for my ignorance.

[L]

Thank you for all of your help Guillaume. I've figured it all out. I just had to add the [] to the column names before I handed it off to the datainsert function. Everything works.

Thank you again!

Answer 2

[John BG]

Why not starting from any of this links

• Export to Excel format, Access will open it anyway https://uk.mathworks.com/help/matlab/import_export/exporting-to-excel-spreadsheets.html?s_tid=srchtitle
• SQL, whatever https://uk.mathworks.com/matlabcentral/fileexchange/17897-access-a-mysql-database?s_tid=srchtitle
• Access also open Power Point Tables, an export to PPoint should work to Access https://uk.mathworks.com/matlabcentral/fileexchange/40277-exporttopptx?s_tid=srchtitle
• and the commandshttps://uk.mathworks.com/matlabcentral/fileexchange/15834-export-data-to-sas?s_tid=srchtitle

if you find these lines useful would you please mark my answer as Accepted Answer?

To any other reader, please if you find this answer of any help, please click on the thumbs-up vote link,

thanks in advance for time and attention

John BG

<mailto:[e-mail] [e-mail]>

[L]

Thank you,

I'll give them a look and if they do help, I'll accept.

-L

[John BG]

L

if you store the table in CSV format then both Excel and Access will open it and there you can display and process.

John BG