Subscribe Bookmark RSS Feed

Export data table to Excel using scripting

ShannonK

Occasional Contributor

Joined:

Nov 3, 2016

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

Joined:

Mar 20, 2012

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?

PDB
briancorcoran

Joined:

Jun 23, 2011

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

6 REPLIES
Phil_Brown

Super User

Joined:

Mar 20, 2012

@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

Occasional Contributor

Joined:

Nov 3, 2016

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

Joined:

Mar 20, 2012

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

Occasional Contributor

Joined:

Nov 3, 2016

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

Joined:

Mar 20, 2012

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?

PDB
briancorcoran

Joined:

Jun 23, 2011

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