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

[#1]

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

[#2]

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

[#3]

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. 

[#4]

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

[#5]

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. 

[#6]

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.

[#7]

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

[#8]

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.

[#9]

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

[#10]

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.