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

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

klm123 — Fri, 09 Jun 2023 18:10:40 GMT

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

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

Thierry_S — Mon, 14 Feb 2022 20:26:33 GMT

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,

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

klm123 — Mon, 14 Feb 2022 20:39:06 GMT

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

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

Thierry_S — Mon, 14 Feb 2022 21:12:57 GMT

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,

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

klm123 — Tue, 15 Feb 2022 00:32:02 GMT

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

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

Thierry_S — Tue, 15 Feb 2022 06:10:08 GMT

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,

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

Thierry_S — Tue, 15 Feb 2022 06:30:07 GMT

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,

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

klm123 — Tue, 15 Feb 2022 19:57:32 GMT

Thank you, Thierry! This is great!