topic Merging Column from excel file int JMP data file in Discussions

Merging Column from excel file int JMP data file

Jackie_ — Thu, 19 Mar 2020 18:47:48 GMT

I have two files:

1st file is the Excel

2nd file is the JMP

1st file

2nd file:

I am trying to write a script which can copy the Column Name "Values" from the excel file and past in the 2nd JMP file so my final output will look something like this:

Any suggestions on writing script in JMP?

Re: Merging Column from excel file int JMP data file

stan_koprowski — Thu, 19 Mar 2020 19:26:46 GMT

Hi @Jackie_,

Using JMP Query Builder will allow you to blend data from multiple sources as you describe, Excel and JMP, JMP and a database table, JMP, Excel and database tables.

The scripts will write themselves.

Import the data with Excel Wizard. Right-Click on green-triangle next to the Source script to view the script.

JMP QB Blending multiple data sources

Use JMP Query Builder as described in your previous post merging a column from one JMP file to another JMP file.

New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		["1st_file" =>
		"Source(Open(
	\!"/Users/stkopr/Desktop/1st_file.xlsx\!",
	Worksheets( \!"1st_file\!" ),
	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" => "/Users/stkopr/Desktop/2nd_file.jmp"]
	),
	QueryName( "JMP QB Blending differnt source tables" ),
	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

cheers,

Stan

Re: Merging Column from excel file int JMP data file

Jackie_ — Thu, 19 Mar 2020 20:04:28 GMT

How can I import xlsx file in Primary section ?

Re: Merging Column from excel file int JMP data file

Jackie_ — Thu, 19 Mar 2020 21:18:47 GMT

@stan_koprowski How can I import xlsx file in Primary section ?

Re: Merging Column from excel file int JMP data file

stan_koprowski — Thu, 19 Mar 2020 21:43:56 GMT

Hi @Jackie_,

You can use the Excel Wizard using File-->Open.

See Importing MS Excel file in the help documentation for additional details.

I would also recommend a few additional resources for those just starting out learning JMP:

New User Welcome Kit
Mastering Series: Getting Started with JMP

There is a new "Live event" on March 27 or you can check out one of the on-demand webinars.

cheers,

Stan

Re: Merging Column from excel file int JMP data file

Jackie_ — Fri, 20 Mar 2020 12:43:20 GMT

Hi @stan_koprowski,

How can I display a File Open Dialog Box for the user to select a Excel file and JMP file after running the script?

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?