Merging Column from excel file int JMP data file

Jackie_

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?

stan_koprowski

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

2nd_file.jmp

JMPQB_Blending_two_sources_of_data.jmp

1st_file.xlsx