Insert Data Into New Table example ODBC to Microsoft Excel file

• Using MATLAB R2019a
• I am trying to Insert Data Into New Table example ODBC to Microsoft Excel file
• ODBC seems to be properly connected as I am able to sqlread() and update() existing data in tables. Also can read and import through Database Explorer APP Toolbox
• I cannot either append or insert data to the database
• I am following the sqlwrite() documentation and InsertDataIntoNewTableExample.m example, only change:

datasource = 'myDatabaseName'; %line 9

• Error received:

Error using database.odbc.connection/sqlwrite (line 102)

ODBC JDBC/ODBC Error: ODBC Driver Error: [Microsoft][ODBC Excel Driver] Syntax error in CREATE TABLE statement..

Error in InsertDataIntoNewTableExample (line 29)

sqlwrite(conn,tablename,data)

Any advise onto how to diagnose the issue is appreciated as it seems to be related to the sqlquery code which is not inside MATLAB.

Thanks in advance, best regards.

Answer 1

[Monisha Nalluru]

The reason behind the error may be due to database is read-only and Schema name is case sensitive in MATLAB.

Also refer the following links:

https://www.mathworks.com/matlabcentral/answers/421192-error-object-already-exists-in-sqlwrite?s_tid=answers_rc1-2_p2_BOTH

https://www.mathworks.com/matlabcentral/answers/400868-matlab-database-toolbox-writting-error

[Carlos Amor-Gonzalez]

Hi Monisha,

Thank you very much for your answer.

My database does not have Schema names defined as far as I know. In fact I cannot see any when I look it up with the Database Explorer App

I do not believe the database is in read-only as I have been able to write on it through the following SQL query:

query = "CREATE TABLE OFFICES (OfficeID TEXT(4),    OfficeName TEXT(10))";
execute(conn,query);                        

When I have tried to input data using the sqlwrite function, I saw that the CREATE TABLE sql query was something like this:

query3 = "CREATE TABLE toytable (productNumber numeric(38,16), stockNumber numeric(38,16), supplierNumber numeric(38,16), unitCost numeric(38,16), productDescription varchar(112) )";

I have been testing with different variable types and it looks like the type numeric(p,d) and others are not accepted by the EXCEL driver, I have read the acceptance variables in the following reference, and those do work.

*Extracted from: https://www.w3schools.com/sql/sql_datatypes.asp

To simplify my problem to the maximum I have tried to input a data table which only had one column with text, and that would not create and error when creating the table (sqlwrite function line 100) as in the example before, but I would get a different error in sqlwrite line 156:

ODBC JDBC/ODBC Error: ODBC Driver Error:

[Microsoft][ODBC Excel Driver]COUNT field incorrect .

Any thoughts?

P.S. I have a professional license of my company, is there a way for me to get support for this issue via phone?