0

I had an outside company write a "file watcher" program in C#. The program reads an excel sheet that contains a list of file paths of folders to watch. The "file watcher" program watches to see if any new text (.txt) files appear in each of the folders that it is watching. If there is a new text file, it opens an excel sheet, which contains a macro and runs the macro and then closes the sheet. Everything works great... Except... The text files are being automatically generated from a CMM (Coordinate Measuring Machine).

Side Note: Although, I have had a few programming classes (Python, Matlab, VBA for Excel). I am by no means a programming expert, nor a computer expert...

The CMM seems to create an empty .txt file (It shows up as 0 kb) then the CMM appears to dump some data every couple minutes. As a result, the text file will show 7 kb, then 15 kb... etc... I can click on this text file at any of these stages and it will open but only shows the text proportional to the kb of completion. If I close the text file and then re-open it, there is usually more completed. The CMM (if you aren't familiar) is measuring parts and reporting the measurements via these text files. Some parts take 15 seconds to measure (only a few measurements), some take 45 minutes (Many, many, measurements). Although I haven't confirmed this, there is a "Yes" / "No" dialog box that pops up for the CMM operator to click when the part is finished being measured and I think it doesn't finish writing the last chunk of the text file until the operator make a "Yes" or "No" selection. The operator could make this selection in 10 seconds, or 30 minutes (if they go to lunch or something). In fact, on a Friday, they could go home and not make a selection until Monday.

The problem I am having is that the C# program sends the file path of the text file to the excel macro before the text file is finish. In my mind, I need the C# program modified to "wait" for the text file to be complete before sending the file path of the txt file to the excel macro. Is this possible? Do text file contain like a EOF (End of File) marker of some sort?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
XCELLGUY
  • 179
  • 2
  • 12
  • 1
    That is a lot of text to read. – mjwills Sep 03 '19 at 12:49
  • 4
    For these kinds of problems, the usual solution is not to write to the target folder. Instead write to a **temporary** folder and then once the file is finished being written to then **move** it to the target folder. That way the file watcher only sees the file when it is ready to be used. _There are other ways to solve it - but that is generally the easiest way._ – mjwills Sep 03 '19 at 12:50
  • That sounds simple, however I am not sure if that is possible because the CMM may not have that capability. Which would mean I would need a custom program made to do that... which is the same boat I am already in. – XCELLGUY Sep 03 '19 at 12:54
  • 3
    Try this: If you attempt to open the file in exclusive mode while the CMM is active, does the open fail? If so, you have your solution: Keep attempting to open the file in exclusive mode until it succeeds (with a delay of a second or so inbetween attempts). (Eg. try to open the file like `File.Open("filename", FileMode.Open, FileAccess.ReadWrite, FileShare.None)`) - although note that if the CMM opens the file in share mode, you will be able to open it successfully and this approach would not work. – Matthew Watson Sep 03 '19 at 12:56
  • @mjwills solution is the route I normally take. Another route would be to have them add something like #EOF at the end of the .txt file when it is complete, or if you can compare the last write time to the current time and then compare to a max time span, and if it exceeds that time span process the file. – Kevin Sep 03 '19 at 13:11
  • Again... mjwills method assumes the CMM program has that capability, which I doubt. Otherwise, It involves writing a program that is capable of identifying when the file is finished (which is what I am already requesting). Kevin's, method assumes that I can use "time" as an indicator. However, as I mentioned above, time can vary and even worse, the CMM program may be changed every once in a while to measure more points or less points(which would change the length of time required to run). I will investigate Matthew Watson's idea of "exclusive mode," although I'm not familiar with what this is? – XCELLGUY Sep 03 '19 at 13:35
  • As a last resort, I was thinking about using the fact that the text files end with "End of Report." However, it is possible that the CMM may stop partially through writing a text file and never finish it and therefore never have "End of Report." One last thing I forgot to mention, there is a website, "https://www.datamystic.com/filewatcher" and it sells a software that says "With File Watcher, files are only processed when they have finished being written to disk - so huge video files (100MB - 10GB or more) will not be processed prematurely." That kind of sounds like what I need? – XCELLGUY Sep 03 '19 at 13:44
  • "File share mode" is defined by the first process that opens a file - see [the FileShare enum](https://docs.microsoft.com/en-us/dotnet/api/system.io.fileshare?view=netframework-4.8) for details. You would need to use `File.Open("filename", FileMode.Open, FileAccess.ReadWrite, FileShare.None)` to attempt to open the file to see if it is still "locked" by CMM. However, like I said: If CMM doesn't open the file using the Windows API equivalent of `FileShare.None` or `FileShare.Read`, then this approach won't work because you will still be able to open the file, even though CMM already has it open – Matthew Watson Sep 03 '19 at 13:54
  • @XCELLGUY _"files are only processed when they have finished being written to disk"_ sounds like marketing blah blah. You can do that if you have a file that is created, then sequentially written to, then closed. I _guess_ that's not what your CMM is doing. From what you write, I expect it to create, write, close, open, write(append), close ... and so on. So the software _may_ detect the _first_ write finish. But maybe I am wrong - I'd be sceptical, though. – Fildor Sep 03 '19 at 14:55
  • Just a thought on "exclusive open": What if CMM is _not_ finished with the file, but has released it temporarily? Then _you_ open it exclusively and CMM will be denied access => boom. – Fildor Sep 03 '19 at 14:58
  • `then the CMM appears to dump some data every couple minutes.` What is the maximum interval of this wait time? – Siddharth Rout Sep 03 '19 at 14:59
  • Here is another way of handling this depending on wait time between two "writes". Let's say the wait time is `2 mins`. So in our code we will use `X = wait time + 1 min buffer`. Hence in this case `X` will be 3 mins. Now we follow these steps. **[1.]** The moment a file appears, wait for X mins **[2.]** After X mins, get the file size and store it in a variable. Again wait for X mins **[3.]** After X mins, check the file size. If the size is the same, work on it else update the variable with the new size and wait for X mins. **[4.]** Repeat Step 3 till the file size is constant. – Siddharth Rout Sep 03 '19 at 15:09
  • I'll be upfront with you - if you choose any solution other than the one I suggested, you'll be kicking yourself in 6 months. :) – mjwills Sep 03 '19 at 21:51

2 Answers2

1

If you cannot use any of the tricks in the comments, you should read the file contents into your C# program and save it as Excel or as a different text file only if "End of Report" is detected. If not, simply start over one minute later.

Maybe (I'm not sure how complicated this would be) you can read the file as a stream and receive the fresh data as soon as it is written. So you can wait for the file to complete and you don't have to read it over and over again.

Wolfgang Jacques
  • 769
  • 6
  • 15
0

Here is the code i found to get me by...

Dim cmdLine As Object
Dim result As String
Dim SearchStr As String
Dim FilePath As String  

Set cmdLine = CreateObject("WScript.Shell")
result = cmdLine.Exec("%comspec% /C Find " & SearchStr & " " & Chr(34) & FilePath & Chr(34)).STDOut.ReadAll
XCELLGUY
  • 179
  • 2
  • 12