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

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

AFaiz — Thu, 06 Jun 2024 00:39:48 GMT

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

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

txnelson — Thu, 06 Jun 2024 01:51:01 GMT

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

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

AFaiz — Thu, 06 Jun 2024 12:27:27 GMT

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.

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

txnelson — Thu, 06 Jun 2024 12:52:37 GMT

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

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

AFaiz — Mon, 10 Jun 2024 00:10:58 GMT

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.

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

txnelson — Mon, 10 Jun 2024 00:33:52 GMT

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.

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

AFaiz — Tue, 11 Jun 2024 14:06:12 GMT

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

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

txnelson — Tue, 11 Jun 2024 18:06:15 GMT

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.

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

AFaiz — Wed, 12 Jun 2024 15:02:05 GMT

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

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

txnelson — Wed, 12 Jun 2024 15:33:25 GMT

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.

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.

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

AFaiz — Wed, 12 Jun 2024 18:00:07 GMT

The problem is that the column with file name and tab name does not get created. there is a File Name column that contains the source file name followed by the data. The source table is incorrect, the same naming is used for data from multiple files/tabs.

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

txnelson — Wed, 12 Jun 2024 18:23:59 GMT

Please provide the following information from this information from 1 of your imported Excel workbooks

The path directory folder
The file name of the workbook
The names of the Tabs(spreadsheets) in the Excel workbook
The data table names that JMP gave the different data tables after running MFI on the workbook
The values in the column File Name for the data tables created

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

AFaiz — Wed, 12 Jun 2024 20:56:26 GMT

I have attached the script, 2x excel files with multi tabs, and generated table when the script is run.

Thanks for your help,

Faiz

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

txnelson — Thu, 13 Jun 2024 00:27:11 GMT

The documentation does not state it, but apparently the default setting for Stack Mode is Stack Similar. When I force it to Table Per File it seems to fix the issues

theFiles = Multiple File Import(
	<<set folder( "C:\Users\abderrazzakf\OneDrive - Samtec\Desktop\Script testing\" )
) << Set Add File Name Column( 1 )<< Set Stack Mode( "Table Per File" )<< import data;

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

AFaiz — Thu, 13 Jun 2024 10:35:07 GMT

This is working great, now the "Source Table" column lists the file and tab names correctly. Really appreciate all the help.

One last question, not really crucial, is there a way to exclude the data from one of the tabs? I don't really want data from the "Main" tab as it is only listing date and time. Now that I have everything in the same table, I can do some manual cleanup, just wondering if there is a way to add that to the script.

Thanks again,

Faiz

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

txnelson — Thu, 13 Jun 2024 11:27:40 GMT

There are a few ways to do this.

Use a Contains() function to search theFiles list and then use Remove From() function to delete the table from the list, which will remove it from the Concatenate operation.
After the Together data table is created, use the Select Where() function to identify the rows in the data table from the Source Table column that you do not want, and then use Delete Rows to delete the selected rows.