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

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

Jackie_ — Fri, 20 Mar 2020 19:47:49 GMT

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?

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

txnelson — Fri, 20 Mar 2020 21:33:56 GMT

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.

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

Jackie_ — Mon, 23 Mar 2020 19:54:47 GMT

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?

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

stan_koprowski — Mon, 23 Mar 2020 22:14:49 GMT

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

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

Jackie_ — Tue, 24 Mar 2020 18:56:18 GMT

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?

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

Jackie_ — Tue, 24 Mar 2020 18:57:45 GMT