topic Re: How to import 30 Excel files without opening the data tables? in Discussions

How to import 30 Excel files without opening the data tables?

TDK_Long — Thu, 08 Jun 2023 16:36:15 GMT

Hi folks,

I am using JMP scripts to import the data of 30 Excel files. When I run it, there will be 30 data tables opened during the "Open" action. So I have to manually close each of these 30 data tables, which is very tedious. Is there a way that I can read / import the data from Excel without opening the data table so that I do not need to close the data table one by one? Your comments / suggestions are much appreciated.

For example:

Names Default To Here( 1 );
dt01 = Open( "$SAMPLE_DATA/Big Class01.jmp" );
dt02 = Open( "$SAMPLE_DATA/Big Class02.jmp" );
dt03 = Open( "$SAMPLE_DATA/Big Class03.jmp" );
dt04 = Open( "$SAMPLE_DATA/Big Class04.jmp" );
dt05 = Open( "$SAMPLE_DATA/Big Class05.jmp" );
dt06 = Open( "$SAMPLE_DATA/Big Class06.jmp" );

....

Re: How to import 30 Excel files without opening the data tables?

WebDesignesCrow — Tue, 14 Feb 2023 01:20:12 GMT

To close the 30 files, you can script the close action as well;

Close (dt01);

Close (dt02);

Close (dt03);

......

Re: How to import 30 Excel files without opening the data tables?

txnelson — Tue, 14 Feb 2023 01:46:40 GMT

It is pretty simple to handle multiple open files that need to be closed. Below is an expansion on your example, that opens the data tables, without having them visible, and then closing them when the activity with them is complete.

Names Default To Here( 1 );
dList = {};

dt01 = Open( "$SAMPLE_DATA/Big Class01.jmp", invisible );
Insert Into( dList, "dt01" );
dt02 = Open( "$SAMPLE_DATA/Big Class02.jmp", invisible );
Insert Into( dList, "dt02" );
dt03 = Open( "$SAMPLE_DATA/Big Class03.jmp", invisible );
Insert Into( dList, "dt03" );
dt04 = Open( "$SAMPLE_DATA/Big Class04.jmp", invisible );
Insert Into( dList, "dt04" );
dt05 = Open( "$SAMPLE_DATA/Big Class05.jmp", invisible );
Insert Into( dList, "dt05" );
dt06 = Open( "$SAMPLE_DATA/Big Class06.jmp", invisible );
Insert Into( dList, "dt06" );

// When ready to close them
For Each( {dt, i}, dList,
	Close( Eval( Parse( dList[i] ) ), nosave )
);

Rather than using the "Invisible" option, the "Private" option can be used, and then the data table is not visible in the Home Window.

If you want to provide this capability in an interactive mode, you can open all of the data tables, and then using the Home Window, you can highlight all of the data table you want to close, and the right click, and select "Close". JMP will ask is you want to Save the data tables, and you can select "Save None" and it will then close all of the highlighted data tables all at once.

Re: How to import 30 Excel files without opening the data tables?

Craige_Hales — Tue, 14 Feb 2023 02:15:19 GMT

Take a look at Multiple File Import. MFI can load similar external files into a single JMP data table.

Import all the xlsx files from a directory.

You can save (and modify) the script to re-import again.

Re: How to import 30 Excel files without opening the data tables?

TDK_Long — Tue, 14 Feb 2023 17:31:52 GMT

Thanks, Jim. Very nice.

Re: How to import 30 Excel files without opening the data tables?

TDK_Long — Tue, 14 Feb 2023 17:32:36 GMT

Thank you Craige.

Re: How to import 30 Excel files without opening the data tables?

Mark_Bailey — Tue, 14 Feb 2023 19:36:57 GMT

You must import data into a data table. I assume you want to avoid the tedium of opening and closing individual files. Have you tried the Multiple File Import feature?