Multiple file import Excel hidden row

[#1]

Hello,

I am using JMP 18 and I need to import multiple excel files.
As different users applied different filters I add this command to import even filtered rows from all excel files. 
<<Set Excel Suppress Hidden Rows( 1 ),

However the filtered data are not imported. Is there a difference on how JMP or excel handle hidden vs filtered rows? Is there another command I should use?

Many thanks for your help

Marie

[#2]

Does single file export work fine for you?

For me Multiple File Import (and normal Open with settings) seem to work fine: I have very simple excel file like this where value = 2 has been filtered out from A

and multiple file import is able to import it

View more...

Multiple File Import(
	<<Set Folder(""),
	<<Set Show Hidden(0),
	<<Set Subfolders(0),
	<<Set Name Filter("demo.xlsx"),
	<<Set Name Enable(1),
	<<Set Size Filter({9083, 9083}),
	<<Set Size Enable(0),
	<<Set Date Filter({3796722227.594, 3796722227.594}),
	<<Set Date Enable(0),
	<<Set Add File Name Column(0),
	<<Set Add File Size Column(0),
	<<Set Add File Date Column(0),
	<<Set Import Mode("CSVData"),
	<<Set Charset("Best Guess"),
	<<Set Stack Mode("Stack Similar"),
	<<Set CSV Has Headers(1),
	<<Set CSV Allow Numeric(1),
	<<Set CSV First Header Line(1),
	<<Set CSV Number Of Header Lines(1),
	<<Set CSV First Data Line(2),
	<<Set CSV EOF Comma(1),
	<<Set CSV EOF Tab(0),
	<<Set CSV EOF Space(0),
	<<Set CSV EOF Spaces(0),
	<<Set CSV EOF Other(""),
	<<Set CSV EOL CRLF(1),
	<<Set CSV EOL CR(1),
	<<Set CSV EOL LF(1),
	<<Set CSV EOL Semicolon(0),
	<<Set CSV EOL Other(""),
	<<Set CSV Quote("\!""),
	<<Set CSV Escape(""),
	<<Set XML Method("guess"),
	<<Set XML Guess("huge"),
	<<Set XML Settings(XML Settings()),
	<<Set JSON Method("guess"),
	<<Set JSON Guess("huge"),
	<<Set JSON Settings(JSON Settings()),
	<<Set PDF Method("guess"),
	<<Set PDF Settings(PDF All Tables(Combine(All))),
	<<Set Excel Best Guess(0),
	<<Set Excel Worksheet Filter(""),
	<<Set Excel Has Headers(1),
	<<Set Excel First Header Line(1),
	<<Set Excel Number of Header Lines(1),
	<<Set Excel First Data Line(2),
	<<Set Excel First Data Column(1),
	<<Set Excel Limit Column Type Detection(0),
	<<Set Excel Column Headers As Hierarchies(0),
	<<Set Excel Replicate Data In Spanned Rows(1),
	<<Set Excel Suppress Hidden Rows(0),
	<<Set Excel Suppress Hidden Columns(1),
	<<Set Excel Suppress Empty Columns(1),
	<<Set Excel Last Data Row(.),
	<<Set Excel Last Data Column(.),
	<<Set Excel Column Name Separator("-"),
	<<Set Excel Multiple Series Stack(0),
	<<Set Excel Replicate Headers In Spanned Rows(0),
	<<Set Excel Import Color Cells(0),
	<<Set Excel Add Sheet Name Column(0),
	<<Set Import Callback(Empty())
) << Import Data;

[#3]

Dear Jarmo,

Thanks for your prompt reply.
It is working when I do it for only one table but it doesn't work if I do it for multiple files either using jsl or the platform.

My excel sheets have macros and it is a date filter.
Not sure if it could make a difference.

[#4 · Accepted solution]

I would guess that it could make a difference depending on how the macros have been created, for example the data might not be there at all if it is being filtered out by a macro (so it is not hidden) but if it works for single file that seems a bit weird (If I just copy paste my simple demo excel sheet it is still working with Multiple File Import). 

Maybe you can just avoid Multiple File Import and loop over the files using some JSL if Multiple File Import has some problems importing your file. It might also be worth contacting JMP support as there could be some bugs (excel import did change in JMP18 https://community.jmp.com/t5/JMP-Knowledge-Base/Changes-to-the-import-of-Microsoft-Excel-files-in-JM...)

[#5]

Thanks again,

Yes I will contact support.

The data are here and just filtered, the macros are executed on other columns than the one filtered.

Your fast and precise answers are really helping me with my JSL.

Best,

Marie

[#6 · Accepted solution]

Hello,

I have followed up with support and think it is worth to put the answer here so people struggling will find the answer here.

Hidden and Filtered rows are two different things. So "<<Set Excel Suppress Hidden Rows( 0 )," doesn't apply to filtered rows.
You have to remove filter from excel so you can import all data in JMP.

[#7]

I would like to add;

- In JMP 18 - the JSL script generated by Excel Import Wizard to "Suppress Hidden Rows (1)" seems didn't work.

It works in JMP 17.2.

- I've contacted JMP Technical support but rather than creating a new discussion, I think it would beneficial to continue from this discussion to avoid duplicating almost similar topic:

So far, the work-around that I can think-of requires me to accept this "hidden" row in Excel during importing.

But, it messed-up 24 numeric columns "Data type" including 6 columns with "Date format" due to 1 empty row.

I'm looking for an alternative to "Suppress the Hidden Rows" during Excel Import before adding more lines to my script.

Thanks