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

JSL script to open multiple excel file using wildcard.

Trana — Tue, 07 Nov 2017 22:09:15 GMT

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

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

ih — Wed, 08 Nov 2017 02:57:23 GMT

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

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

Trana — Wed, 08 Nov 2017 20:10:23 GMT

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

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

ih — Wed, 08 Nov 2017 19:34:28 GMT

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

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

txnelson — Wed, 08 Nov 2017 19:54:15 GMT

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"?

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

Trana — Wed, 08 Nov 2017 20:13:36 GMT

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.

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

ih — Wed, 08 Nov 2017 20:19:56 GMT

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