Need help importing excel, specific worksheet with workbook title for concatenation to existing table

[#1]

I have a slew of excel files from which I transfer data into JMP for analysis. Up till recently, I have been doing this in a very medieval way where I opened the excel files manually, and copied over the relevant information directly into the JMP tables. While this is tenable, I do believe that the import function (along with concatenation to append to existing data tables) is a much better long term solution to improve efficacy.
My relevant information is in Sheet 4 of each workbook, and is labelled "cycle". I need to import anywhere between 4 and 20 such workbooks at the time, with which to update my JMP tables. Each workbook has a string title, usually 8 characters long. This is a unique identifier to the object being tested.

My questions are these: is it possible for me replace the name of the generated table upon import (for example, right now if I import 8 files, the tables generated in JMP are "cycle" "cycle 2", "cycle 3" etc.) to be the titles of the original excel workbooks (becoming in this example, "workbook 1", "workbook 2", "workbook 3" etc.)?

My next question is regarding concatenation. Is it possible for me to write a script that could analyse a specific cell (e.g., the one generated by your script, which copies the title of the workbook into a specific column on import), and then use the value within that cell to append the information in the remaining columns to an existing JMP table?

To clarify, let us imagine that I am testing an object, in this case labelled "A71320". I have a JMP data table also called "A71320", which is a compilation of all my relevant data for this test object. Each new test generates an excel file, also labelled "A71320". I would like to import this file using your script. If I understand correctly, it will then copy all the data from sheet 4, "cycle", create a new JMP table, labelled "cycle", and then within this new table, create a new column with 1 populated cell, "A71320". I would then like to run a script which will see this cell "A71320", recognise an existing table within JMP called "A71320", and append all the information except for the titular cell, from "cycle" to "A71320". Is this possible?

My most sincere thanks for taking the time to read this question.

Antelope

[#2]

This does not seem to be a difficult task....however, the specific details are too ambiguous to attempt to write a script to fulfill your needs.  Samples of your input excel workbooks and final data tables would really be helpful in attempting to guide you though the developtment of a script.

[#3]

Hi @txnelson ,

I hear you! Please see the attached. As described above, I generate "new" A71322.xls files frequently. Each instance of the file is unique (I have a file system structure for storing/differentiating them) and each corresponds to a "Run" as described in the .jmp table. I usually open the excel file manually, then copy all the data underneath the column headers in the "cycle" worksheet, then paste them in the .jmp table underneath the last entry in the "Cycle ID" column. The formulas for "Total Cycle Number" and "Run" generate correct entries in the data table.

I have so far been able to recreate the above by importing the excel books, cycle sheet only, and then using the base concatenate function. However, it is currently slow and the fact that only the sheet name is imported (meaning importing say, 4 workbooks leads to "cycle" "cycle 1" "cycle 2" and "cycle 3") sometimes confusing. It is faster for me to continue manually as I have done.

However, my data sets are continually growing and therefore I am looking for a scripting solution. I carry out tests for multiple batteries (i.e., A71321, A71311 etc.), and am confident I'll be able to adapt your provided solution.

One more thing to note, the title of the cell can be found in sheet "test", cell I-6 if that is helpful (however, this is not as flexible as being able to import the title of the workbook in my scenario.).

Once again, your patience with reading my request and any forthcoming reply are both appreciated. If you need any more information I will be on hand to answer.

Regards,

Antelope

[#4 · Accepted solution]

Here is a script that should give you a good start on writing you own script to complete your task.  Please make sure you study the supplied script, so you understand how it works.

Names Default To Here( 1 );

// Set the object in question
object = "A71322";

// Get a list of input xls files to proces
// I am using the method where all files to be processed are moved into a single directory
dir = "C:\users\xxxx\input files\";

theFiles = Files In Directory( dir );

// Create the start of the final data table
dt = New Table( "Combined",
	New Column( "Run",
		formula(
			If(
				Row() == 1, 1,
				:Cycle ID == 1 & Lag( :Cycle ID ) != 1, Lag( :Run ) + 1,
				Lag( :Run )
			)
		)
	),
	New Column( "Total Cycle Number", formula( Row() ) )
);


// Loop through all of the file names and select only object in question files
For( i = 1, i <= N Items( theFiles ), i++,
	If( Contains( theFiles[i], object ) == 1,
		dtCycle = Open(
			Char( dir ) || theFiles[i],
			Worksheets( "cycle" ),
			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 ),
				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 = dt << concatenate( dtCycle, Append to first table( 1 ) );

// Clean up
		Close( dtCycle, nosave );
	)
);