Export data table to Excel using scripting

[#1]

Hi, I'm trying to learn how to export my data table to excel using scripting and some automation.

I am using save("") to open a "Save JMP File As" window prompting me to save the file. I'd like to have the "Save as type" set to *.xls instead of *.jmp so I don't have to change it every time. I'd also like to set the default folder that it chooses for saving the file to. Can any of this be done in the scipting commands? I am an new user and am looking for a simple solution. Thanks for any tips! ~Shannon

[#2]

@ShannonK

 Try this, replacing "<default folder path>" with the folder path you would like to initially appear::

SaveFileWindow = Pick File(
	"Save File",
	"<default folder path>",
	{"Excel Files|xlsx;xls", "JMP Datatables|jmp", "Text Files|txt;csv;dat", "All Files|*"},
	1,
	saveFlag = 1
);

You can read more about the Pick File function at: http://www.jmp.com/support/help/13/Select_a_Directory_or_File.shtml

[#3]

This is great, thank you.

I am still having 1 issue though. How do I tell it which file I am saving? I am typing in a filename to save it as, but nothing appears in my folder. I wonder if that is because it doesn't have anything to save?

I am creating a datatable and then running the SafeFileWindow code that you suggested. I apologize if I am missing something simple. I am new to this! (Also, for some reason, the save file window is appearing before the datatable appears. In other words, the save file window isn't the active window after running this code.)

Thanks for your help.

Here is a portion of my code:

filtereddata = sorteddt << Subset(
    (Selected Rows),
    Columns(:YEARX, :PSU, :CASEID, :RATWGT, :VEHNO, :OCCNO, :DVTOTAL, :ROLLOVER, :GAD1, :PDOF1, :DOF1, :BODYTYPE, :AGE, :ROLE, :SEATPOS),    
    Output Table Name(filename)
);

SaveFileWindow = Pick File(
    "Save Filtered Data",
    path,
    {"Excel Files|xlsx;xls", "JMP Datatables|jmp", "Text Files|txt;csv;dat", "All Files|*"},
    1,
    saveFlag = 1
);

[#4]

Hi @ShannonK,

So the Pick file Dialog is only responsible for securing the Path and Filename you would like. So in your example:

Let's say: 

path = "$DOCUMENTS"    //  this is shorthand for your "My Documents" folder

filtereddata = sorteddt << Subset(
    (Selected Rows),
    Columns(:YEARX, :PSU, :CASEID, :RATWGT, :VEHNO, :OCCNO, :DVTOTAL, :ROLLOVER, :GAD1, :PDOF1, :DOF1, :BODYTYPE, :AGE, :ROLE, :SEATPOS),    
    Output Table Name(filename)
);

SaveFilePath = Pick File(
    "Save Filtered Data",
    path,
    {"Excel Files|xlsx;xls", "JMP Datatables|jmp", "Text Files|txt;csv;dat", "All Files|*"},
    1,
    saveFlag = 1
);

 After you've selected the folder and chosen a name for the file, the variable SaveFilePath will contain this information as a string. 

You would then do the following:

filtereddata << Save( SaveFilePath );

Add Tags

[#5]

Thank you for your help. I included the save code that you suggested but I get an error: "Unable to save file. Unknown data target."

I've been trying unsuccessfully to troubleshoot the error. When I check the output for SaveFilePath, it looks like this: "/C:/Users/jmp/name". My best guess at the problem is that the save function isn't adding the ".xlsx" to the file name.

(I am using Windows but "/C: ..." doesn't seem to be an issue.)

Have I correctly identified the error? Any thoughts on how I can fix the problem?

Thank you!

Shannon

[#6 · Accepted solution]

@ShannonK,

Yes, you're correct. If you PICK an already existing file then the extension is included. However, if it's a NEW file, then you'll have to include the extension.

@Craige_Hales may be able to comment further?

[#7 · Accepted solution]

Something like this would strip a .jmp extension and append .xlsx.  If you have already picked a .xlsx file in the filter, it will do nothing and overwrite the file.  Please be aware that JMP is good at saving XLSX, but really doesn't do XLS well.

dt = current data table();
filepath = pickFile("Pick a file to save", "c:\", {"Excel Files|xlsx"}, 1, 1);
pos = Contains(filepath, ".xlsx");
if (pos == 0, filepath = Left(filepath, pos-1); filepath = filepath || ".xlsx");
dt << Save As(filepath, Excel File);

Brian Corcoran

JMP Development

[#8]

Hello,

I am having an issue with many of excel file where were created long back with huge data I assume those were created earlier ver. 2007 or 2003. Strange thing is that the file is not workin in 95% machines it crashes by adding or deleting colum, with error 1000 and Execel.exe.

I tried with 2 ways which worked fine but this not the solution that I need. The files contain freezpan and when I unfreez that works fine or I copy the sheet to a new sheet then it works fine even with freezpan is enabled.

How to fix this issue in 100s of files? I am stuck with this issue.

Thanks in well Adv.

Regards

Faisal

[#9]

That is awesome! But how to ask the user if replace an existing file or not?