cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Tomer_Aminizar
Level II

open and work on different excel files

hi all

Im trying to build a script that will open an excel file from a folder and will filter this file based on conditions that might change from time to time.

I need the user to state the file name and the conditions for that time, base on whatever he needs from 3-4 columns.

Ive tried to do that manually and take the script but it never worked.

Can anyone assist?

8 REPLIES 8
Jeff_Perkinson
Community Manager Community Manager

Re: open and work on different excel files

What you described is possible but we're going to need some more information about where you're having trouble.

 

What have you tried? What errors or problems did you run into?

-Jeff
Tomer_Aminizar
Level II

Re: open and work on different excel files

Ill try to be more specific

this is the code for opening a file 

 

Open(
	"\\source\fileName.xlsx",
	Worksheets( "Sheet1" ),
	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( "-" )
	)
)

now i want the user to have a option to open that file based on a name that hell insert.

 

the second part of the problem is that i want the user to have a way of filtering the file based on a few fixed filters. for example, column J which called people_count equals to 60 or column G which called type will be equal to either travel or business.

the main point of this script is to let the users open files quicly with the filters they usually use.

 

thanks for the help

gzmorgan0
Super User (Alumni)

Re: open and work on different excel files

@Tomer_Aminizar ,

 

There are multiple ways to do this, but you should select what  makes sense for your user environment.

 

The script below provides one option using Pick File() . Note, select Team Results.xlsx when prompted

Names Default To Here( 1 );

//***Select the file Team Results.xlsx

xlfid = Pick File(
	"Select the Excel File",        //prompt
	"$Sample_Import_Data",          //starting path
	{"Excel Files|xls;xlsx", "Excel Files|*"},   //file types
	1,
	0,
	""
	);

//xlfid is the use selected file path 

dt = Open( xlfid,                 //file path
	Use for all sheets( 1 ),
	Concatenate Worksheets( 1 ),
	Create Concatenation Column( 1 ),
	Worksheet Settings(
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 3 ),
		Data Starts on Row( 4 ),
		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( "-" )
	)
);

There are options to use SQL database commands, but that might require your users to set up OBDC connections to Excel files.

 

If the Excel files are not HUGE, it might be easier to create the file and use JSL  dt << Select Where( ) syntax to get the observations meeting certain criteria.

 

It seems you are new to the JMP Scripting Language (JSL) . Is that true?  JMP will capture the script (JSL) for JMP platforms (Open, Sort, Fit YbyX, etc.) but it does not record the point and click steps you take. The onus is on scripter to find the equivalent JSL syntax.  

 

If you are new, I suggest you get familiar with Help > Scripting Index > and search. The JMP Community can be helpful for finding the JSL function (object message) to achieve certain tasks.

 

A simple safe (no IP data)  example would be useful.    

 

Good Luck.

Tomer_Aminizar
Level II

Re: open and work on different excel files

ill try to be more specific

here are the steps I need to be able to accomplish:

1. import an excel file based on his name - I need to have a variable that when the user inserts the name of the file it will update the jsl and will open the specific file.

2. filtering - I need to be able to filter out things - like to only include when age = 15 or country = USA - or by column J =25  or G = England

3. fit model - the city will be the X axis and the salary will be the Y axis.

 

thanks in advance. 

 

txnelson
Super User

Re: open and work on different excel files

The Community Discussion forum is there to assist individuals in solving problems and assisting them in their journey of learning JMP.  It is not intended as a place one goes to request that a script be written per their specifications.  I believe it is for your best interest, to take the time to read the documentation, "Discovering JMP", "Using JMP" and the "Scripting Guide", to help you in putting together the application you want.  @gzmorgan0 gave you a good "leg-up" in getting started.  Below, I have taken her code and put together a very simplistic script, that should give you a second "leg-up", to you developing your script.

Names Default To Here( 1 );

nw = New Window( "Input File Name",
	V List Box(
		H List Box(
			Text Box( "Enter File Name:  " ),
			theName = Text Edit Box( "",
				<<set width( 150 ),
				<<Set Script( fileName = theName << get )
			)
		),
		H List Box(
			Button Box( "OK",
				dt = Open(
					fileName,                 //file path
					Use for all sheets( 1 ),
					Concatenate Worksheets( 1 ),
					Create Concatenation Column( 1 ),
					Worksheet Settings(
						Has Column Headers( 1 ),
						Number of Rows in Headers( 1 ),
						Headers Start on Row( 3 ),
						Data Starts on Row( 4 ),
						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( "-" )
					)
				);
				dt << Graph Builder(
					Size( 534, 450 ),
					Show Control Panel( 0 ),
					Variables( X( :City ), Y( :Salary ) ),
					Elements( Points( X, Y, Legend( 6 ) ) ),
					Local Data Filter( Add Filter( columns( :Country, :Age ) ) )
				);
			),
			Button Box( "Cancel", Throw() )
		)
	)
);


If during your development of your script you come across specific questions or issues that you can not solve, the Community will be here to help you.

 

Jim

Re: open and work on different excel files

Another approach is to treat the Excel workbook as a relational database. You can take the user input from a dialog box and make the SQL statement for the second argument to the Open Database() function. There is nothing wrong with using the Open() function and then filtering the data from within JMP. I just wanted you to be aware of the other approach.

Tomer_Aminizar
Level II

Re: open and work on different excel files

that really sound more like something i would do. my boss insists on everything to be automatic by code, but i know sql so it might be more easy.

can you explain how i use the sql query on a jmp file? can you give example for openning and than filtering jsl code?

thank you in advance

Tomer_Aminizar
Level II

Re: open and work on different excel files

up?