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
Trana
Level I

JSL script to open multiple excel file using wildcard.

Hi, Could anyone help me to code JSL to open multiple excel files using wildcard?

1 ACCEPTED SOLUTION

Accepted Solutions
ih
Super User (Alumni) ih
Super User (Alumni)

Re: JSL script to open multiple excel file using wildcard.

Here is one way which might get you started.  Unless other users will enter the search phrase I recommend specifying the regex yourself. Regexr is a great resource.

 

//list all files in directory
files = Files in Directory( Pick Directory() );

//a string with wildcards, * for any string and ? for exactly one character
str = "My?Files*.xls*";

//Replace windcards with regex character classes.  Note that this does 
//not protect against all strings a user might enter
rgx = Substitute( str, ".", "\.", "*", ".*", "?", "." );

//Or just specify the regex yourself
rgx = "My Files.*\.xlsx?";

//Check each file, if it matches then open it
if(	N items( files ) > 0, 
	for( i = 1, i <= N items( files ), i++,
		if( !is missing( regex( files[i], rgx, "\0" , IGNORECASE ) ),
			//Open script goes here
			Show( files[i] );
		)
	)
);

 

 

View solution in original post

6 REPLIES 6
ih
Super User (Alumni) ih
Super User (Alumni)

Re: JSL script to open multiple excel file using wildcard.

Here is one way which might get you started.  Unless other users will enter the search phrase I recommend specifying the regex yourself. Regexr is a great resource.

 

//list all files in directory
files = Files in Directory( Pick Directory() );

//a string with wildcards, * for any string and ? for exactly one character
str = "My?Files*.xls*";

//Replace windcards with regex character classes.  Note that this does 
//not protect against all strings a user might enter
rgx = Substitute( str, ".", "\.", "*", ".*", "?", "." );

//Or just specify the regex yourself
rgx = "My Files.*\.xlsx?";

//Check each file, if it matches then open it
if(	N items( files ) > 0, 
	for( i = 1, i <= N items( files ), i++,
		if( !is missing( regex( files[i], rgx, "\0" , IGNORECASE ) ),
			//Open script goes here
			Show( files[i] );
		)
	)
);

 

 

Trana
Level I

Re: JSL script to open multiple excel file using wildcard.

Hi,

 

Thank you for taking your time to give me the solution. Your solution is worked. I used the following script:

However, I am not sure how can I use regex here and how using regex will be benificial.

 

My script:

 

// Read all the file names
files = Files In Directory( "C:\Users\MyUserName\Documents\My Data\" );
X = N Items( files );
Y = "C:\Users\MyUserName\Documents\My Data\";
dt = New Table( "My data file");
For( i = 1, i < X, i++,
//open jmp files for each xls files.
dt1 = Open(
Y || files[i],
Worksheets( "my xls worksheet" ),
Use for all sheets( 0 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
 
Worksheet Settings(
1,
Has Column Headers( 0 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 1 ),
Data Starts on Column( 1 ),
Data Ends on Row( 0 ),
Data Ends on Column( 0 ),
Replicated Spanned Rows( 1 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 1 ),
Treat as Hierarchy( 0 )
 
)
);
dt1 << Set Name( files[i] );
 
 
//I concatenated all the files in one jmp file and close it each time.
dt << Concatenate( Data Table( dt1 ), "Append to first table" );
Close( dt1 );
 
 
);

 

 

ih
Super User (Alumni) ih
Super User (Alumni)

Re: JSL script to open multiple excel file using wildcard.

If you want to open every file in that directory (you know they are all xls files) then you are done and do not need to worry about regex.  If you only want to open files that start with 'my file' and end in '.xls' then you could make sure the file matches your criteria by adding an if statement inside your for loop, similar to this.

 

*Untested Code*

// Read all the file names
files = Files In Directory( "C:\Users\MyUserName\Documents\My Data\" );

X = N Items( files );

Y = "C:\Users\MyUserName\Documents\My Data\";

dt = New Table( "My data file" );

For( i = 1, i < X, i++, 

	if( !is missing( regex( files[i], "my file.*\.xls", "\0" , IGNORECASE ) ),


		//open jmp files for each xls files.

		dt1 = Open(

			Y || files[i], 

			Worksheets( "my xls worksheet" ), 

			Use for all sheets( 0 ), 

			Concatenate Worksheets( 0 ), 

			Create Concatenation Column( 0 ), 

	 

			Worksheet Settings(

				1, 
				Has Column Headers( 0 ), 
				Number of Rows in Headers( 1 ), 
				Headers Start on Row( 1 ), 
				Data Starts on Row( 1 ), 
				Data Starts on Column( 1 ), 
				Data Ends on Row( 0 ), 
				Data Ends on Column( 0 ), 
				Replicated Spanned Rows( 1 ), 
				Suppress Hidden Rows( 1 ), 
				Suppress Hidden Columns( 1 ), 
				Suppress Empty Columns( 1 ), 
				Treat as Hierarchy( 0 )
			)

		);

		dt1 << Set Name( files[i] );

	 

		//I concatenated all the files in one jmp file and close it each time.
		dt << Concatenate( Data Table( dt1 ), "Append to first table" );

		Close( dt1 );
	);
);
txnelson
Super User

Re: JSL script to open multiple excel file using wildcard.

Your original question requested the capability to use a Wildcard when selecting the file names to be read into JMP.  RegEx() has a syntax that allows for Wildcards. @ih illustrated how you can structure a RegEx() function to return file names meeting a structure using Wildcards.  

Now the question needs to go back to you to qualify what your thinging was when you specified "using wildcard"?

Jim
Trana
Level I

Re: JSL script to open multiple excel file using wildcard.

Thanks for the response. I thought I would use wild card to select all the files (something like *.*). Seems regex is great to select specific files based on some criteria. Seems for my need now, I can just use the for loop to open all files. I am sure I will need regex later for more flexibility.
ih
Super User (Alumni) ih
Super User (Alumni)

Re: JSL script to open multiple excel file using wildcard.

In that case you might also consider using Pick File function with the "multiple" keyword. From the scripting index:

Names Default To Here( 1 );
Files =
Pick File(
	"Select JMP File",
	"$SAMPLE_DATA",
	{"JMP Files|jmp;jsl;jrn", "All Files|*"},
	1,
	0,
	"",
	"multiple"
);
For( i = 1, i <= N Items( Files ), i++,
	Try( Open( Files[i] ) )
);