Need help creating batch files by comparing appended csv files

Get help with using AutoHotkey and its commands and hotkeys
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Need help creating batch files by comparing appended csv files

24 Jan 2020, 15:26

I am trying to write a script that will create unique batch csv files by looking at only the new data being added to an appended csv file. I have attached three sample files for reference.

Each file is a successive iteration of the same file with new appended data saved to a file called "Log.csv" by a barcode scanner that updates the file every 1-4 minutes.

The "-01" file is the first save, the "-02" file is the second save, and the "-03" file is the third save of the same file named "Log.csv" as it is updated by the barcode reader. What I would like is a script that creates a batch file of just the new data added since the last update, so I envision something that is looking for changes in the "Log.csv" file (say once every minute) and each time new data is detected, it creates a new unique batch file with just the new data and saves it to a specific network location. If no new data is detected, no batch file would be created and of course the first file of the day would be the first batch. Since the file is appending, I know it is just a matter of comparing the number of lines in each file and then creating a new file with just the data from added lines. So, if the previous file has 20 lines of data and current file has 26, the batch file would contain the last 6 lines from the current file. There is a header in the log file, but the batch file does not need a header.

If you have any ideas or code samples, I would greatly appreciate it. I am very new to Hotkey and scripting, but so far my experience with this help forum and AHK has been very good!

Thank you in advance for any help!

Chris70
Attachments
Log-01.csv
(377 Bytes) Downloaded 23 times
Log-02.csv
(546 Bytes) Downloaded 24 times
Log-03.csv
(685 Bytes) Downloaded 15 times
just me
Posts: 7305
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: Need help creating batch files by comparing appended csv files

24 Jan 2020, 16:59

For example, using a timer:

Code: Select all

#NoEnv
#Persistent
CheckPeriod := 1 * 60 * 1000 ; check every 1 minute
FilePath := "Full file path of Log.csv"
PrevLength := 0
SetTimer, CheckFile, %CheckPeriod%
CheckFile:
If !(FO := FileOpen(FilePath, "r")) {
   MsgBox, 16, Error!, Could not open %FilePath%!
   Return
}
If (PrevLength)
   FO.Pos := PrevLength
Else
   FO.ReadLine() ; skip the header
NewLines := FO.Read()
MsgBox, %NewLines%`n`n... or do what you want to do!
PrevLength := FO.Length
FO.Close()
Return
*not really tested*
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Re: Need help creating batch files by comparing appended csv files

24 Jan 2020, 17:30

Just me -

Thank you for the post. I will test out what you provided. I did not see where this saves just the new data (data lines added between the "CheckPeriod" duration) as a unique separate file with each iteration of discovered new lines. It looks to just fire a message box when new lines are detected between saves, correct? Is there some additional code that could save just the new lines of data to a separate unique .csv file? Like maybe something in the format of Log-{date}-{time}.csv?

Thanks!

Chris70
User avatar
flyingDman
Posts: 863
Joined: 29 Sep 2013, 19:01

Re: Need help creating batch files by comparing appended csv files

24 Jan 2020, 17:55

This will check every 10 seconds for a new log file. I presume the log files will be numbered sequentially 01, 02, 03 etc. (what happens at 99 ?).
It will calculate the highest number (max), read and calculate the number of lines of the previous highest file (prev) and append only the newly added lines to a file called inc_log-XX.csv. So if log-46.csv exists, inc_log-46.csv will be created (unless inc_log-46.csv already exists). Give it a try!.

Code: Select all

settimer, label1,10000
return

label1:
max := 0
loop, files, log-*.csv
	max := max(regexreplace(A_LoopFileName, ".*(\d{2}).*","$1"),max)
max := SubStr("00" max, -1)
prev := SubStr("00" max-1, -1)
fileread,oVarA,Log-%prev%.csv
fileread,oVarB,Log-%max%.csv
rowsA := strsplit(oVarA,"`n").length()
if !FileExist("inc_log-" max ".csv")
	{
	for x,y in strsplit(oVarB,"`n")
		if (x>=rowsA)
			log .= y "`n"
	fileappend,% trim(log, "`n"), inc_log-%max%.csv
	}
return
edit: if max is 1 the new incremental (inc) file is a copy of of log-01.csv. and the script needs to be adjusted for that (minor change required).
just me
Posts: 7305
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: Need help creating batch files by comparing appended csv files

26 Jan 2020, 07:44

Chris70 wrote:
24 Jan 2020, 17:30
... Is there some additional code that could save just the new lines of data to a separate unique .csv file? Like maybe something in the format of Log-{date}-{time}.csv? ...

Code: Select all

#NoEnv
#Persistent
CheckPeriod := 1 * 60 * 1000 ; check every 1 minute
FilePath := "Full file path of Log.csv"
SplitPath, FilePath, Name, Dir, Ext, NameNoExt
PrevLength := 0
SetTimer, CheckFile, %CheckPeriod%
CheckFile:
If !(IF := FileOpen(FilePath, "r")) {
   MsgBox, 16, Error!, Could not open %FilePath%!
   Return
}
If (IF.Length <> PrevLength) {
   If (PrevLength)
      IF.Pos := PrevLength
   Else
      IF.ReadLine() ; skip the header
   NewLines := IF.Read()
   If (NewLines) {
      MsgBox, %NewLines%`n`n... or do what you want to do!
      OutDate := SubStr(A_Now, 1, 8)
      OutTime := SubStr(A_Now, 9, 6)
      OutPath := Format("{:}\{:}-{:}-{:}.{:}", Dir, NameNoExt, OutDate, OutTime, Ext)
      If !(OF := FileOpen(OutPath, "w")) {
         MsgBox, 16, Error!, Could not open %OutPath%!
         Return
      }
      OF.Write(NewLines)
      OF.Close()
   }
   PrevLength := IF.Length
}
IF.Close()
Return
? (*not tested*)
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Re: Need help creating batch files by comparing appended csv files

27 Jan 2020, 12:16

FlydingDman -

Thanks for the reply. To answer your question:
FlyingDman: I presume the log files will be numbered sequentially 01, 02, 03 etc. (what happens at 99 ?).
Actually, the log file will have the same file name. The log file is being written to by a barcode reader application and it is simply appending new data to the log file with the same name (overwriting and appending new lines of data with each save). So, for this script, I am looking for it to create batch files from just the new lines being added to the same log file with each running of the script. So, the first time the script runs, the batch file would be the same as the log file (since there would be no previous log file to compare to upon initial execution) except it would remove the first row (the header). On each iteration, the script would take a snapshot of the log file starting with the first run, then on the second run it would compare the current log file contents to the first furn snapshot that was taken previously and determine if any new lines were added. If so, it would create a batch file with just the new lines and save it with a unique file name, such as "Log-{date}{time}.csv" where the date and time suffix would be added to each batch file as it is created ensuring its uniqueness for processing. After creating a batch file, a new snapshot of the log file would be created to compare to the next round and so on. If the comparison yields no differences, no log file would be created. Does that make sense?

Thanks!

Chris70
User avatar
flyingDman
Posts: 863
Joined: 29 Sep 2013, 19:01

Re: Need help creating batch files by comparing appended csv files

27 Jan 2020, 14:00

Try this:

Code: Select all

settimer, label1,1000					; updates every second 
gosub , label1
return

label1:
fileread,oVar,Log-01.csv
arr := strsplit(oVar,"`n")
newrows := arr.length()
if (newrows = oldrows)
	return
list := ""
for x,y in arr
	if (x=1) OR (x>=oldrows)
		list .= y "`n"
Progress, m2 b fm10 zh0 x750 y500 WM1 CWFFFFFF w400 h200, ,%list%               ; delete if you do not want screen updates
stamp := Format("{:04}{:02}{:02}_{:02}{:02}{:02}", A_Year, A_Mon, A_DD, A_Hour, A_Min, A_Sec)              
Fileappend, %list%, Log-%stamp%.csv
oldrows := newrows
return
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Re: Need help creating batch files by comparing appended csv files

27 Jan 2020, 15:47

FlyingDman -

Your script worked very well and yielded the batch files like I was looking for in my problem description. Thank you SO MUCH for that.

Quick question about your script (and scripts in general as I am very new to them), suppose I want the log file to be a defined variable that I can update either in each script that uses it (or can variables be set globally for use in all scripts?). For example, in the line:

Code: Select all

fileread,oVar,Log.csv
I would want the "Log.csv" to be a variable that I update to whatever log file is being used for the process on a given day (currently manually updating the log file name and starting a new file each morning). Right now I have two scripts looking at the same log file, this would be the third. And, each day I have to manually update each script to look at the new file. I know this is not the best way to do it, but I am not sure of a better way to index the file name in all of the scripts. Can you provide any guidance there?

Thanks!

Chris70
User avatar
flyingDman
Posts: 863
Joined: 29 Sep 2013, 19:01

Re: Need help creating batch files by comparing appended csv files

27 Jan 2020, 16:05

You can use something like this:

Code: Select all

logfilename := "Log-01.csv"
;...
fileread,oVar,%logfilename%
To "store" the filename in a file use https://www.autohotkey.com/docs/commands/IniWrite.htm and https://www.autohotkey.com/docs/commands/IniRead.htm. The .ini file remains the same but the log file name listed in it can be changed. Your various scripts can then read the same .ini file.
User avatar
flyingDman
Posts: 863
Joined: 29 Sep 2013, 19:01

Re: Need help creating batch files by comparing appended csv files

27 Jan 2020, 16:14

As the number of rows as calculated by the script depends on whether or not the log file includes a linefeed and/or carriage return, it is better to remove these. You can do that using Rtrim(oVar,"`n`r" so that your script becomes

Code: Select all

label1:
fileread,oVar,%logfilename%
arr := strsplit(Rtrim(oVar,"`n`r"),"`n")
;...
and the line if (x=1) OR (x>=oldrows) should be if (x=1) OR (x>oldrows)
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Re: Need help creating batch files by comparing appended csv files

28 Jan 2020, 13:29

Just Me -

Thank you very much for the sample code for the batch file. I am currently testing the code as shown below and it is working very nicely on the live log data (been running about . I did comment out the message boxes (not needed) and added a line to save the batch files to a network location (commented out your original and added mine). My only problem is that I still get the message popup for if it cannot open the source log file (which happens occasionally because multiple scripts may be accessing it). Is there a way to open the file read only so it won't interfere and get the file open error? I assume I could change the "w" to an "r" in the

Code: Select all

If !(OF := FileOpen(OutPath, "w")
line, but I wasn't sure.

Below is the full script I am testing:

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.

#Persistent
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
#SingleInstance force

;FileName		:= "Scan Log 01-28-2020"
;SourceLogFile	:= "R:\Tolleson\DuroShare\SW Scan Logs\Scan Log 01-28-2020.csv"
NetworkPath		:= "R:\Tolleson\DuroShare\SW Scan Logs\Batch Files"

CheckPeriod := 1 * 5 * 1000 ; check every 1 minute (60) OR SOME OTHER NUMBER OF SECONDS
;FilePath := A_ScriptDir "\log.csv"
FilePath 	:= "R:\Tolleson\DuroShare\SW Scan Logs\Scan Log 01-28-2020.csv"
SplitPath, FilePath, Name, Dir, Ext, NameNoExt
PrevLength := 0
SetTimer, CheckFile, %CheckPeriod%
CheckFile:
If !(IF := FileOpen(FilePath, "r")) {
   MsgBox, 16, Error!, Could not open %FilePath%!
   Return
}
If (IF.Length <> PrevLength) {
   If (PrevLength)
      IF.Pos := PrevLength
   Else
      IF.ReadLine() ; skip the header
   NewLines := IF.Read()
   If (NewLines) {
;      MsgBox, %NewLines%`n`n... or do what you want to do!
      OutDate := SubStr(A_Now, 1, 8)
      OutTime := SubStr(A_Now, 9, 6)
;      OutPath := Format("{:}\{:}-{:}-{:}.{:}", Dir, NameNoExt, OutDate, OutTime, Ext)
      OutPath := Format("{:}\{:}-{:}-{:}.{:}", NetworkPath, NameNoExt, OutDate, OutTime, Ext)
      If !(OF := FileOpen(OutPath, "w")) {
;         MsgBox, 16, Error!, Could not open %OutPath%!
         Return
      }
      OF.Write(NewLines)
      OF.Close()
   }
   PrevLength := IF.Length
}
IF.Close()
Return
Thanks!

Chris70
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Re: Need help creating batch files by comparing appended csv files

29 Jan 2020, 15:30

@FlyingDman

I tested your script suggestion using test data and it appeared to work fine. It created unique batch files with each appended iteration of the source file. However, when I tested the script on the live log file (that is updating every 1-3 minutes depending on the production throughput on the machine, there is some idle time when nothing is going through), I get different performance for some reason. I have attached a zip file with about a 25 min. worth of batch files created using the script below. For some reason, after the initial file is created (the first one = the full source file) the script throws out a blank file, then it creates a new batch file with the entire source file as a batch (again but with perhaps a new line or two of data), then creates one or two good batch files, then creates another blank file, then kicks out another batch file equal to the full source file (again but with perhaps a new line or two of data), etc. I am wondering what could cause that. The source file is updating once every 1-4 minutes on average and during the time it is updating, there might be a brief moment were the file is in use wile it is being overwritten. Could that be causing the script to behave differently?

Below is a copy of the script:

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
#Persistent
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
#SingleInstance force

FileName		:= "Scan Log 01-29-2020"
SourceLogFile	:= "R:\Tolleson\DuroShare\SW Scan Logs\Scan Log 01-29-2020"
NetworkPath	:= "R:\Tolleson\DuroShare\SW Scan Logs\Batch Files\"

settimer, label1,5000					; update TIMER 
gosub , label1
return

label1:
fileread,oVar,%SourceLogFile%.csv
arr := strsplit(Rtrim(oVar,"`n`r"),"`n")
newrows := arr.length()
if (newrows = oldrows)
	return
list := ""
for x,y in arr
	if (x=1) OR (x>oldrows)
		list .= y "`n"
;Progress, m2 b fm10 zh0 x750 y500 WM1 CWFFFFFF w400 h200, ,%list%               ; delete if you do not want screen updates
stamp := Format("{:04}{:02}{:02}_{:02}{:02}{:02}", A_Year, A_Mon, A_DD, A_Hour, A_Min, A_Sec)              
Fileappend, %list%, %NetworkPath%%FileName%-%stamp%.csv
oldrows := newrows
return
Let me know if you see anything that would cause that or that needs to be added to prevent that.

Thanks!

Chris
Attachments
FlyingDman Batch Test 01-29-2020.zip
(5.81 KiB) Downloaded 17 times
User avatar
flyingDman
Posts: 863
Joined: 29 Sep 2013, 19:01

Re: Need help creating batch files by comparing appended csv files

29 Jan 2020, 18:48

I created a second script to simulate the creation of the "scanner" file and ran my original script. I cannot reproduce the errors. Everything runs smoothly and accurately. I am not running on a network but a standalone laptop PC. Note that I am using fileread rather than openfile . With fileread the content of the file is read and stored in a variable. This, I believe reduces the risk of catching the file when open.
just me
Posts: 7305
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: Need help creating batch files by comparing appended csv files

30 Jan 2020, 04:17

Hi Chris,

the source log file is already opened for reading only.

Code: Select all

CheckPeriod := 1 * 5 * 1000 ; check every 1 minute (60) OR SOME OTHER NUMBER OF SECONDS
You're running the timer every 5 seconds. So it might be sufficient to show the error message after several sequent open failures.

Code: Select all

...
MaxAttempts := 12
FailedToOpen := 0
CheckFile:
If !(IF := FileOpen(FilePath, "r")) {
   FailedToOpen++
   If (FailedToOpen >= MaxAttempts)
      MsgBox, 16, Error!, Could not open %FilePath%!
   Return
}
FailedToOpen := 0
...
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Re: Need help creating batch files by comparing appended csv files

30 Jan 2020, 13:05

@just me

Thanks for the response. I actually just commented out the message boxes in the two IF FileOpen lines and the script has been working perfectly. It just keeps trying to open the file and eventually gets it to open to process the batch file. Since I did that (code below), it hasn't missed a beat during the two live testing sessions I have conducted so far. I really don't care if it is unable to open file each time it tries, but I see the value in putting in a section as you described just to be safe for a set number of attempts. I will test it out.

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
#Persistent
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
#SingleInstance force

;FileName		:= "Scan Log 01-28-2020"
;SourceLogFile	:= "R:\Tolleson\DuroShare\SW Scan Logs\Scan Log 01-28-2020.csv"
;NetworkPath	:= "R:\Tolleson\DuroShare\SW Scan Logs\Batch Files"
NetworkPath		:= "\\Novoshares.Novolex.local\AXShare\RAF_Scanner\INCOMING"	; Network location to copy OutputFile for AX Processing


CheckPeriod := 1 * 5 * 1000 ; check every 1 minute (60) OR SOME OTHER NUMBER OF SECONDS
;FilePath := A_ScriptDir "\log.csv"
FilePath 	:= A_ScriptDir "\Scan Log-BATCH-Test.csv"
SplitPath, FilePath, Name, Dir, Ext, NameNoExt
PrevLength := 0
SetTimer, CheckFile, %CheckPeriod%
CheckFile:
If !(IF := FileOpen(FilePath, "r")) {
;   MsgBox, 16, Error!, Could not open %FilePath%!
   Return
}
If (IF.Length <> PrevLength) {
   If (PrevLength)
      IF.Pos := PrevLength
   Else
      IF.ReadLine() ; skip the header
   NewLines := IF.Read()
   If (NewLines) {
;      MsgBox, %NewLines%`n`n... or do what you want to do!
      OutDate := SubStr(A_Now, 1, 8)
      OutTime := SubStr(A_Now, 9, 6)
;      OutPath := Format("{:}\{:}-{:}-{:}.{:}", Dir, NameNoExt, OutDate, OutTime, Ext)
      OutPath := Format("{:}\{:}-{:}-{:}.{:}", NetworkPath, NameNoExt, OutDate, OutTime, Ext)
      If !(OF := FileOpen(OutPath, "w")) {
;         MsgBox, 16, Error!, Could not open %OutPath%!
         Return
      }
      OF.Write(NewLines)
      OF.Close()
   }
   PrevLength := IF.Length
}
IF.Close()
Return
Thanks!

Chris70

Return to “Ask For Help”

Who is online

Users browsing this forum: No registered users and 29 guests