Combine data from different workbooks in stack format

[#1]

Hi JMP experts, 

I am looking for an option to club the data from different excel workbooks into one JMP file by stacking them under one header.

Please suggest best option to import the data from multiple workbooks [Please remember that it is not worksheet]

By the way, I am using JMP 15 pro, on Mac OS.

Thanks in advance. 

[#2]

Below is an example that should give you an idea of how to approach this issue.  You will probably need to make changes to it based upon the details of what your actual structures are

Names Default To Here( 1 );

// get the workbooks to be processed
theFiles = Files in Directory(<your path>, recursive(1) );

// reduce the list to only .xlsx entries
For( i = N Items( theFiles ), i >= 1, i--,
	If( Word( -1, theFiles[i], "." ) != "xlsx",
		Remove From( theFiles, i, 1 )
	)
);

// create the base data table that all other tables will be concatenated to
dtFinal = New Table( "All Data" );

// loop through all .xlsx files and concatenate them to dtFinal
// the Concatenate Worksheets(1) will result in a single data table
// for each workbook read in and that single data table will be concatenated
// to the dtFinal
For( i = 1, i <= N Items( theFiles ), i++,
	dt = Open(
		theFiles[i],
		Use for all sheets( 1 ),
		Concatenate Worksheets( 1 ),
		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 ),
			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( "-" )
		)
	);

// concatenate the new table with the dtFinal table
	dtFinal << concatenate( dt, append to first table( 1 ) );

// delete the no longer needed data table dt
	Close( dt, nosave );
);

[#3]

Thanks for the quick response, txnelson.

But honestly, I am not good at editing the .jsl scripts. I don't know which are all the steps that I have to edit in the above shared jsl script.

Right now, I have "n" no. of similar kind of excel workbooks in one single folder. Each excel workbook has multiple sheets, but my focus is to club the information from the worksheet named "Data" from all the workbooks.

assuming this scenario, can you please guide me further to edit the given script.?

Also, if you have any idea on "Multiple file import" wizard, please suggest it for the given scenario. I can able to club the data from multiple workbooks, if the individual workbooks have only one worksheet. But, I don't know, what is the right step to follow for the scenario I have given here [ie. if each workbooks have multiple worksheets.]

[#4]

1. The script I provided is based upon a JSL list of workbooks that are to be read in.  That list is called theFiles.  In my script, it is created by reading all of the .xlsx files from a given directory/folder structure.  But you can change that out, and just manually create that list.

   theFiles = {"complete path to workbook 1", "complete path to workbook 2"
        "complete path to workbook 3" };

   A made up example of what the "complete path to workbook n" could be is:

   "C:\Excel Data\2020\January\Department21 Monthly.xlsx"

    So you can just remove the code in lines 3-11 and replace it with your new definition of "theFiles" list, and the script should give you the results you are asking for.
2. The script already will read in all worksheets from any workbook it opens and will place them into a single JMP data table, which the script will then add to the overall final data table the script is creating.
3. You need to work on your JMP JSL skills, so you can take responsibility for the usage and maintenance of the script.  Or if that is not within the definition of your job, then you need to find the budget to hire someone to support the code.  Using scripts that are just passed on to you is a very questioning thing to do.  JMP provides free of charge documentation, examples and tutorials  on learning JSL.  In addition, they provide "for cost" training classes on JSL.  The JMP Community Discussion participants are there to aid in problem solving, but it would be the wrong decision to assume they will be able to write the finished scripts you may need.
4. Below is a modification of my original script, placing into it, the example code of manually inputting the paths to the workbooks you want to read in

Names Default To Here( 1 );

theFiles = {"complete path to workbook 1", "complete path to workbook 2"
     "complete path to workbook 3" };

// create the base data table that all other tables will be concatenated to
dtFinal = New Table( "All Data" );

// loop through all .xlsx files and concatenate them to dtFinal
// the Concatenate Worksheets(1) will result in a single data table
// for each workbook read in and that single data table will be concatenated
// to the dtFinal
For( i = 1, i <= N Items( theFiles ), i++,
	dt = Open(
		theFiles[i],
		Use for all sheets( 1 ),
		Concatenate Worksheets( 1 ),
		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 ),
			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( "-" )
		)
	);

// concatenate the new table with the dtFinal table
	dtFinal << concatenate( dt, append to first table( 1 ) );

// delete the no longer needed data table dt
	Close( dt, nosave );
);

[#5]

I tried this script, and got the following error. Not sure what happened, wondering if you could help. Thanks.

[#6]

It looks like the definition for the "theFiles" list is incorrect or non existant.  What is the value of theFiles and what is the value of the variable i?