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
AdamChoen
Level III

Open file from changing folder name

Hi,
Need help to open an excel file from folder with updated name for the current date and time.
E.g 23_5_18_234415

I have a way to create a string with the current date but the time is very pin point and can't be evaluated so I need some kind of wildcard to the string.

Any help will be appreciated
Thanks, Adam
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Open file from changing folder name

Here is a simple script that I think will give you an idea of how to figure out what file(s) meet your needs

Names Default To Here( 1 );
folder = Pick Directory();

// Get all of the files in the folder
FileList = Files In Directory( folder );

// Create the known part of the files to look for
Header = Char( Day( Today() ) ) || "_" || Char( Month( Today() ) ) || "_" ||
Char( Year( Today() ) - 2000 ) || "_";

// Loop through the found files and eliminate all that do not
// match the pattern we are looking for
For( i = N Items( FileList ), i >= 1, i--,
	If( Left( FileList, Length( Header ) ) != Header,
		FileList = Remove( FileList, i, 1 )
	)
);
Jim

View solution in original post

4 REPLIES 4
txnelson
Super User

Re: Open file from changing folder name

Here is a simple script that I think will give you an idea of how to figure out what file(s) meet your needs

Names Default To Here( 1 );
folder = Pick Directory();

// Get all of the files in the folder
FileList = Files In Directory( folder );

// Create the known part of the files to look for
Header = Char( Day( Today() ) ) || "_" || Char( Month( Today() ) ) || "_" ||
Char( Year( Today() ) - 2000 ) || "_";

// Loop through the found files and eliminate all that do not
// match the pattern we are looking for
For( i = N Items( FileList ), i >= 1, i--,
	If( Left( FileList, Length( Header ) ) != Header,
		FileList = Remove( FileList, i, 1 )
	)
);
Jim
AdamChoen
Level III

Re: Open file from changing folder name

Jim,
thanks for your help,
I don't know the pick directory function can it run non-prompt? my script should run daily in the background no user involvement

thanks  

Thanks, Adam
txnelson
Super User

Re: Open file from changing folder name

You did not indicate how you would specify which directory to get the files from, so I just used Pick Directory().  I am assuming that you just have a standard folder to go to, and in that case you can just bypass the Pick Directory() with:

folder = "<your folder path>";
Jim
gzmorgan0
Super User (Alumni)

Re: Open file from changing folder name

Adam,

 

I am not sure if this matches your scenario, but I had a similar problem a few years ago.  On a file server, a new directory was created each day, and that folder contained the files for that day. The problem was that the time was not consistent. The time of the folder (directory) name  was the datetime when the first file for that day arrived. Hence, the specific time was not known.

 

The simplest JSL command was to use folders = Files in Directory( "path") and do not use recurse. Then use JSL to search the folder that started with or contained today's date.  That worked perfectly, however, the server had "bazillions" of folders and the performance was pretty slow.  I work with a Windows operation system, so I used Run Program with the cmd.exe to run a custom dir command. For example,  this  MS Dir command

      dir /a:d /o:-d c:\temp\jsl* 

would return all folders (/a:d all directories) listed from most recent to oldest (/o:-d order reverse date) that exist in my c:\temp directory whose folder name starts with jsl. So I could use the current day's date to narrow the list.

 

For my "bazillion" folder problem, the directory name strucure I had to search for was like 20180524*something_somethingelse. because there were times a backup would restart the folder build, sometimes I would get 2 and very rarely 3 values in my list. But because it is reverse order, the first item in my list was the target folder. 

 

In my script, once I got the target folder, then I would use Files In Directory (), now that I had the directory with the specific, unknown, varying time, and process each file and create a report, all remotely (in batch).

 

If you are on a Mac, I can only offer Jim's suggestion which is terrific. For each file, in the list returned by Files inDirectory() search for those that contain today's date and if more than one, get the creation date and find the latest one.   

 

If you are using Windows, and if you are dealing with a performance issue, here is a script that shows how to get all folders in your JMP install directory.  [Note for something as simple as this example, Files in Directory() is the best solution.]

// Get the full path for the JMP Install Directory
jmpDir = Convert File Path( "$JMP_Home", Windows );
Show( jmpDir );

// Build the DOS "dir" command
// dir /-C /T:C /S "C:\Program Files\SAS\JMPPRO\13\Samples\Data\*.J*" > c:\temp\dirOut_rp.txt
// --- DOS requires quoted file paths if the path includes spaces Program Files has a space

//this has no output file and /b only the directory name is returned
cmd_txt ="\[/c dir /a:d /b /-c /o:-d "]\"  
 || jmpDir     //will replace it with the full path of your installed JMP directory
 ||"\[* ]\";  
 
show(cmd_txt ); //see the log file

//results returned as text
rpobj = RunProgram(Executable("CMD.EXE" ), 
    Options({Eval(cmd_txt)}),
    ReadFunction("text") 
);

show(rpobj);

if(length(rpobj) >0, dirList= words(rpobj,"\!N") );
show(dirList);
/*
{"ExcelProfiler", "imagemaps", "Tip of the Day", "Help", "Resources", "pptx",
"Flash", "Documentation", "tkext", "Scoring", "Samples", "Web", "Maps"}
*/ 

//____________________________________________________________________________________________

//alternate command to return information to a file
outDir = "c:\temp\dirOut_rp.txt";
cmd_txt ="\[/c dir /a:d /b /-c /o:-d "]\"  
 || jmpDir                      //will replace it with the full path of your installed Sample Data directory
 || "\[*" > ]\"
 || outDir;
 
show(cmd_txt ); //see the log file
t0 = Today();

rpobj = RunProgram(Executable("CMD.EXE" ), 
    Options({Eval(cmd_txt)}),
    ReadFunction("text") 
);

//--- ReadFunction is reading statements from the OS, it reads until the program quits.
//    This command suspends subsequent JSL until executable has completed the tasks. 



//==== Show results=============================================================================

// A couple of "checks" that you may employ here if the file does not does not exist or if it is older than expected
If( !File Exists( outDir ) | (Creation Date( outDir ) - t0 > 10),
	Caption( "There was a problem with the dir command, it did not run. Aborting..." );
	Wait( 4 );
	Caption( Remove );
	Throw();
);

// Open the results file as text stored in column 1
dir_dt = Open(
	outDir,
	columns( Name( "Dir results" ) = Character ),
	Import Settings(
		End Of Line( CRLF ),
		End Of Field( CRLF ),  // ensures only 1 column
		Strip Quotes( 1 ),
		Use Apostrophe as Quotation Mark( 1 ),
		Scan Whole File( 1 ),
		Treat empty columns as numeric( 0 ),
		Labels( 0 ),
		Data Starts( 1 ),
		Lines To Read( All ),
		Year Rule( "10-90" )
	)
);