cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Jackie_
Level VI

Display a File Open Dialog Box for the user to select a Excel file

I created a SQL script using JMP SQL Builder

 


New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		["Limits" =>
		"Source(Open(
	\!"C:\Jay\JMP\JMP Scripts\Python Scripts\JMP scripts\New folder\New folder\SQL 2\Limits.xlsx\!",
	Worksheets( \!"Limits\!" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( \!"-\!" )
	)
))",
		"SummaryTable" =>
		"\C:\Jay\JMP\JMP Scripts\Python Scripts\JMP scripts\New folder\New folder\SQL 2\SummaryTable.jmp"
		]
	),
	QueryName( "SQLQuery11" ),
	Select(
		Column( "Test Number", "t2" ),
		Column( "Test Name", "t2" ),
		Column( "Test Units", "t2" ),
		Column( "LL", "t2" ),
		Column( "UL", "t2" ),
		Column( "N", "t2" ),
		Column( "Failing Soft Bin", "t1" )
	),
	From(
		Table( "Limits", Alias( "t1" ) ),
		Table(
			"SummaryTable",
			Alias( "t2" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "Test Number", "t2" ), Column( "Test Number", "t1" ) )
				 & EQ( Column( "Test Name", "t2" ), Column( "Test Name", "t1" ) ) &
				EQ( Column( "Test Units", "t2" ), Column( "Test Units", "t1" ) )
			)
		)
	)
) << Run

The above script does the task of copying a column but I how can I display a File Open Dialog Box for the user to select a Excel file and JMP file after running the script?

Any suggestions?

 

Every time I have to change the directory path in the script if I run the script on other files. Does JMP Query Builder has any syntax to display a file open Dialog box?

1 ACCEPTED SOLUTION

Accepted Solutions
stan_koprowski
Community Manager Community Manager

Re: Display a File Open Dialog Box for the user to select a Excel file

Hi @Jackie_ ,

You have some syntax errors including missing semicolons after each of your pick statements. Please start to read the scripting guide or refer to the scripting index for additional details on the use of the Pick function.

 

Try the following script which will prompt to open an Excel file and then prompt to open a JMP data table to use in the query before performing the  join.  

 

A word of caution when running the query on the two opened tables; the script will only work if the existing data tables have the respective columns in each of the opened data tables.

 

Names Default To Here( 1 );
//Prompt to select and open an MS Excel file
dt1 = Open( Pick File( "Select Excel File", "$DOCUMENTS", {"Excel Files|csv;xlsx;xls", "All Files|*"}, 1, 0 ), Invisible );

//Prompt to select an open a JMP data table
dt2 = Open( Pick File( "Select JMP File", "$DOCUMENTS", {"JMP Files|jmp", "All Files|*"}, 1, 0 ), Invisible );

//Perform a left outer join on the tables using the "Name" Column
New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		["1st_file" =>
		"Source(Open( \!"dt1\!",
	Worksheets( \!"worksheet\!" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 1 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( \!"-\!" )
	)
))",
		"2nd_file" => "Source(Open( \!"dt2\!"))"]
	),
	QueryName( "Data_Blending" ),
	Select(
		Column( "Name", "t2" ),
		Column( "Enable", "t2" ),
		Column( "Type", "t2" ),
		Column( "Phase", "t2" ),
		Column( "Values", "t3" )
	),
	From(
		Table( "2nd_file", Alias( "t2" ) ),
		Table(
			"1st_file",
			Alias( "t3" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "Name", "t2" ), Column( "Name", "t3" ) )
			)
		)
	)
) << Run;

//Close source files without saving
Close(dt1, "NoSave");
Close(dt2, "NoSave") 

cheers,

Stan

 

 

View solution in original post

5 REPLIES 5
txnelson
Super User

Re: Display a File Open Dialog Box for the user to select a Excel file

I am not sure I am answering what you want, but you can run 2 Pick File() functions, one after another, with the first one selecting the xlsx and then the second, selecting the jmp file.  Then substitute into your script, and run the code.

Jim
Jackie_
Level VI

Re: Display a File Open Dialog Box for the user to select a Excel file

Hi @txnelson,

dt = Pick File()
dt2 = Pick FIle()
New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		["Limits" =>
		"Source(Open(
	\!"dt\!",
	Worksheets( \!"Limits\!" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( \!"-\!" )
	)
))",
		"SummaryTable" => "dt2"
		
		]
	),
	QueryName( "SummaryTable" ),
	Select(
		Column( "Test Number", "t2" ),
		Column( "Test Name", "t2" ),
		Column( "Test Units", "t2" ),
		Column( "LL", "t2" ),
		Column( "UL", "t2" ),
		Column( "N", "t2" ),
		Column( "Failing Soft Bin", "t1" )
	),
	From(
		Table( "Limits", Alias( "t1" ) ),
		Table(
			"SummaryTable",
			Alias( "t2" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "Test Number", "t2" ), Column( "Test Number", "t1" ) )
				 & EQ( Column( "Test Name", "t2" ), Column( "Test Name", "t1" ) ) &
				EQ( Column( "Test Units", "t2" ), Column( "Test Units", "t1" ) )
			)
		)
	)
)
<< Run

I used two Pick File() functions, one after another, with the first one selecting the xlsx and then the second, selecting the jmp file. Then substitute into the script. Getting errors

 

Any suggestions?

 

stan_koprowski
Community Manager Community Manager

Re: Display a File Open Dialog Box for the user to select a Excel file

Hi @Jackie_ ,

You have some syntax errors including missing semicolons after each of your pick statements. Please start to read the scripting guide or refer to the scripting index for additional details on the use of the Pick function.

 

Try the following script which will prompt to open an Excel file and then prompt to open a JMP data table to use in the query before performing the  join.  

 

A word of caution when running the query on the two opened tables; the script will only work if the existing data tables have the respective columns in each of the opened data tables.

 

Names Default To Here( 1 );
//Prompt to select and open an MS Excel file
dt1 = Open( Pick File( "Select Excel File", "$DOCUMENTS", {"Excel Files|csv;xlsx;xls", "All Files|*"}, 1, 0 ), Invisible );

//Prompt to select an open a JMP data table
dt2 = Open( Pick File( "Select JMP File", "$DOCUMENTS", {"JMP Files|jmp", "All Files|*"}, 1, 0 ), Invisible );

//Perform a left outer join on the tables using the "Name" Column
New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		["1st_file" =>
		"Source(Open( \!"dt1\!",
	Worksheets( \!"worksheet\!" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 1 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( \!"-\!" )
	)
))",
		"2nd_file" => "Source(Open( \!"dt2\!"))"]
	),
	QueryName( "Data_Blending" ),
	Select(
		Column( "Name", "t2" ),
		Column( "Enable", "t2" ),
		Column( "Type", "t2" ),
		Column( "Phase", "t2" ),
		Column( "Values", "t3" )
	),
	From(
		Table( "2nd_file", Alias( "t2" ) ),
		Table(
			"1st_file",
			Alias( "t3" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "Name", "t2" ), Column( "Name", "t3" ) )
			)
		)
	)
) << Run;

//Close source files without saving
Close(dt1, "NoSave");
Close(dt2, "NoSave") 

cheers,

Stan

 

 

Jackie_
Level VI

Re: Display a File Open Dialog Box for the user to select a Excel file

Hi @stan_koprowski ,

 

I appreciate your help. The script will only run if the following file name matches 


 
Updating Media

What if I want to run the same script on different files ? Let's my 2nd file name remains constant and I want to perform similar operations on different files. I have multiple excel file with different name(All files have same columns) 

 

Any suggestions?

 

Jackie_
Level VI

Re: Display a File Open Dialog Box for the user to select a Excel file

Jacksmith12_0-1585076254812.png