How can I import excel sheets that will reference the Excel workbook name when they are imported to JMP

[#1]

I have a bunch of excel files, about 2 years worth, which are compiled monthly. I would like to import a sheet from each workbook into JMP. Unfortunately the date timestamp is only referenced in the title of each workbook and not anywhere on the sheets that I want to import. Is there a anyway to have the workbook title imported with the sheet?

Thanks

[#2]

The Open() function for opening Excel workbooks has an element called "Worksheets" that can be set within the open.  Below was taken from the "Source" entry for a worksheet input into JMP.  It shows the name of the worksheet to read in

     worksheets( "Sheet1" )

Open(
	"$DOCUMENTS/Discussion Group/Data wrangling .xlsx",
	Worksheets( "Sheet1" ),
	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( "-" )
	)
)

[#3 · Accepted solution]

Hi, @fionaweston.

Assuming you are using JSL to import the data, you could store the file name (or any portion of the file name) in a variable that could be used to populate a new column. 

filepath = "$SAMPLE_IMPORT_DATA/Team Results.xlsx";
file = Word( -2, filepath, "/." );

dt = Open(
	filepath,
	Worksheets( "Grouped Team Results" ),
	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( 3 ),
		Data Starts on Row( 4 ),
		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 << New Column( "Workbook Name", Character, Set Each Value( file ) );

Alternately, you can automatically place the desired information into the Excel file directly.  See this post for instructions. 

Hope that helps.

[#4]

Thanks Wendy

[#5]

Hi @Wendy_Murphrey ,

Apologies for reviving a finished conversation, but I have a query that is similar in nature.

Just like Fiona, 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.
As for Fiona, 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

[#6]

If I understand what you want, you can definitely write JSL script which is able to handle that. How the JSL script should look like and what it should do, depends a lot on the data you have and how it is created.

Most likely you would need at least Files In Directory(), For loop (or maybe even better For Each), Open() and then some string/datatable manipulation functions,

[#7]

@fionaweston how are you importing these sheets? Multiple File Import has a checkbox to add file names to a column that is added to the final data table. You can create a similar data table column if you are manually concatenating files using the Concatenate platform. The file name for each imported sheet will be the title of the file which, based on your description, should include the file timestamp.

 

 

[#8]

Jeff,

Multiple file Import has the feature I need and that you have highlighted (add a file name column, which I need because the name of each workbook contains the date ex "04/21 Metrics"). However when I use Multiple file Import will only give me back the first sheet of each workbook and I need the 3rd sheet in each of the workbooks. Is there a way that I can get the 3rd sheet from each workbook using Multiple file Import ? I have about 20 or so workbooks.

Conversely,

Using the File/Open, I can call the correct worksheet (sheet 3) from each workbook. So now I have 20 of the correct worksheet but I cannot tell which date they come from because this method does not have an option to create a source column.

Thanks

Fiona

[#9]

Wendy's response shows a simple way to add the source column as the multiple file import creates.

Using Files in Directory() to get the files, and then a simple For() loop reading in one file after another, with each one adding the column like Wendy illustrated, is a very simple approach to use.

[#10]

@fionaweston it sounds like control over tab importation for Excel sheets would be a useful JMP improvement. I found this request to be on the JMP Wish List - give it upvote kudos and we may see this improvement in JMP in the future!

In the meantime, your JMP colleagues above have provided a nice JSL solution to help you out.

Best,

Jeff