How do I Import data from a large number (>100) of excel files, each containing a number of worksheets/tabs, into one JMP file.

[#1]

I need to import (into JMP) specific Excel worksheets from a number of excel files and the data in the JMP file must be stacked with a ID column specifying the file origin.

The Excel import wizard makes it relatively easy to import and stack two or more selected worksheets from a single file (see script below). The data is structured similarly in the different worksheets.

If possible I need to import all the relevant worksheets from all the excel files in one go - I can imagine that it may be solved by some kind of looping of the script below reading the excel filenames from a (captured?) list - but I cannot construct a usable "For loop".

If any one have suggestions I would be more than happy to hear from you

Henrik 

Open(
	"/X:/Extracted data part 1/B6_1_PBS-0_combined.xlsx",
	Worksheets( {"B6_1 Ratio", "B6_2 Ratio"} ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 1 ),
	Create Concatenation Column( 1 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 2 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 3 ),
		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 tables
// → Data Table( "Ratio_00" )
Data Table( "Ratio_00" ) << Concatenate(
	Data Table( "B6_1_PBS-0_combined" ),
	Output Table( "Untitled 82.jmp" ),
	Append to first table
);

[#2]

Hi @hhi,

Have you checked the "Import Multiple Files" platform ? 

Here is a presentation that might help you use it efficiently for your use case : Multiple File Import - JMP User Community

And here is a short Youtube video to use the platform : Multiple File Import - YouTube

I hope this answer will help you,

[#3]

Hi Victor_G

Thanks for the suggestion.

I have tried the multiple file import wizard but I need to import specific excel worksheets/tabs into a given stacked file and the wizard does not provide a way to select the worksheets I want to extract from the excel files.

Henrik

[#4]

If MFI cannot be used, you can get file list by using Files In Directory() and use the list it returns to build your loop. If you have JMP16+ I suggest using For Each instead of For. Within the loop open your new tables and concatenate them to the first table you have created. It might be better to create first table outside the loop or use if statement inside the loop.

[#5]

Hi jthi

Thanks for the suggestion I'll try to build a loop as suggested.

However, I may get MFI to work with a "little" cleanup work.

Henrik

[#6]

Hi Victor_G (again),

I have looked more closely at the MFI output and I think I can  make it work with some post import data cleanup work.

Again thanks for the suggestion.

Henrik