How can I summarize multiple Excel Spreadsheets and output it into 1 table?

Hobbi — Sat, 10 Jun 2023 23:39:37 GMT

Hello everyone!

I look through a lot of data. Since all files are stored the same way I wanted to use this as a tool to help me find exactly what I'm looking for.

I am currently able to summarize a single excel spreadsheet (removing duplicates) with this script.

Clear Symbols();

names default to here(1);

path = Pick File ();

dt = Open(
	path,
	Worksheets( "Data" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 2 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 3 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
			
	)
	
);

dt << select duplicate rows(match(:Wafer));
dt << delete rows;

Attached is an example document (there are that many columns of data - I just deleted the rows) that this script is meant for.

My goal with this post is to hopefully find a way to open multiple spreadsheets at the same time --> have it remove duplicates --> and output all values into a single table. This would help me save a lot of time instead of opening each spreadsheet one at a time.

Thank you very much!

Re: How can I summarize multiple Excel Spreadsheets and output it into 1 table?

jthi — Tue, 02 Nov 2021 18:08:29 GMT

If you have all the spreadsheets in same folder you can try using Import Multiple Files which is found from Files menu. Then run it once manually to get the script from table script which will be something like this:

And if you need to you can update your script to use Pick Directory() instead of Pick File() and modify the JMP made script to work with the path you get.

topic Re: How can I summarize multiple Excel Spreadsheets and output it into 1 table? in Discussions

How can I summarize multiple Excel Spreadsheets and output it into 1 table?

Re: How can I summarize multiple Excel Spreadsheets and output it into 1 table?