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

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

juna1 — Mon, 26 Nov 2018 06:38:25 GMT

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!

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

txnelson — Mon, 26 Nov 2018 10:46:08 GMT

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.

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

juna1 — Wed, 06 Mar 2019 13:42:51 GMT

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?

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

Ordon — Wed, 06 Mar 2019 08:49:48 GMT

Hi,

Is there any solution for the problem described by #juna1

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

juna1 — Wed, 06 Mar 2019 08:58:51 GMT

thanks, Ordon.

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

and give up the function"import multi files ".

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

Ordon — Wed, 06 Mar 2019 09:57:11 GMT

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.

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

txnelson — Wed, 06 Mar 2019 13:44:06 GMT

Please add this request to the JMP Wish List

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

juna1 — Thu, 07 Mar 2019 00:35:17 GMT

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

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

JoeyJambo — Mon, 17 Jun 2019 11:02:57 GMT

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

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

juna1 — Tue, 18 Jun 2019 16:48:11 GMT

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

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

JoeyJambo — Tue, 18 Jun 2019 10:46:15 GMT

@juna1Super thanks

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

ashwint27 — Wed, 17 Jul 2024 19:06:15 GMT

Hi @juna1 . This script works great. I notice that if a file in the directory does not contain the worksheet name, the script stops and doesn't complete the remaining files that do have it. Is there some code we can insert to skip files in the selected directory that do not contain the worksheet name?

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

txnelson — Wed, 17 Jul 2024 21:50:52 GMT

You can place a Try() function around the Open() function and it will keep the Excel workbooks that do not have the correct sheet name from erroring out.

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 = "";
	Try(
		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( "-" )
			)
		)
	);
	If( dt != "",
		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;
		);
	);
);