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

Jackie_

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?

stan_koprowski

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