cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
juna1
Level III

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
juna1
Level III

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

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;
	);
);

View solution in original post

10 REPLIES 10
txnelson
Super User

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

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". 

selected.PNG

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

Jim
juna1
Level III

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

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?  

 

Capture-7.PNG

 

 

Ordon
Level III

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

Hi,

Is there any solution for the problem described by #juna1

juna1
Level III

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

thanks, Ordon.

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

and give up the function"import multi files ".

Ordon
Level III

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

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.

txnelson
Super User

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

Please add this request to the JMP Wish List
Jim
juna1
Level III

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

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

JoeyJambo
Level I

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

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

juna1
Level III

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

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;
	);
);