cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
klm123
Level II

How do I skip a particular worksheet while importing an Excel file?

Hello JMP Forum,
I'm using JMP 16. I'm looking for a way to import an Excel spreadsheet with several worksheets, but want to skip importing a particular worksheet named 'Summary'. Is there a way to write a script for this?

 

Please note: I create a report using such an Excel file periodically. And the number and names of the worksheets (other than 'Summary') vary every time.

 

Thanks

 

Thanks,
Kishore
1 ACCEPTED SOLUTION

Accepted Solutions
Thierry_S
Super User

Re: How do I skip a particular worksheet while importing an Excel file?

Hi,

After reviewing your script, I think there is a much more direct approach to reach the same solution:

Names Default to Here (1);

xlfid = Pick File(
	"Select the Excel File",        //prompt
	"$Documents",          //starting path
	{"Excel Files|xls;xlsx", "Excel Files|*"},   //file types
	1,
	0,
	""
	);
	
dt = Open(xlfid,
	//Worksheets(),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 1 ), //CONCATEMATE IS ON
	Create Concatenation Column( 1 ), //CREATE CONCAT COLUMN IS ON
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 7 ),  // First seven rows are headers
		Headers Start on Row( 1 ), 
		Data Starts on Row( 13 ),  // Data starts on row 13
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 1 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "_" )
	)
);

dt << Select where (contains(:Source Table, "Summary"));

dt << Delete Rows;

Sorry for the convoluted route I took to get to this simpler approach.

Best,

TS

Thierry R. Sornasse

View solution in original post

7 REPLIES 7
Thierry_S
Super User

Re: How do I skip a particular worksheet while importing an Excel file?

Hi,

A simple "dirty" way to handle this would be to open all worksheets, record the names of all open tables [ Get Data Table List()], and discard the table with name == "Summary", [Close (dt, NoSave)].

Let us know if you need an actual example of a script to assemble these commands?

Best,

TS

 

Thierry R. Sornasse
klm123
Level II

Re: How do I skip a particular worksheet while importing an Excel file?

Thanks Thierry!

 

I'm not much familiar with using List(), so an example script would be helpful

 

My plan is to concatenate the rest of the worksheets (excluding 'Summary').

 

Regards

Thanks,
Kishore
Thierry_S
Super User

Re: How do I skip a particular worksheet while importing an Excel file?

Here is a short script to discard any Data Table with a name containing "Summary:

 

Names Default to Here (1);

tb_list = Get Data Table List ();

for (i = 1, i <=  N Items (tb_list), i++,

	tb_name = tb_list [i] << Get Name;
	
	if (contains(tb_name, "Summary"), Close (tb_list[i],NoSave))


);

You can then rerun the Get Data Table List () command and concatenate the remaining tables into a new data table.

Best,

TS

 

Thierry R. Sornasse
klm123
Level II

Re: How do I skip a particular worksheet while importing an Excel file?

Hi Thierry,

This is working -- discarding the "Summary" datatable. Thanks!

 

However, I'm making some mistake in using the for() loop for concatenating. Sharing my code below. Would appreciate your guidance here

 

Names Default to Here (1);

xlfid = Pick File(
	"Select the Excel File",        //prompt
	"$Documents",          //starting path
	{"Excel Files|xls;xlsx", "Excel Files|*"},   //file types
	1,
	0,
	""
	);
	
dt = Open(xlfid,
	//Worksheets(),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 7 ),  // First seven rows are headers
		Headers Start on Row( 1 ),
		Data Starts on Row( 13 ),  // Data starts on row 13
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 1 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "_" )
	)
);

// Identifying and closing 'Summary' datatable tb_list = Get Data Table List (); for (i = 1, i <= N Items (tb_list), i++, tb_name = tb_list [i] << Get Name; if (contains(tb_name, "Summary"), Close (tb_list[i],NoSave)) );
// (1) Concatenating in to single datatable 'my_dt'.
// (2) Closing individual datatables.
tb_list = Get Data Table List (); for (i = 1, i <= N Items (tb_list), i++, my_dt = Data Table( tb_list [1] ) << Concatenate( Data Table( tb_list [i+1] ), Create source column,
Close (tb_list[i],NoSave) ) );
Thanks,
Kishore
Thierry_S
Super User

Re: How do I skip a particular worksheet while importing an Excel file?

Hi,

Try replacing the last part of your script with the following snippet:

tb_list = Get Data Table List ();

for (i = 1, i <=  N Items (tb_list), i++,
	
	if(i == 1, 	my_dt = tb_list [i],
				concatenate(Data Table (my_dt), Data Table (tb_list[i]), "Append to first table")	
	)
	
);

Of note, the script snippet above works well as is, but it does not work correctly if you add the switch "Create source column" because each round of concatenation creates new columns resulting in a jumble of table identifier

.Best,

TS

Thierry R. Sornasse
Thierry_S
Super User

Re: How do I skip a particular worksheet while importing an Excel file?

Hi,

After reviewing your script, I think there is a much more direct approach to reach the same solution:

Names Default to Here (1);

xlfid = Pick File(
	"Select the Excel File",        //prompt
	"$Documents",          //starting path
	{"Excel Files|xls;xlsx", "Excel Files|*"},   //file types
	1,
	0,
	""
	);
	
dt = Open(xlfid,
	//Worksheets(),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 1 ), //CONCATEMATE IS ON
	Create Concatenation Column( 1 ), //CREATE CONCAT COLUMN IS ON
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 7 ),  // First seven rows are headers
		Headers Start on Row( 1 ), 
		Data Starts on Row( 13 ),  // Data starts on row 13
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 1 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "_" )
	)
);

dt << Select where (contains(:Source Table, "Summary"));

dt << Delete Rows;

Sorry for the convoluted route I took to get to this simpler approach.

Best,

TS

Thierry R. Sornasse
klm123
Level II

Re: How do I skip a particular worksheet while importing an Excel file?

Thank you, Thierry! This is great!

Thanks,
Kishore