BookmarkSubscribeRSS Feed
tylerram123

Occasional Contributor

Joined:

Dec 4, 2018

Sorting multiple worksheets in an excel spreadsheet

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)

);

);

 

5 REPLIES
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Re: Sorting multiple worksheets in an excel spreadsheet

@tylerram123,
        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); 
Best
Uday
tylerram123

Occasional Contributor

Joined:

Dec 4, 2018

Re: Sorting multiple worksheets in an excel spreadsheet

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?

uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Re: Sorting multiple worksheets in an excel spreadsheet

@tylerram123,  
 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 -> 

image.png

Best
Uday
tylerram123

Occasional Contributor

Joined:

Dec 4, 2018

Re: Sorting multiple worksheets in an excel spreadsheet

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

Highlighted
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Re: Sorting multiple worksheets in an excel spreadsheet

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

https://www.jmp.com/support/help/14/perform-actions-on-all-open-data-tables.shtml

 

     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. 

image.png
"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 

 

Best
Uday