topic Re: Open files (lookup characters from file name) in Discussions

Open files (lookup characters from file name)

CG — Mon, 29 Jan 2018 19:30:43 GMT

All,
I am trying to open files in a folder by searching characters of file name based on user input.

/* Input values in 3 columns by user. eg of file name: ABC_12_XYZ. User will input ABC in 1st column, 12 in second col and XYZ in 3rd column. all three should match in a row for selecting the file. There could be 100's of files (unique name) in the folder. User will input all that he wants to select from folder into input file. The file name search is limited to 10 characters from beginning of file name. The code is getting stuck somewhere in loop*/

//Script ------

Retrieve = Open( "C:\*\inputfile.jmp" );
　
x = Column( Retrieve, "Col1" ) << Get Values;
y = Column( Retrieve, "Col2" ) << Get Values;
z = Column( Retrieve, "Col3" ) << Get Values;

　
// location of files which needs to be read from a folder based on user inputs.
Set Default Directory("C:\test\");

Close( Retrieve, NoSave );

Col1_search = 1;
Col2_search = 1;
Count = 0;
　
For( i = 1, i <= N Items( files ), i++,
For( j = 1, j <= N Items( z ), j++,
If( Contains( files[i], z[j] ),
If( Col1_search == 1,
If( (Contains( files[i], z[j] ) & Contains( Left( files[i], 10 ), x[j] )) > 0,


If( Col2_Search == 1,
If(
((Contains( files[i], z[j] ) & Contains( Left( files[i], 10 ), x[j] )) > 0) & (Contains( Left( files[i], 10 ), y[j] ) > (Contains( Left( files[i], 10 ), x[j] ))),
Open( files[i] );
Count++;
),
Open( files[i] );


If( Count == N Items( z ),
Break();

);

)
)

),
Open( files[i] )

)

);

);


openDTs = {};

Re: Open files (lookup characters from file name)

uday_guntupalli — Mon, 29 Jan 2018 20:17:13 GMT

@CG,
Is it fair to assume that in your case, the file format will always be ABC_12_XYZ (i.e. x_y_z) ? If yes, an alternative and simpler way might be :

Retrieve = Open( "C:\*\inputfile.jmp" );
　
x = Column( Retrieve, "Col1" ) << Get Values;
y = Column( Retrieve, "Col2" ) << Get Values;
z = Column( Retrieve, "Col3" ) << Get Values;

Close(Retrieve,"No Save"); 

TestDir = "C:\test\";
Files = Files In Directory(TestDir); // Returns a list of files in the directory
for(i = 1, i <= N Items(Files), i++,
       If(!IsMissing(Regex(Char(x || "_" || y || "_"||z),Files[i])),
			Open(TestDir ||"\" || Files[i]); 
         ); 
    );

Re: Open files (lookup characters from file name)

CG — Mon, 29 Jan 2018 22:09:37 GMT

i am looking for 1st 18 characters in file name

i modified below loop and added condition to look for 18 characters upto z for file name x_y_z1234

for(i = 1, i <= N Items(Files), i++,

If (!IsMissing(Regex(Char(x || "_" || y || "_"||z),contains (Left (Files[i]),18)),

Open(TestDir ||"\" || Files[i]);

);

But i still get error -

For( i = 1, i <= N Items( Files ), i++,

If/*###*/(Is Missing(

Regex( Char( x || "_" || y || "_" || z ), Contains( Left( Files[i] ), 18 ) ),

Open( TestDir || "\" || Files[i] )

))

)

Re: Open files (lookup characters from file name)

uday_guntupalli — Mon, 29 Jan 2018 22:32:32 GMT

@CG,
Can you share what the error is ? A copy of your log ?

Also, I don't follow why you are adding a Contains ?
The second argument should be the term you are trying to match against :
So , if you were trying to look for your name, it would like :

If (!IsMissing(Regex(Char(x || "_" || y || "_"||z),"CG")),

Re: Open files (lookup characters from file name)

CG — Mon, 29 Jan 2018 22:48:19 GMT

Here is copy of log -

In the following script, error marked by /*###*/

For( i = 1, i <= N Items( Files ), i++,

If(

!Is Missing(

Regex/*###*/(Char(

x || /*###*/"_" || /*###*/y || /*###*/"_" || /*###*/z/*###*/

), Files[i])

Open( TestDir || "\" || Files[i] )

)

The reason why i am using contains is to look for 18 characters from file name which could be 40characters long.

I am not sure if i am doing it right.

Re: Open files (lookup characters from file name)

uday_guntupalli — Mon, 29 Jan 2018 23:40:26 GMT

dt = Open( "$SAMPLE_DATA/Cities.jmp" ); // Open Sample Data 

dt << Select Where(!IsMissing(Regex(Char(:city),"ALB")));

You dont need to use "Contains" function. Look at the example shown above. If you wanted to just search for 18 characters, of the file name, then that will need to be extracted using the Substr function

dt = Open( "$SAMPLE_DATA/Cities.jmp" ); // Open Sample Data 

dt << Select Where(!IsMissing(Regex(Char(:city),Substr("ALBANY",1,3))));

Re: Open files (lookup characters from file name)

CG — Tue, 30 Jan 2018 23:43:54 GMT

@Uday

I am able to figure out the issue. The source is 18 characters and pattern is entire string.

I added one more loop to perform lookup of input files against all files as below.

Appreciate your quick responses!

Thanks!

For( i = 1, i <= N Items( Files ), i++,

For( j = 1, j <= N Items( x ), j++,

If( !Is Missing( Regex( Substr( Files[i], 1, 18 ), Char( x[j] || "_" || y[j] || "_" || z[j] ) ) ),

Open( TestDir || "\" || Files[i] );

Break();

);,

)

);