nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

open and work on different excel files

Tomer_Aminizar

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?

Accepted answers

All comments

Jeff_Perkinson

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?

Tomer_Aminizar

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

@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

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.

Mark_Bailey

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

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

up?