cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
ShannonK
Level II

Export data table to Excel using scripting

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 ACCEPTED SOLUTIONS

Accepted Solutions
Phil_Brown
Super User (Alumni)

Re: Export data table to Excel using scripting

@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?

PDB

View solution in original post

Re: Export data table to Excel using scripting

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

View solution in original post

8 REPLIES 8
Phil_Brown
Super User (Alumni)

Re: Export data table to Excel using scripting

@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

PDB
ShannonK
Level II

Re: Export data table to Excel using scripting

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
);

Phil_Brown
Super User (Alumni)

Re: Export data table to Excel using scripting

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 );
PDB
ShannonK
Level II

Re: Export data table to Excel using scripting

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

Phil_Brown
Super User (Alumni)

Re: Export data table to Excel using scripting

@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?

PDB

Re: Export data table to Excel using scripting

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

karaaar
Level I

Re: Export data table to Excel using scripting

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

Re: Export data table to Excel using scripting

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