cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
AFaiz
Level II

import Multiple excel files with many tabs into one table in JMP

Hi, I am using "import Multiple files" feature to get data from different excel files, my challenge is that only data from the first tab are extracted. I have three more tabs on each file that I would like to get the data from. Is there a way to do that in JMP? appreciate all the help.

AFaiz

15 REPLIES 15
txnelson
Super User

Re: import Multiple excel files with many tabs into one table in JMP

Using 

names default to here(1);
theFiles = multiple file import(
	<<set folder("<path to folder>")
)<<import data

in JMP 16, JMP 17 and JMP 18, multiple sheet Excel files open all sheets.  JMP 15 and earlier only open the first Sheet.

If you are using JMP 15 or earlier, you can use the Files in Directory() to get all of the files to read, and then loop through using Open() to read in the Excel files and it will read all of the sheets

 

 

Jim
AFaiz
Level II

Re: import Multiple excel files with many tabs into one table in JMP

thanks a lot! the script worked great as expected. It opened 3x JMP tables because each of the excel files have 3x tabs.

Is there a way to concatenate these files into one? I believe that requires the JMP columns to have the same names, and I am not sure how to do that using JMP scripting. Any recommendations? Thanks. 

txnelson
Super User

Re: import Multiple excel files with many tabs into one table in JMP

Names Default To Here( 1 );
theFiles = Multiple File Import(
	<<set folder( "<path to folder>" )
) << import data;

// There is a option that can be specified to the MFI
//    << Stack Similar Files
// that can be specified, and JMP will stack all files that
// have the same columns. 

// However, the below JSL can be used to Concatenate(Stack) all created
// data tables

// The MFI returns a complete list of all of the data tables created
// The variable in this example that contains that list is the
// variable "theFiles".  It can be used in the Concatenation Platform
// to reference all of the created data tables.
dtfinal = New Table( "Together" );
dtFinal << concatenate(
	theFiles,
	Append to First Table( 1 ),
	Create Source Column( 1 )
);

// To close the individual files after the concatenation
For Each( {file}, theFiles, Close( file, nosave ) );
Jim
AFaiz
Level II

Re: import Multiple excel files with many tabs into one table in JMP

I have added the additional script lines, and I keep getting the error below when trying to run it, is JMP scripting case sensitive? I correct the "f" in dtfinal to dfFinal, but did not help. 

AFaiz_0-1717978158885.png

 

txnelson
Super User

Re: import Multiple excel files with many tabs into one table in JMP

I suspect the JSL line just prior to the line

dtfinal = New Table( "Together" );

does not end in a semicolon.

 

Without seeing the lines before what you included only lets me guess as to the issue.

Jim
AFaiz
Level II

Re: import Multiple excel files with many tabs into one table in JMP

I copied the Script again, and it run with no errors.

It doing mostly what is needed, only issue is that the added column that describes where the data point is coming from (the file and Tab) is not correct. 

Ideally, I would like it to create a description column stating where each data point is coming file, something lie "File_Name_Tab_Name". Is there a way to add that to the script?

Appreciate all the help,

Faiz

txnelson
Super User

Re: import Multiple excel files with many tabs into one table in JMP

If you change the MFI to 

Names Default To Here( 1 );
theFiles = Multiple File Import(
	<<set folder( "<path to folder>" )
) << Set Add File Name Column( 1 )<< import data;

It will create a column that contains the file name.  You can then add a new column in the final data table that puts the File Name column together with the Source Table column to get what you need.

Jim
AFaiz
Level II

Re: import Multiple excel files with many tabs into one table in JMP

The script looks good, the generated file has a new column listing the source file name, but it is not listing the tab name. All the data from the three tabs have the same file name, nothing listed about the tab name. Any other recommendations?

Thanks a lot,

Faiz

 

AFaiz_0-1718204512929.png

 

txnelson
Super User

Re: import Multiple excel files with many tabs into one table in JMP

If the code you ran specified 

<< Set Add File Name Column( 1 )<< import data;

The MFI will create a new column in each table created called "File Name".  It contains the name of the Excel workbook.  The MFI also gives each data table created a name based upon the  workbook name followed by the individual Tab name.

Therefore, when the Concatenate is run, with the option

Create Source Column( 1 )

specified, an additional column named "Source Table" is created in the concatenation result table.  

txnelson_0-1718206072451.png

and it contains the workbook name followed by the Tab name.  From what I interpreted from your question, that should provide you with the information you need.  If you want the data from the 2 column displayed in a different form, such as combined into a single column, all that you need to do is to create a new column with a formula that concatenates the information from the 2 columns together.

Jim