Directory tool gives duplicate file names starting with '~'

[jimmke7]

Hi community,

I've the task to load hundreds of planning files that are in xlsb format. Im using the macro and instructions that are given in this article here. To see if the macro works and reduce runtime I only load the planning files for december, which are 10 xlsb files. 1 for each department. When i run the macro from the article I get errors that it cant find the files in the appdata/local/temp folder. After some research on why it can't find several files I found out why:

The macro creates a file in the temp folder for each batchID. Each planning file receives 1 batchID which means the macro would be searching for only 10 file in the appdata/local/temp folder. But this is not the case, it will search for 15 files and will only find the first 10. After researching why it is searching for 15 files I saw that the directory tool outputs 15 record of xlsb files while there are only 10 in the folder. Here is where my question is starting:

I've made a new worflow where only the directory tool is present. I entered the file path to the folder where the 10 xlsb planning files are stored. See the picture below a screenshot of the files in the folder:

In the configuration of the directory tool I entered the file path of the folder and inserted the following file specification: *.xlsb
I would expect that the directory tool will output 10 records: 1 for each xlsb file.

But the truth is that the directory tool outputs 16 records. Where the first 10 records are the record that I expect to receive and the last 6 records being a sort of duplicates. See the screenshot below the output of the directory tool:

Does anyone know why the directory tool creates these 6 last records starting the file name with ~?

If you have any idea, please let me know.

Thank you beforehand,
Jimmy

[Emil_Kos]

Hi @jimmke7,

I am not an expert on this but maybe those files are opened and those are like duplicate files that you are working on? 

[Emil_Kos]

@jimmke7,

Actually, those might be some kind of version control files. Those probably are the older version of the files. You can always use the filter tool to remove them from your data. They will always start with ~ so if you will use the filter tool and remove everything that starts with ~ it shouldn't be a problem for you. 

[jimmke7]

Hi @Emil_Kos ,

I also thought using a filter would be a valid solution and it is. The workflow now works completely fine without errors. But I was still wondering why it produces these 'duplicate' records. That those are version control files could be the answer.

Thank you for your reaction!

[Emil_Kos · Accepted solution]

Hi @jimmke7,

Actually I have tested it for you and it looks like it is created when the file is opened:

When I close the file the second line isn't there:

If this was helpful please mark my post as an answer.

Good luck!

[jimmke7]

@Emil_Kos 

It's weird because I do not have any of the files open. I did copy the files from a network drive and pasted it in my local storage. Maybe it kept the status that the files are opened? No idea what's wrong with the files but good to know that the problem occurs when a file is still opened.

I will accept your answer in a few hours if there are no better answers coming in. Thankyou for your input

[Emil_Kos]

Hi @jimmke7,

Thank you. 

It is strange that it behaves like this but probably those are the auto recovered versions of those files. I tried to google it for you but I didn't found any reliable answer. 

[danilang]

Hi @jimmke7 

The files of the form ~filename.xls are control files that Excel uses to track information about open files.  It stores the name of the user that has the file open and a few other details.  This is the file that's used by Excel to display the "This file is open by User XXX.  Do you want to open read-only..." message.  Unfortunately, these files don't always get erased, for various reasons, especially with older version of Excel, leading to the "duplicates" in your directory.

Dan