Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
Jacksmith12
Level IV

Merging Column from excel file int JMP data file

I have two files:

1st file is the Excel

2nd file is the JMP 

 

1st file

Excel.PNG

2nd file:

JMP file.PNG

 

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:

 

Output.PNG

Any suggestions on writing script in JMP?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
stan_koprowski
Community Manager Community Manager

Re: Merging Column from excel file int JMP data file

Hi @Jacksmith12,

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 sourcesJMP 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

View solution in original post

5 REPLIES 5
Highlighted
stan_koprowski
Community Manager Community Manager

Re: Merging Column from excel file int JMP data file

Hi @Jacksmith12,

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 sourcesJMP 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

View solution in original post

Highlighted
Jacksmith12
Level IV

Re: Merging Column from excel file int JMP data file

How can I import xlsx file in Primary section ?

Import.PNG

Highlighted
Jacksmith12
Level IV

Re: Merging Column from excel file int JMP data file

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

Re: Merging Column from excel file int JMP data file

Hi @Jacksmith12,

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:

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

cheers,

Stan

 

Highlighted
Jacksmith12
Level IV

Re: Merging Column from excel file int JMP data file

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?

 

Import.PNG

 

 

 

Article Labels

    There are no labels assigned to this post.