How can I create a new table in a Microsoft Access database and import data to it using Matlab?
12 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
L
el 29 de Nov. de 2016
Comentada: Guillaume
el 12 de Dic. de 2016
Hello,
I am trying to export test data into an existing Access database, but I am having difficulty finding the correct commands to achieve this goal. I've been searching up and down the internet to try and find an example to go off of, but a lot of the info refers to a table already existing and simply inserting data into it.
The data I have for this instance is a 14x13 cell where the first column are names and the first row contains characteristics, everything else is the data. Another note, I expect different tests to very in their size so I'm trying to avoid anything hard coded where it can't pull NxM types of data, if that makes sense. I want to store everything in the database and I want it to look just as it does (format and what not) when I look at it in Matlab.
So far I know how to make the connection to the database, I just need someone to give me a hint or a tip to export this data. Let me know if you need more information. I'll also continue to work on it and if I figure anything out I'll keep this post updated.
Thank you in advance,
L
0 comentarios
Respuesta aceptada
Guillaume
el 6 de Dic. de 2016
Access SQL syntax documentation is arguably one of the worst documentation produced by Microsoft (who can also produce some of the best documentation). It's full of errors and missing information.
Nonetheless, a few years back I managed to piece together how to create databases, tables, parameter queries, etc. in access. Note that I don't use / have the database toolbox. I communicate directly with access using ADO. Nonetheless, the following function (which I wrote a few years back) should show you the SQL syntax for a CREATE TABLE query. It's been tailored to my needs so does not support all types of columns / constraints
function CreateTable(this, tabledefinition)
%CREATETABLE Create an access 2010 table.
% CreateTable(tabledefinition)
% tabledefinition: {tablename columndefinition+} (cell array).
% tablename: The name of the table.
% columndefinition: {columnname columnntype constraint*} (cell array)
% columnname: The name of the column (string).
% columntype: 'typename[:typesize]' (string).
% typename: 'auto' | 'int' | 'double' | 'longtext' | 'text'
% typesize: Only applies to 'text', size of column (default is access default).
% constraint: 'constraintname[:constraintoptions]'
% constraintname: 'primary' | 'required' | 'reference'
% constraintoption: Required for 'foreignkey', Table[(field)]
sql = [];
tablename = tabledefinition{1};
for iter = tabledefinition(2:end)
column = iter{1}; %iteration over cell arrays always return a single-cell array holding the content of the iterator
columnname = column{1};
[columntype, columnsize] = SplitAtColon(column{2});
switch columntype
case 'auto'
sqltype = 'AUTOINCREMENT';
case 'int'
sqltype = 'INTEGER';
case 'double'
sqltype = 'DOUBLE';
case 'longtext'
sqltype = 'TEXT';
case 'text'
if isempty(columnsize)
sqltype = 'CHAR';
else
sqltype = ['CHAR(' columnsize ')'];
end
otherwise
error('unknown column type: %s', column{2});
end
columnsql = ['[' columnname '] ' sqltype];
for constraint = column(3:end)
[constraintname, constraintoption] = SplitAtColon(constraint{1});
switch constraintname
case 'primary'
columnsql = [columnsql ' PRIMARY KEY']; %#ok<AGROW>
case 'required'
columnsql = [columnsql ' NOT NULL']; %#ok<AGROW>
case 'reference'
columnsql = [columnsql ' REFERENCES [' constraintoption '] ON UPDATE CASCADE ON DELETE CASCADE']; %#ok<AGROW>
case 'indexed'
% columnsql = [columnsql ' INDEXED']; %#ok<AGROW> %is this legal access ddl? No it isn't
warning('indexed constraint not implemented'); %#ok<WNTAG> warning is only temporary anyway.
otherwise
error('invalid constraint: %s', constraint{1});
end
end
if isempty(sql)
sql = columnsql;
else
sql = [sql ', ' columnsql]; %#ok<AGROW>
end
end
sql = ['CREATE TABLE [' tablename '] (' sql ')'];
this.Connection.Execute(sql);
end
5 comentarios
Guillaume
el 12 de Dic. de 2016
As far as I know, what the database toolbox calls a cursor is what Access (VBA) calls a recordset. You'll always get one when you execute a query as it allows you to navigate the rows returned by the query. For a CREATE TABLE query, it's probably not useful as you don't get any row but for a SELECT query, it's critical.
It does sound like you need to learn a bit more how to use a database. Even if you create the table directly in access, you still need to know how to use cursors to append or query data.
Once you've build up the query SQL, you indeed need to execute it. That's the purpose of your exec(conn, sql) and my this.Connection.Execute(sql).
Más respuestas (1)
Sid Jhaveri
el 6 de Dic. de 2016
1) You can refer to the following documentation link for general information on how Database Toolbox works: https://www.mathworks.com/help/database/getting-started-with-database-toolbox.html
2) As far as creating a new table goes, you will have go the SQL syntax for creating a table. Once you have the SQL statement ready, you can use it like " curs = exec(conn,sqlquery) " where "sqlquery" is the SQL statement for creating table.
0 comentarios
Ver también
Categorías
Más información sobre Database Toolbox en Help Center y File Exchange.
Productos
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!