topic Re: SQLQuery in Discussions

SQLQuery

Françoise — Fri, 18 May 2018 08:56:35 GMT

Hi,

I've made a sqlquery.jmp from an excel file (xlsm).

When I execute for the first time: no pb. the jmp file is created.

but when I try to update the jmp file by "update from Database" (I use JMP14), the file is not updated.

The excel file is write protected. In excel, the file requests a password for writing / reading. I can only open it as read-only.

is it the explanation?

What should I check ?

thanks for your help.

best regards

Re: SQLQuery

gzmorgan0 — Sat, 19 May 2018 21:55:48 GMT

Francoise,

Attached is an Excel workbook (.xlsx), with 3 sheets, Basketball, Football and an empty Sheet 3. Basketball and Football are are sheet protected with the password 1234. Run lines 1-13 (//Run to Here... ) of the embedded script, then open and change or add something to the Excel file sheet Football, then run line 15, the last JSL statement. It works, but I get a prompt for which sheet. If I select Football my JMP table is updated.

It is likely there is syntax to specify which worksheet in the connect string, but I do not know it. If you have just one sheet, then this should work. Note I am using Excel as a database and not using Open (or Import Excel).

JMP 13 had documentation that Open/Import supported passwords for

"Import password-protected Excel 2007 .xls files by including the Password argument"

However, when writing our book (written with JMP13) early Jan 2017, we reported it did not work. There is no message of support in JMP 14 documentation.

Note my system name (DSN=Excel Files) might not be the name you have set up, and you need to change the path to match the location where you saved this file. Hopefully, a JMP person or guru who has faced this issue can help with the issue of specifying the sheet in the connect string.

//---create a string with multiple commands
xls_connect_str = "DSN=Excel Files;
  DBQ=Basketball Football Sample Data.xlsx;
  DefaultDir=c:\temp\;pwd=1234";

//--select some fields/parameters/columns  

//--Football is one of the worksheet names
//--each worksheet is treated as a database table
football_dt1=Open Database( xls_connect_str,
  "SELECT * FROM `Football$`",
  "Football");  
//--- Run to Here and look at results 

football_dt1 << Update from Database(xls_connect_str);

Re: SQLQuery

gzmorgan0 — Mon, 04 Jun 2018 17:18:22 GMT

This is a late update. The reason for the prompt is that the Update From Database() needs both the connect string and the query.

This is just an FYI, to correct the prompting issue in my last post for this discussion.

Names Default to Here(1);
//---create a string with multiple commands
xls_connect_str = "DSN=Excel Files;
  DBQ=Basketball Football Sample Data.xlsx;
  DefaultDir=c:\temp\;pwd=1234";

//--select some fields/parameters/columns  

//--Football is one of the worksheet names
//--each worksheet is treated as a database table
football_dt1=Open Database( xls_connect_str,
  "SELECT * FROM `Football$` ",
  "Football");  
//--- Run to Here and look at results 

nr= nrow(football_dt1);
football_dt1 << delete rows((nr-10)::nr);

football_dt1 << Update from Database(xls_connect_str , "SELECT * FROM `Football$` ");