Good evening, I am trying sort through worksheets in one excel file. Right now I can open all the worksheets using this:
Should I save to .jmp datatables and then have a for loop sort through those?
files = Files In Directory( "C:\Users\Desktop\JMP" );
For( i = 1, i <= N Items( files ), i++,
If( Ends With( files[i], ".xlsx" ),
dt = open( files[i]), dt << save(filename)
If all you want to do is sort the worksheets in an excel workbook, you can do the following:
Clear Log(); Clear Globals(); Close All(DataTables,"No save"); FilePath = "C:\Copy of w2089-data-analysis-1a (002).xlsx"; SheetsList = Get Excel Worksheets(FilePath); Show(SheetsList); SortedList = Sort List(SheetsList); Show(SortedList);
I tried this is a JMP script but it didn't show anything? To call each of the elements to perform operations on the data table would I need a for loop?
Yes. you would use a for loop and do something like this :
Clear Log(); Clear Globals(); Close All(DataTables,"No save"); FilePath = "C:\Copy of w2089-data-analysis-1a (002).xlsx"; SheetsList = Get Excel Worksheets(FilePath); Show(SheetsList); SortedList = Sort List(SheetsList); Show(SortedList); for(i = 1, i <= N Items(SortedList), i++, // Individual scripts );
Alternatively, you can always open all sheets at once and then perform the actions on the sheets. You can use the UI to import the file and see how JMP is handling the import code. You can see something like this , when you use the Excel Import Wizard :
File -> Open ->
So normally I use dt = Current Data Table(); so how would I get the dt when I am looping through. Sorry for all the trivial questions, I am a very new user
When you have multiple data tables open, you can do the following:
openDTs = List(); For( i = 1, i <= N Table(), i++, Insert Into( openDTs, Data Table( i ) ); );
Don't be sorry that you have to ask questions. The community is a great resource with lot of people who are patient and contribute a lot. I learned a lot from the community and asking questions here and I am happy to do a small bit in helping new users get familiar with JMP.
However, I would simultaneously encourage you to familiarize yourself with some great resources that you already have at your disposal.
"Scripting Index" - great resource to learn and understand about functions and it has examples
"Sample Data" - sample data sets to test your code and try different things on
"Books" - has a lot of unlimited resources which goes into a lot of little details and examples
Good Luck. And you have the JMP community too to help anytime