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

accesscommandscreatedatabaseMATLABoptimizationsqltable

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

Best Answer

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