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

[#1]

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]

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

[#3]

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

        )

    );

);

[#4]

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