topic Re: Multi File Import Certain Tabs and Columns in Discussions

Multi File Import Certain Tabs and Columns

patriciafm811 — Wed, 11 Oct 2023 13:25:48 GMT

I am trying to import multiple XLSX files (which I know how to do), but I only need certain tabs with certain columns. I don't need everything being imported. Is there a way to do this?

Using JMP 17. Can recreate with steps or JSL code.

Re: Multi File Import Certain Tabs and Columns

txnelson — Wed, 11 Oct 2023 13:51:33 GMT

The settings for inputting an .xlsx file has the ability to specify the specific worksheets to be input. It also has the ability to specify the starting column and ending column

The Scripting Index has an entry that shows and example that has the input options displayed.

Re: Multi File Import Certain Tabs and Columns

patriciafm811 — Wed, 11 Oct 2023 14:04:20 GMT

Thank you. This works if I am doing each file one by one, but not multiple files at once. Do you know how to do this for multiple files at once?

Re: Multi File Import Certain Tabs and Columns

mmarchandTSI — Wed, 11 Oct 2023 14:32:33 GMT

As long as each xlsx is set up the same way, just iterate over a list of the files:

FPlist = {"$DESKTOP/temp.xlsx", "$DESKTOP/temp1.xlsx"};
For Each( {val}, FPlist,
	Open(
		val,
		Worksheets( {"Sheet1", "Sheet3"} ),
		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( 1 ),
			Data Ends on Row( 0 ),
			Data Ends on Column( 0 ),
			Replicated Spanned Rows( 1 ),
			Suppress Hidden Rows( 1 ),
			Suppress Hidden Columns( 1 ),
			Treat as Hierarchy( 0 ),
			Multiple Series Stack( 0 ),
			Import Cell Colors( 0 ),
			Column Separator String( "-" )
		)
	)
);

Re: Multi File Import Certain Tabs and Columns

patriciafm811 — Wed, 11 Oct 2023 16:47:17 GMT

I have been trying to get that script to work and I cannot make it happen.

Re: Multi File Import Certain Tabs and Columns

mmarchandTSI — Wed, 11 Oct 2023 17:22:08 GMT

Could be the worksheet settings won't work with your specific files. This is how I got this script. Open one file using the Excel wizard first.

Choose the worksheets you want and make sure the data preview looks right.

After import, open the "Source" table script from one of the newly-created tables and copy it.

Adapt that script for the For Each() function by replacing the absolute path with the val variable and inserting the list of desired worksheets into the proper parentheses.

I hope that all makes sense. If you still have issues with it, please post the error message.

Re: Multi File Import Certain Tabs and Columns

patriciafm811 — Wed, 11 Oct 2023 18:18:19 GMT

Ok. That got me much closer! Thank you!