Subscribe Bookmark RSS Feed

JSL script to open multiple excel file using wildcard.

Trana

New Contributor

Joined:

Nov 7, 2017

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

1 ACCEPTED SOLUTION

Accepted Solutions
ih

Community Trekker

Joined:

Sep 30, 2016

Solution

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] );
		)
	)
);

 

 

6 REPLIES
ih

Community Trekker

Joined:

Sep 30, 2016

Solution

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

New Contributor

Joined:

Nov 7, 2017

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

Community Trekker

Joined:

Sep 30, 2016

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

Joined:

Jun 22, 2012

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

New Contributor

Joined:

Nov 7, 2017

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

Community Trekker

Joined:

Sep 30, 2016

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] ) )
);