Subscribe Bookmark RSS Feed

Importing & Concatenat​ing CSV files based on their Last Modificati​on Date

jpol

Community Trekker

Joined:

Oct 11, 2015

Hi,
I am a new JMP12 user and I have a need to concatenate several hundred *.CSV files every day in order to monitor a manufacturig tool's performance.
Now that the tool has been in use for several months I currently have ~ 75 000 files on the directory from where I need to read the files.
All files need to remain on this directory.
I need to monitor the tool over the last 30 days or so. Opening and concatenating the latest 2000 files on a daily basis would meed my needs.
How can I add to the   filelist   to be opened, based on the files' Last Modification Date?
I would like to add an additional time based filter to those already in use:
filepath = "K:\...\PROBER_01\" ;
prefilelist = Files In Directory( filepath );
n2=nitems(prefilelist);
//Provide filters to ensure appropriate csv files
For( i2 = 1, n2 >= i2, i2++,
file=(prefilelist[i2]);
If
( word( 3, prefilelist[i2], "_" ) == "DRIE"
& word( 2, prefilelist[i2], "_" ) == "ILGO1"
& substr(prefilelist[i2],5,1 ) == "L"
& substr(prefilelist[i2],1,1 ) == "G",
Insert Into( filelist,file),
show(file)
)
);
something like :
& Last Modification Date() > today()-30 days    ??????
Thanks,
Philip
2 REPLIES
ms

Super User

Joined:

Jun 23, 2011

That should work well with minor modification. I am not sure what is the most efficient; to first filter on date and then on the name, like in the example below, or in the reverse order. You could try both and compare the performance.

filepath = filepath = "K:\...\PROBER_01\";

prefilelist = Files In Directory(filepath);

n2 = N Items(prefilelist);

datelimit = Today() - In Days(30);

filelist = {};

For(i2 = 1, i2 <= n2, i2++,

    file = prefilelist[i2];

    If(Last Modification Date(filepath || file) > datelimit,

        If(

            Word(3, file, "_") == "DRIE" &

            Word(2, file, "_") == "ILGO1" &

            Substr(file, 5, 1) == "L" &

            Substr(file, 1, 1) == "G",

            Insert Into(filelist, file);

            Show(file);

        )

    );

);


jpol

Community Trekker

Joined:

Oct 11, 2015

Hi MS,

Your solution works very well thank you.

FYI:

I made two versions for testing purposes as you suggested:

the first version creates the filelist in 1 minute 50 seconds.

filepath = "K:\...\PROBER_01\";

prefilelist = Files In Directory(filepath);

n2 = N Items(prefilelist);

datelimit = Today() - In Days(30);

filelist = {};

For(i2 = 1, i2 <= n2, i2++,

  file = prefilelist[i2];

   If(Last Modification Date(filepath || file) > datelimit,

   If(

   Word(3, file, "_") == "DRIE" &

   Word(2, file, "_") == "ILGO1" &

   Substr(file, 5, 1) == "L" &

   Substr(file, 1, 1) == "G",

   Insert Into(filelist, file);

   Show(file);

   );

);

the second version creates the same  filelist in 20 seconds.

filepath = "K:\....\PROBER_01\";

prefilelist = Files In Directory(filepath);

n2 = N Items(prefilelist);

datelimit = Today() - In Days(30);

filelist = {};

For(i2 = 1, i2 <= n2, i2++,

  file = prefilelist[i2];

   If(

   Word(3, file, "_") == "DRIE" &

   Word(2, file, "_") == "ILGO1" &

   Substr(file, 5, 1) == "L" &

   Substr(file, 1, 1) == "G",

   If(Last Modification Date(filepath || file) > datelimit,

   Insert Into(filelist, file);

   Show(file);

   );

);

Best Regards,

Philip