[SOLVED] Outputting JMP tables to Excel Worksheets

[#1]

Say that I have 4 JMP Tables dt1, dt2, dt3 and dt4. I would like each table to be output to its own worksheet (tab) in an new Excel file. Can someone please point me in the right direction.

Thanks,
Bill

Message was edited by: Bill E

[#2]

Should have mentioned that I am using JMP8 and want to do this with a script.

[#3]

Don't know if it's possible to do right out of the box. I'm interested if someone's figured it out.

If JMP can't do it natively you can reverse engineer it using the Excel XML 2003 format. We do this routinely with PL/SQL. What you do is create a sample 4-tab Excel spreadsheet in Excel, and then save it as an XML file using the following format: XML Spreadsheet 2003 (*.xml). Don't use the XML Data (*.xml) format as that requires XML mappings.

Next you use JSL to output an ASCII file that has the exact format of the file you just created. Of course the cell contents will be different, but the result will be an XML file you can double-click to open in Excel, that contains the four tabs you require. It's somewhat complicated the first time you do it, but once you get the hang of it it works well.

One thing to watch out for - in JMP 8 and 9 when you save a dataset as an Excel file (*.xls, *.xlsb), JMP will truncate cells containing more than 256 characters. This is because JMP is using ODBC to do the export, and it's a limitation of ODBC. It would be really great if JMP could overcome this limitation.

Regards,
Peter

[#4]

Thanks Peter. I think I have discovered a way to do this natively. After hours of googling various permutations of jmp, excel, odbc, etc, I stumbled across the following ap note:

http://www.jmp.com/support/notes/17/582.html

I ran the following code snippet:

myPath = "C:\Pareto History\";

myName = "CGTRUNK1";
dt = Data Table(myName);
fn = myPath || myName || ".xls";
dt << save(fn);

This created a file called CGTRUNK1.xls with 1 worksheet called CGTRUNK1 and the log yielded the following info:

DSN=Excel Files;DBQ=C:\Pareto History\CGTRUNK1.xls;DefaultDir=C:\Pareto History\;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

As the ap note suggests, I wrapped that whole string in quotes and inserted it into the save database command followed by the name of my desired worksheet as follows:

dt << Save Database("DSN=Excel Files;DBQ=C:\Pareto History\CGTRUNK1.xls;DefaultDir=C:\Pareto History\;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;","foo");

When I ran this line, it created a second worksheet named foo in the original file. I haven't been this proud of myself since I learned how to spell c++

Regards,
Bill

[#5]

Bill,

Glad that worked for you. I didn't know one could do that!

Has anyone solved the problem of the ODBC driver truncating table cells at 255 characters when saving a dataset as an Excel spreadsheet? This "feature" is there in JMP 8, 9 for .XLS and .XLSB files.

Thanks,
Peter