What is the most efficient/ fastest way to do this?

[datanibbler]

Hi,

- I have a directory with approx. 45.000 files in it, most of them jpg files of a specific format - unfortunately not all, but most of them are

   uniformly formatted.

- On the other side, I have a list of approx. 4.500 item_numbers which are the most important to process (out of these 45.000 - I have to

   process them all, but these 4.500 are more pressing than the rest)

=> I am looking for the most efficient way to do this:

- I have to loop through all those 45.000 files, find out whether one is present in my shortlist and if yes, use an EXECUTE command to

   move it to another directory for processing in the next step (which I'm not thinking about right now).

The EXECUTE thing is not a problem. The only hurdle there is is finding out whether the current file (in the loop) figures in the shortlist.

I guess one way to do this would be a LOOKUP function. I haven't worked with that yet, but it's the only way I can think of.

<=> Is that efficient/ fast?`Speed of execution becomes relevant with this number of files.

Can you either confirm that this is a good way to do it or propose another one?`

Thanks a lot!

Best regards,

DataNibbler

[Peter_Cammaert · Accepted solution]

A quick one, to get you started:

• use a FOR EACH loop to build a list of available files. The names probably contain some ID that can be connected to the shortlist.
• Perform an INNER JOIN of newly-created file list with shortlist. The resulting set will be the ones that need to be moved
• Loop through the result list and perform an EXECUTE for each entry.

Useful?

Peter

[datanibbler]

Hi Peter,

quite so.

Generally, I am doing it in exactly that way. Only I was once again imagining the thing too complicated - I was trying to come up with a way to do like a lookup in that table within every single iteration of the loop, but now I see I don't even have to do this - it is quite certain that these 4.500 item_numbers exist in that list of files, but I can still check it first - then I just have to build a cmd command for the EXECUTE and that will pick the right file all by itself.

Thanks a lot!

Best regards,

DataNibbler

[marcus_sommer]

Hi DataNibbler,

I think there aren't many alternatives to your approach and other tools and/or methods won't be faster so that a stable solution is more important then speed and I believe the biggest bottleneck will be your network and how fast could it copy/move/rename your files. This meant you might need sleep-statements and/or checking-routines that your execute-statement will be executed successfully (you will get soon error-messages if you already starts the hundreds execute-statement if windows is yet storing the tenth file).

To come to your check file from filelist to your table-itemNumber beside the lookup might fieldvalue a faster alternatively, maybe in this way:

fieldvalue('item_number', fieldindex(FileName, '$(File)'))

- Marcus