JMP14 How can i import multi excel files with defined sheet?

[#1]

Dear All

there is a function of JMP14 which can import multi excel files together and stack them automatically.

But it also choose the first sheet in excel file, can i define the sheet in the excel which include more than one sheet?

thanks!

[#2]

You can select the worksheet you want, when you select the file to open, and then click on the down arrow for the "Open". and select "Open Selected Worksheets". 

If you choose the "Use Excel Wizard" it will allow you to select multiple sheets to open at the same time.

[#3]

Hi

thanks a lot for your reply.

My questions is about new function " import multiple files" in JMP14.

I have more than 10 same structure Excel files which i want to extract some data and stack them together.

For each excel file, there are more than one sheet.

When i use this function, it also extract the data from the first sheet, how can i get the data in second sheet in each excel file?  

[#4]

Hi,

Is there any solution for the problem described by #juna1

[#5]

thanks, Ordon.

I already use the "for" funcition to do it now.

and give up the function"import multi files ".

[#6]

Hi juna1,

is that mean you have to import every single file from given directory manually? It's a pity there is no option to do it via "import multi files" function and jsl.

[#7]

Please add this request to the JMP Wish List

[#8]

No, i use "for "  loop statement  to do that. it`s ok now. thanks

[#9]

Hi @juna1 

What was your final solution to this problem? You mention that you use the "loop" statement. Would it be possible to provide an example of your code?

Thanks

[#10 · Accepted solution]

hi this is part of the script.

dir = Pick Directory( "Select a directory" );
//get all file names under the folder
files = Files In Directory( dir, Recursive );
nfiles = N Items( files );
For( i = 1, i <= N Items( files ), i++,
	fullfile = dir || files[i];
	dt = Open(
		fullfile,
		Worksheets( "Intensity" ),
		Use for all sheets( 0 ),
		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 << New Column( "AbbCon1", character, width( 8 ), <<set each value( files[i] ) );
	If( i == 1,
		(dt1 = dt ; dt1 << Set name( "Tem" ))
	);
	If( i > 1,
		dt1 << Concatenate( dt, "Append to first table" );
		dt << closewindow;
	);
);