New to AHK & script writing, need help on project

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Re: New to AHK & script writing, need help on project

02 Jan 2020, 17:50

TLM -

Thanks for the recent post. I hope you enjoyed your holiday time off! I have been using a modified version of your previous version to save a copy of the .csv file every 3 min. of the current log file. I have been simply stopping the scripts in the morning, manually renaming the file to the current date, then restarting the scripts (I have one I wrote which clicks the save icon every second on the scanner program since that is currently the only way to get it to save, different issue entirely, and the one you created with some minor tweaks). It seems to work in the background and hasn't given any errors or stopped running in the week since I started using it (except when they cut the power for the New Year shutdown, of course). I think that means that we can work with the log file while it is updating, so that test is done. Here is what I am using for this:

Code: Select all

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

LogFile 	:= "C:\Users\chickman\Documents\terminal-01-02-2020.log" 	; points to the log file
OutputFile 	:= A_ScriptDir "\Scan Log 01-02-2020.csv"	; points to the output file
StdPltQty 	:= A_ScriptDir "\Std pallet qty-loc.xlsx" 	; points to the standard quantity file
CopyToFile      := "R:\Tolleson\DuroShare\SW Scan Logs\Scan Log 01-02-2020.csv"

CSVHead 	:= "Pallet ID,Quantity,Product ID,Loc. Code"

; Checks to make sure there's either the log file
; or Standard Quantity files(s) exist
s := FileExist( StdPltQty ), l := FileExist( LogFile )
if (!s||!l)
{
	MsgBox, 0x10, Whoops!, % "Cannot Locate The " ( (b:=!s&&!l) ? "Log & The Standard Quantity" 
	: !s ? "Standard Quantity" : !l ? "Log" : "" ) " File" (b?"s":"") "!`nPoint To Or Install "
	. (b?"Them":"It") " & Try Again!"

	return
}
Loop 
{
if FileExist( OutputFile ) 	; if output file exists...
	FileDelete % OutputFile ; ...delete it

if ( !IsObject( xlObj ) )
{
	xlObj := ComObjCreate( "Excel.Application" ), xlObj.Visible := False ; Reads the Standard Pallet Quanity Location worksheet
	xlObj.Workbooks.Open( StdPltQty )
}	

Loop, Read, % LogFile, % ( OutputFile, PidArray := [] ) ; Reads & Loops through lines of logfile
{
	FileAppend % ( A_Index = 1 ? CSVHead "`n" : "" ) ; add the column headings

	Match := RegExMatch( A_LoopReadLine, "^.*?(?<pid>RO\d{11})", pallet_ ) ; looks for 13 char pallet ID in log line

	if ( pallet_pid ) ; if 13 char pallet ID detected...
		PidArray.push( pallet_pid ) ; ... push to pallet id to array

	if ( Match && ( !InStr( A_LoopReadLine, PidArray[ PidArray.length()-1 ] ) || PidArray.length() = 1 ) ) ; checks for 13 char RO# & ignores duplicates 
	{	
		pallet_loc := "" ; standard location code reset

		; Regular Expression Array to search for each value
		NeedleArray := [ "[-`,](?<quantity>\d{1,3})\D", "\D(?<prid>\d{5}(-\d{1,3}|))\D", "\D(?<spid>RO\d{8})\D" ]

		For Each, Needle in NeedleArray ; matches each value in current line
			RegExMatch( A_LoopReadLine, Needle, pallet_ )

		QtyRowObj := xlObj.Range( "A1" ).End( -4162 ).Find( pallet_prid, xlObj.Cells( 1 ), -4163, 2, 1, 1, 0, 0 ) ; Finds the pallet quantity based on product ID in Standard Pallet Quantity-Location worksheet

		Try ( pallet_loc := xlObj.Range( "D" QtyRowObj.Row ).Value ) ; Finds the associated location code for each product ID
		Catch
			TrayTip, Warning!, % "Product ID: " pallet_prid " Skipped!`nLocation Code Not Found!",, 0x2

		if ( pallet_spid && !pallet_quantity ) ; short pallet ID & no quantity, fills in the standard pallet quantity if missing from scan
			pallet_quantity := Floor( xlObj.Range( "B" QtyRowObj.Row ).Value )

		FileAppend % pallet_pid "," pallet_quantity "," pallet_prid "," pallet_loc "`n"
	}
}
DetectHiddenWindows, On

xlHwnd := xlObj.Hwnd ; used the grab the Excel client process handle

; The below methods should close the workbook,
; quit ( kill ) the client, and free the object
xlObj.Workbooks.Close
( xlObj.Quit ), xlObj := ""

WinGet, xlPID, PID, % "ahk_id " xlHwnd ; used the get the Excel client process ID
Process, Close, % xlPID ; used the close / kill the Excel client process

if FileExist( OutputFile ) 	; if output file exists...
        FileCopy, %OutputFile%, %CopytoFile%,1

Sleep, 180000
}
return
I will test out the other changes you made tomorrow as well as the file compare function you mentioned and get back to you on those.

Thanks again for all your help!

Chris
User avatar
TLM
Posts: 1608
Joined: 01 Oct 2013, 07:52
Contact:

Re: New to AHK & script writing, need help on project

02 Jan 2020, 21:10

Glad to see you made some mods :thumbup:

However you should now mod the last one I posted as the old script has an inherited object reference bug in it.
Chris70 wrote:
02 Jan 2020, 17:50
....one I wrote which clicks the save icon every second on the scanner program since that is currently the only way to get it to save....
There may be a way to do this very reliably.
In the AutoHotkey directory, you should see Au3_Spy.exe.. start it up or press Win+R and paste in %programfiles%\autohotkey\Au3_Spy.exe

Once the spy starts, mouse over the button, in the spy you may see a ClassNN like this:
Image
In this example, you would then send a ControlClick command to it like this:ControlClick, Button1, Some Window TItle

If this doesn't work, there's another more advanced PostMessage approach that may be able to fire the save command.
For more info on this see the Message Tutorial.
NOTE: You'll have to use another Message Spy like Win Detective, as the old one doesn't work on OS's greater than XP.
gregster
Posts: 8921
Joined: 30 Sep 2013, 06:48

Re: New to AHK & script writing, need help on project

03 Jan 2020, 02:25

TLM wrote:
02 Jan 2020, 21:10
There may be a way to do this very reliably.
In the AutoHotkey directory, you should see Au3_Spy.exe.. start it up or press Win+R and paste in %programfiles%\autohotkey\Au3_Spy.exe
To avoid confusion: in current AHK versions, this program is called WindowSpy.ahk - the original Au3_Spy.exe hasn't been included for some time now.
User avatar
TLM
Posts: 1608
Joined: 01 Oct 2013, 07:52
Contact:

Re: New to AHK & script writing, need help on project

03 Jan 2020, 03:22

@gregster my bad, I still have that and the original au3 spys on my system you're correct.
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Re: New to AHK & script writing, need help on project

14 Jan 2020, 17:43

TLM -

I appreciate the information on how to do the mouse click function for the log save feature. I already had a script for that that I cobbed together from researching other forum posts and AHK help. The hardest part was trying figure out how to reference a window, find the AHK class names and IDs and then the relative mouse position. I am not sure where I found it, but this script helped me find the information needed to ID the information needed to write the script. It is a simple visual tool tip that gives the relevant information about where the mouse is:

I call it "Watch mouse-window.ahk"

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
#Persistent
SetTimer, WatchCursor, 100
return

WatchCursor:
MouseGetPos, xpos, ypos, id, control
WinGetTitle, title, ahk_id %id%
WinGetClass, class, ahk_id %id%
ToolTip, ahk_id %id%`nahk_class %class%`n%title%`nControl: %control%`nXpos: %xpos%`nYpos: %ypos%
return
Once I had the information (Window name "Terminal (Advanced)") and the relative mouse click positions, I wrote the following which just clicks the save icon in the terminal window (which then pops up a window where you can make changes to the file name/location or just hit ENTER for OK to accept the current file name), sends the ENTER key, then clicks save again to register the save. This loops repeatedly until the HotKey ctrl+J is sent to break the loop. This actually serves several functions for me: it updates the scanner log file every 2-3 seconds, it keeps the computer from going to sleep (which would make the mouse clicking script useless), and because it is tying up the mouse, it keeps people from messing with the computer to do other things (this is a dedicated workstation just for this purpose, but anyone with a network password could get on an use it if needed).

I called this one "Keyence Terminal Save.ahk"

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
#Persistent
#SingleInstance Force
if WinExist("Terminal (Advanced)")
{
    WinActivate
    WinWait, Terminal (Advanced), , 3
}
else
    MsgBox, Terminal (Advanced) is not running.
Loop
{
If (Breakloop = 1)
    break
{
    if WinExist("Terminal (Advanced)")
{
    WinActivate
    WinWait, Terminal (Advanced), , 3
}
else
    break
MouseClick, left, 30, 400
    Sleep, 500
SendInput, {Enter}
    Sleep, 500
MouseClick, left, 30, 400
    Sleep, 1000
}
}
^j::
Breakloop = 1
Return
Now, I did test the last version of the parsing script you wrote and it worked just like the first one (ran it on the same log file and set it save to different test output files to compare). I did notice the tooltip window that pops up to show the status of the file being processed, that was a nice add-on feature! I am not sure if that slows down the file processing time or not, but it is definitely nice to have a visual of the script running. I want to set it up to run every 3 minutes (so it will run, wait 3 minutes and run again). I was able to do that with your original script (see my script posted on 1/2/2020), but I am getting turned around somewhere trying to loop the new one entirely the same way. Can you revise the newest one you sent so it will run every 3 minutes?

Also, I would still like to get back to the batch file phase that we started to discuss before I went on vacation (started with my post on 12/30/2019). I did verify that we can use the log file while it is being updated, so I hope that makes the process easier. I see I did not respond to this yet:
Do you want to compare if one output file is entirely different than the previous OR whether there's a line by line difference ?

For instance,
compare if output file 1 contains a particular record ( or records ) ANYWHERE in output file 2?
OR
if each line of both files are the same?
If you look at the sample file I sent in my 12/30/2019 post, I believe this will look at a line by line comparison, but I am looking to only grab the new data from output file 2 that does not exist in output file 1 and create a separate batch file that contains just the new data. This batch file would then be used by our ERP system to post a production receipt. Output file 2 would then become the source to compare against the next output file (3) to create the next unique batch file. Does that make sense? The source log file will be continually appended until it is manually renamed (current process) or we work in a script function to start a new log file (once every 24 hours). The resulting output file that is updated every 3 minutes (under the current method I am using, open to changing it to achieve the desired unique batch file) would be essentially redoing the work of sorting through all of the data again plus any new data added to the log file in the previous 3 minute period. It is probably not the most efficient way to do it, but that's how I have it working right now. I hope that makes sense and I am open to suggestions on how to streamline the data collection log file process side vs. the output file side as well. If you have access to Team Viewer, I can set something up with you to remote in on the computer if that would help. We can also speak on the phone. I am happy to send you my work cell phone through private message if that would work for you as well.

Thanks as always for your help!

Chris
User avatar
TLM
Posts: 1608
Joined: 01 Oct 2013, 07:52
Contact:

Re: New to AHK & script writing, need help on project

16 Jan 2020, 09:55

Sorry for the delay yesterday, I had an exam then had to work..

My point to retrieving the ClassNN ( for the save button ) was to negate having to use coordinate offsets and create a more reliable `save log` approach.
If what you're using is working, let's leave that part. We can revisit that later if necessary.
Chris70 wrote:
14 Jan 2020, 17:43
..I did notice the tooltip window that pops up ... I am not sure if that slows down the file processing time..
I benchmarked the script with/without the tooltip display and noticed no difference in process times.
Chris70 wrote:
14 Jan 2020, 17:43
I want to set it up to run every 3 minutes ... I was able to do that with your original script
... but I am getting turned around somewhere trying to loop the new one entirely the same way. Can you revise the newest one you sent so it will run every 3 minutes?
I deliberately wrapped the script's steps into functions to make it easier to facilitate this purpose. For instance

Code: Select all

Loop
{
	ProcessLogFile( LogFile, StdPltQty, OutputFile, CSVHead )
	Sleep % (1000*60)*3 ; ( 1000 milliseconds * 60 seconds ) * 3  = 3 minutes
}
I also wrapped the sleep duration expression into a function to make it easier for you to make changes if needed. See: Delay := DelayMinutes( 3 )
Try this version ( please read the comments ):

Code: Select all

LogFile 	:= A_ScriptDir "\terminal-12-17-2019.log" 	; points to the log file
OutputFile 	:= A_ScriptDir "\test-output.csv"			; points to the output file
Delay 		:= DelayMinutes( 3 ) 						; how many minutes to wait between loops

StdPltQty 	:= A_ScriptDir "\Std pallet qty-loc.xlsx" 	; points to the standard quantity file
CSVHead 	:= "Pallet ID,Quantity,Product ID,Loc. Code"

Loop
{
	; Read & Sanitize Log, Grab Locations, Save to Output File 
	ProcessLogFile( LogFile, StdPltQty, OutputFile, CSVHead )
	Sleep % Delay
}

; ↑↑↑ YOU ONLY HAVE TO WORRY ABOUT THIS SECTION ↑↑↑

return

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;; Functions
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

ProcessLogFile( lF, sF, oF, cH := "" )
{
	;; Check if log and/or location files exist
	CheckSourceFiles( sF, lF )

	;; Display "Processing..." status
	Display( "Processing..." )

	;; Open Workbook, Return Excel Object
	Global xO := CreateExcelObject( sF )
	
	lFn := GetFileName( lF ), oFn := GetFileName( oF )
	
	;; Reset Output File
	if FileExist( oF )
		FileDelete % oF
	
	;; Assign Array of RegEx needles 
	NeedleArray := 	[ "[-`,](?<quantity>\d{1,3})\D", "\D(?<prid>\d{5}(-\d{1,3}|))\D", "\D(?<spid>RO\d{8})\D" ]

	;; Parse Log File, Save Output File
	Loop, Read, % lF, % ( oF, PidArray := [] )
	{
		;; Add CSV heading if there is one
		if ( A_Index = 1 && cH )
			FileAppend % cH "`n"

		Match := RegExMatch( A_LoopReadLine, "^.*?(?<pid>RO\d{11})", pallet_ )

		if ( pallet_pid )
			PidArray.push( pallet_pid )

		if ( Match && ( !InStr( A_LoopReadLine, PidArray[ PidArray.length()-1 ] ) || PidArray.length() = 1 ) )
		{	
			pallet_loc := ""
			
			For Each, Needle in NeedleArray
				RegExMatch( A_LoopReadLine, Needle, pallet_ )

			QtyRowObj := xO.Range( "A1" ).End( -4162 ).Find( pallet_prid, xO.Cells( 1 ), -4163, 2, 1, 1, 0, 0 )

			Try
				pallet_loc := xO.Range( "D" QtyRowObj.Row ).Value
			Catch
				w .= RTrim( "Product ID: " pallet_prid " Location Not Found!`n" )

			if ( pallet_spid && !pallet_quantity )
				pallet_quantity := Floor( xO.Range( "B" QtyRowObj.Row ).Value )

			Display( RTrim( "Processing log file: " lFn "`n-----`nPallet ID: " pallet_pid
					. 		"`nQuantity: " pallet_quantity "`nProduct ID: " pallet_prid
					. 		"`nLocation: " pallet_loc . e := ( w ? "`n-----`n" w : "" ) ) )

			FileAppend % pallet_pid "," pallet_quantity "," pallet_prid "," pallet_loc "`n"
		}
	}

	;; Display "Complete..." status
	Display( "Processing complete!`nOutput saved to: " oFn . e ), KillExcel()
}

CreateExcelObject( sF, Visible := false )
{
	if ( !IsObject( xO ) )
	{
		Try
		{
			xO := ComObjCreate( "Excel.Application" ), xO.Visible := Visible
			OnExit( "KillExcel" ), xO.Workbooks.Open( sF )
		}
		Catch Error
		{
			MsgBox, 0x10, Error!, Cannot open an Excel instance!`nScript will now exit.
			ExitApp 
		}

		return xO
	}
}

KillExcel()
{
	Global xO

	if ( !xO.Visible )
		DetectHiddenWindows, On

	if ( xO.Workbooks.Count )
		xO.Workbooks.Close 

	( xO.Quit ), xO := ""
}

CheckSourceFiles( sF, lF )
{
	s := FileExist( sF ), l := FileExist( lF )

	if (!s||!l)
	{
		MsgBox, 0x10, Error!, % "Cannot locate the " ( (b:=!s&&!l) ? "Log & the Standard Quantity" 
		: !s ? "Standard Quantity" : !l ? "Log" : "" ) " file" (b?"s":"") "!`nPoint to or install "
		. (b?"them":"it") " & try again!"

		Exit
	}
}

Display( msg = "" )
{
	if ( A_CoordModeToolTip != "Screen" )
		CoordMode ToolTip, Screen

	ToolTip % msg, 10, 10
}

GetFileName( p )
{
	return (a:= StrSplit( p, "\" ) )[ a.Length() ] 
}

DelayMinutes(Dur)
{
	Mis:=1000, Min:=(60*Mis)
	Return (Dur*Min)
}
I'm trying to decide what the best approach to comparing csv versions will be unless ofcourse you already have an idea of how you'd like to do this.
Possible candidates are the FC Command and Excel's compare feature.
Let me know if you have a preferred approach. Once we come to a decision, csv versioning and comparing can be added to the script.
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Re: New to AHK & script writing, need help on project

16 Jan 2020, 17:51

TLM -

Thank you for the updated script with the loop built in, it is making more sense now. You created a master function call and looped that, then detailed all the functions below it and they are not directly in the loop, but are pulled in by the function call within the loop, clever. I updated my script to use this latest revision of the code and it is working great. It is definitely a nice feature to see the tooltip display so I know whether the scrip is running or idle. I do have a question about the syntax change within the script. At the top, you create reference names for the files (LogFile, OutputFile, StdPltQty, and CSVHead). You use these in the function call within the loop, but then change to using two-letter versions elsewhere. How/why does that work? Before you had to use those reference names exactly as they were defined, but now it seems that lF is the same as LogFile, oF is the same as OutputFile, sF is the same as StdPltQty, and cH is the same as CSVHead. How/why does that work?

As for the .csv file comparison method, I have no preference, just use whatever you think will be the easiest to work with. I honestly don't know enough about either method to make a call either way.

Thanks!

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

Re: New to AHK & script writing, need help on project

17 Jan 2020, 12:52

TLM -

Below is the updated script I am currently running on the machine. It has the current PID logic/function call setup and I simply added a few items for copying both the log file and output file to our shared network folder so I can monitor the status of the scanner (if the save date/time differs between the two files, I will know the scanner shut down for some reason). Anyway, I reviewed the output file and found an anomaly in it on line 35. I remember in the old script, it would look for missing quantity data and fill in the standard pallet quantity data (from the lookup in the "Std pallet qty-loc.xlsx" file) in the event the scanner didn't get it. However, for line 35, the quantity is blank despite the existence of a standard pallet quantity in the lookup file (should have been 18). Can you look at the attached log file, output file, and standard quantity file to see why the script is skipping this one?

Code: Select all

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


LogFile 	:= "C:\Users\chickman\Documents\terminal-01-17-2020.log" 	; points to the log file
OutputFile 	:= A_ScriptDir "\Scan Log 01-17-2020.csv"			; points to the output file
Delay 		:= DelayMinutes( 1 ) 						; how many minutes to wait between loops
StdPltQty 	:= A_ScriptDir "\Std pallet qty-loc.xlsx" 			; points to the standard quantity file
CopyLogFile	:= "R:\Tolleson\DuroShare\SW Scan Logs\terminal-01-17-2020.log" ; Network location to copy LogFile
CopyToFile	:= "R:\Tolleson\DuroShare\SW Scan Logs\Scan Log 01-17-2020.csv"	; Network location to copy OutputFile
CSVHead 	:= "Pallet ID,Quantity,Product ID,Loc. Code"

Loop
{
	; Read & Sanitize Log, Grab Locations, Save to Output File 
	ProcessLogFile( LogFile, StdPltQty, OutputFile, CSVHead )

	if FileExist( OutputFile ) 	; if output file exists, copy it to network location
        FileCopy, %OutputFile%, %CopytoFile%,1

	if FileExist( LogFile ) 	; if LogFile exists, copy it to the network location
        FileCopy, %LogFile%, %CopyLogFile%,1

	Sleep % Delay
}

; ^^^ YOU ONLY HAVE TO WORRY ABOUT THIS SECTION ^^^

return

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;; Functions
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

ProcessLogFile( lF, sF, oF, cH := "" )
{
	;; Check if log and/or location files exist
	CheckSourceFiles( sF, lF )

	;; Display "Processing..." status
	Display( "Processing..." )

	;; Open Workbook, Return Excel Object
	Global xO := CreateExcelObject( sF )
	
	lFn := GetFileName( lF ), oFn := GetFileName( oF )
	
	;; Reset Output File
	if FileExist( oF )
		FileDelete % oF
	
	;; Assign Array of RegEx needles 
	NeedleArray := 	[ "[-`,](?<quantity>\d{1,3})\D", "\D(?<prid>\d{5}(-\d{1,3}|))\D", "\D(?<spid>RO\d{8})\D" ]

	;; Parse Log File, Save Output File
	Loop, Read, % lF, % ( oF, PidArray := [] )
	{
		;; Add CSV heading if there is one
		if ( A_Index = 1 && cH )
			FileAppend % cH "`n"

		Match := RegExMatch( A_LoopReadLine, "^.*?(?<pid>RO\d{11})", pallet_ )

		if ( pallet_pid )
			PidArray.push( pallet_pid )

		if ( Match && ( !InStr( A_LoopReadLine, PidArray[ PidArray.length()-1 ] ) || PidArray.length() = 1 ) )
		{	
			pallet_loc := ""
			
			For Each, Needle in NeedleArray
				RegExMatch( A_LoopReadLine, Needle, pallet_ )

			QtyRowObj := xO.Range( "A1" ).End( -4162 ).Find( pallet_prid, xO.Cells( 1 ), -4163, 2, 1, 1, 0, 0 )

			Try
				pallet_loc := xO.Range( "D" QtyRowObj.Row ).Value
			Catch
				w .= RTrim( "Product ID: " pallet_prid " Location Not Found!`n" )

			if ( pallet_spid && !pallet_quantity )
				pallet_quantity := Floor( xO.Range( "B" QtyRowObj.Row ).Value )

			Display( RTrim( "Processing log file: " lFn "`n-----`nPallet ID: " pallet_pid
					. 		"`nQuantity: " pallet_quantity "`nProduct ID: " pallet_prid
					. 		"`nLocation: " pallet_loc . e := ( w ? "`n-----`n" w : "" ) ) )

			FileAppend % pallet_pid "," pallet_quantity "," pallet_prid "," pallet_loc "`n"
		}
	}

	;; Display "Complete..." status
	Display( "Processing complete!`nOutput saved to: " oFn . e ), KillExcel()


}

CreateExcelObject( sF, Visible := false )
{
	if ( !IsObject( xO ) )
	{
		Try
		{
			xO := ComObjCreate( "Excel.Application" ), xO.Visible := Visible
			OnExit( "KillExcel" ), xO.Workbooks.Open( sF )
		}
		Catch Error
		{
			MsgBox, 0x10, Error!, Cannot open an Excel instance!`nScript will now exit.
			ExitApp 
		}

		return xO
	}
}

KillExcel()
{
	Global xO

	if ( !xO.Visible )
		DetectHiddenWindows, On

	if ( xO.Workbooks.Count )
		xO.Workbooks.Close 

	( xO.Quit ), xO := ""
}

CheckSourceFiles( sF, lF )
{
	s := FileExist( sF ), l := FileExist( lF )

	if (!s||!l)
	{
		MsgBox, 0x10, Error!, % "Cannot locate the " ( (b:=!s&&!l) ? "Log & the Standard Quantity" 
		: !s ? "Standard Quantity" : !l ? "Log" : "" ) " file" (b?"s":"") "!`nPoint to or install "
		. (b?"them":"it") " & try again!"

		Exit
	}
}

Display( msg = "" )
{
	if ( A_CoordModeToolTip != "Screen" )
		CoordMode ToolTip, Screen

	ToolTip % msg, 10, 10
}

GetFileName( p )
{
	return (a:= StrSplit( p, "\" ) )[ a.Length() ] 
}

DelayMinutes(Dur)
{
	Mis:=1000, Min:=(60*Mis)
	Return (Dur*Min)
}
On a separate note, I really really like the display message screen that pops up while the script is running. Now that it is looping, the final "Success" message function hangs on the screen until the loop restarts, which is an added bonus. I was wondering how I could add addition items to this message. For instance, it would be nice if (in addition to what's already there) it would have a 3rd line that says "Total Logged Pallets" (then insert a count of the rows in the output file, minus 1 for the header) and a 4th line that says "Last Scanned Pallet Tag" (then insert the last row in the output file, with each cell separated by a space or commas). That way, while the loop is resting, I can easily see the total number of pallets in the file and the last pallet logged in the output file to see if the current pallet in the machine was captured without having to open the file.

I know this is the section to modify for this, but I need help with how to gather the data

Code: Select all

;; Display "Complete..." status
	Display( "Processing complete!`nOutput saved to: " oFn . e ), KillExcel()
I hope this back and forth is not getting to be too much for you. I really am enjoying working with you on this.

Thanks!

Chris
Attachments

[The extension xlsx has been deactivated and can no longer be displayed.]

Scan Log 01-17-2020.csv
(1.61 KiB) Downloaded 37 times
terminal-01-17-2020.log
(309.28 KiB) Downloaded 37 times
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Re: New to AHK & script writing, need help on project

17 Jan 2020, 17:47

TLM -

I have attached an updated copy of the log file and csv file from testing today. You will notice several lines with missing quantity data, which is concerning because I know that information is available via lookup on the IDs and it is not populating for some reason. Each of the ones I saw there was a scan that contained the quantity as well, but it was later in the log file. Also, the last two lines (119 & 120) are missing both quantity and product ID values. However, in looking at the log file, all of the scan data for each pallet tag contained both the pallet ID and quantity values in them. The only issue I saw is that the ID is 4 digits versus the typical 5, but I thought your script picked up the product ID as long is was 4-6 digits (or with a "-90" modifier)?

Can you please give this a look and let me know what you think is causing the missing quantities and missing ID (on the last two lines)?

Thanks!

Chris
Attachments

[The extension xlsx has been deactivated and can no longer be displayed.]

Scan Log 01-17-2020.csv
(3.23 KiB) Downloaded 49 times
terminal-01-17-2020.log
(642.93 KiB) Downloaded 44 times
User avatar
TLM
Posts: 1608
Joined: 01 Oct 2013, 07:52
Contact:

Re: New to AHK & script writing, need help on project

20 Jan 2020, 07:58

Just a FYI, my time has become severely constrained so I can only check in on the progress a few times today.
Chris70 wrote:
16 Jan 2020, 17:51
you create reference names for the files (LogFile, OutputFile, StdPltQty, and CSVHead). You use these in the function call within the loop, but then change to using two-letter versions elsewhere. How/why does that work?
The function parameters are abbreviations:
ProcessLogFile( lf = log file, sf = standard pallet quantity file, oF = output file, cH = csv headings )
If you like I can change the parameters to global variables, then they can be removed from the function parameters entirely.
Chris70 wrote:
17 Jan 2020, 12:52
I reviewed the output file and found an anomaly
I'm hoping this relates to the issue below ↓↓↓↓
Chris70 wrote:
17 Jan 2020, 17:47
...The only issue I saw is that the ID is 4 digits versus the typical 5
This was the exact problem. I updated the RegEx needle to match 3 to 5 numbers instead:
NeedleArray := [ "[-`,](?<quantity>\d{1,3})\D", "\D(?<prid>\d{3,5}(-\d{1,3}|))\D", "\D(?<spid>RO\d{8})\D" ]
That fixed the issue on my end based on the provided log, please test this.
Chris70 wrote:
16 Jan 2020, 17:51
As for the .csv file comparison method, I have no preference
I went with the FC command and while it took some getting use to the sometimes odd output, I was able to grab the target data.

I changed the CopyToFile so that it increments the batch file name in sequence when a comparison detects a difference in the output files.

The script will now:
  • scan the log file as it is saved or generated
  • create an output csv file, wait for 3 minutes
  • scan the log and create a second csv file
  • compare the 2 files and save the difference, sequentially named, to the CopyToFile directory.
It only loops twice so you can test it out for one `go around` feel free to remove 2 this from: Loop 2 to make the script process continuously.
Give everything a test and let me know how everything works out.

Code: Select all

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;; Variables
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

LogFile 		:= "C:\Users\chickman\Documents\terminal-01-02-2020.log" 		; points to the log file
StdPltQty 		:= A_ScriptDir "\Std pallet qty-loc.xlsx" 						; points to the standard quantity file

OutputFile 		:= A_ScriptDir "\Scan Log 01-02-2020.csv"						; sets output file pattern
CopyToFile      := "R:\Tolleson\DuroShare\SW Scan Logs\Scan Log 01-02-2020.csv" ; sets batch file pattern sequence

Duration 		:= DelayMinutes( 3 ) 											; how many minutes to wait between loops
CSVHead 		:= "Pallet ID,Quantity,Product ID,Loc. Code"

Loop 2 			; remove the 2 to loop continuously 
{
	CurrentOutputFile := GetCurrentOPFile( OutputFile )

	ProcessLogFile( LogFile, StdPltQty, CurrentOutputFile, CSVHead )
	CompareOutputFiles( OutputFile, CopyToFile, CSVHead )

	Sleep % Duration
}

ExitApp 			; change to `Return` to keep script running after loop completion

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;; Functions
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;


ProcessLogFile( lF, sF, oF, cH := "" )
{
	PalletCnt := 1 ; init counter
	
	;; Check if log and/or location files exist
	CheckSourceFiles( sF, lF )

	;; Display "Processing..." status
	Display( "Processing..." )

	;; Open Workbook, Return Excel Object
	global xO := CreateExcelObject( sF )
	
	lFn := GetFileName( lF ), oFn := GetFileName( oF )
	
	;; Reset Output File
	if FileExist( oF )
		FileDelete % oF
	
	;; Assign Array of RegEx needles 
	NeedleArray := 	[ "[-`,](?<quantity>\d{1,3})\D", "\D(?<prid>\d{3,5}(-\d{1,3}|))\D", "\D(?<spid>RO\d{8})\D" ]

	;; Parse Log File, Save Output File
	Loop, Read, % lF, % ( oF, PidArray := [] )
	{
		;; Add CSV heading if there is one
		if ( A_Index = 1 && cH )
			FileAppend % cH "`n"

		Match := RegExMatch( A_LoopReadLine, "^.*?(?<pid>RO\d{11})", pallet_ )

		if ( pallet_pid )
			PidArray.push( pallet_pid )

		if ( Match && ( !InStr( A_LoopReadLine, PidArray[ PidArray.length()-1 ] ) || PidArray.length() = 1 ) )
		{	
			pallet_loc := "", PalletCnt++
			
			For Each, Needle in NeedleArray
				RegExMatch( A_LoopReadLine, Needle, pallet_ )

			QtyRowObj := xO.Range( "A1" ).End( -4162 ).Find( pallet_prid, xO.Cells( 1 ), -4163, 2, 1, 1, 0, 0 )

			Try
				pallet_loc := xO.Range( "D" QtyRowObj.Row ).Value
			Catch
				w .= RTrim( "Product ID: " pallet_prid " Location Not Found!`n" )

			if ( pallet_spid && !pallet_quantity )
				pallet_quantity := Floor( xO.Range( "B" QtyRowObj.Row ).Value )

			Display( RTrim( "Processing log file: " lFn . ( pC := "`nPallet Count: " PalletCnt )
					. 		"`n-----`n" ( lP := "Pallet ID: " pallet_pid "`nQuantity: " pallet_quantity
					. 		"`nProduct ID: " pallet_prid "`nLocation: " pallet_loc )
					. 		e := ( w ? "`n-----`n" w : "" ) ) )

			FileAppend % pallet_pid "," pallet_quantity "," pallet_prid "," pallet_loc "`n"
		}
	}

	;; Display "Complete..." status
	Display( "Processing complete!`nOutput saved to: " oFn . pC "`n----- Last Pallet -----`n" lP . e ), KillExcel()
}

CompareOutputFiles( oF1, bF, cH )
{
	if FileExist( oF1 ) && FileExist( oF2 := GenNextOPFileName( oF1 ) )
	{
		Loop 2 ; get max number of lines from compare files
		{
			Loop Read, % ( ( i:=!i ) ? oF1 : oF2 )
			   tl_lines := a_index

			mx_lines .= tl_lines "`,"
		}

		Sort, mx_lines, RND`,
		mx_lines := StrSplit( mx_lines, "`," ).1

		DetectHiddenWindows On
		Run 	% ComSpec,, Hide, pid
		WinWait % "ahk_pid " pid

		DllCall( "AttachConsole", "UInt", pid )

		wshObj 	:= ComObjCreate( "WScript.Shell" )
		excObj 	:= wshObj.Exec( "FC " "/c /lb" mx_lines
				.  " """ oF1 """ """ oF2 """" )
		stdOut	:= cH "`r`n" RegExReplace( excObj.StdOut.ReadAll()
				,  "s)^.*?\*{5}.*?(RO0.*?)`r`n\*{5}.*?$", "$1" )

		DllCall( "FreeConsole" )
		Process Close, % pid
		
		CreateBatchSequence( bF, stdOut )

		FileDelete % oF2
	}
}

CreateBatchSequence( bF, stdOut )
{
	if ( !InStr( stdOut, "no differences encountered" ) )
	{
		SplitPath bF, FileName, Dir, Ext, NameNoExt

		if FileExist( bFP := Dir "\" NameNoExt "_*." Ext )
		{
			Loop Files, % bFP
				bfNum 	:= A_LoopFileDir "\"  NameNoExt "_"
						.  SubStr((fn:=A_LoopFileName)
						,  InStr(fn,"_")+1,-(StrLen(NameNoExt)-1))+1 "." Ext 
		}
		else 
			bfNum := Dir "\" NameNoExt "_1." Ext

		if ( !FileExist( Dir ) )
			FileCreateDir % Dir

		FileAppend % stdOut, % bfNum
	}
}

CreateExcelObject( sF, Visible := false )
{
	if ( !IsObject( xO ) )
	{
		Try
		{
			xO := ComObjCreate( "Excel.Application" ), xO.Visible := Visible
			OnExit( "KillExcel" ), xO.Workbooks.Open( sF )
		}
		Catch Error
		{
			MsgBox, 0x10, Error!, Cannot open an Excel instance!`nScript will now exit.
			ExitApp 
		}

		return xO
	}
}

KillExcel()
{
	Global xO

	if ( !xO.Visible )
		DetectHiddenWindows, On

	if ( xO.Workbooks.Count )
		xO.Workbooks.Close 

	( xO.Quit ), xO := ""
}

CheckSourceFiles( sF, lF )
{
	s := FileExist( sF ), l := FileExist( lF )

	if (!s||!l)
	{
		MsgBox, 0x10, Error!, % "Cannot locate the " ( (b:=!s&&!l) ? "Log & the Standard Quantity" 
		: !s ? "Standard Quantity" : !l ? "Log" : "" ) " file" (b?"s":"") "!`nPoint to or install "
		. (b?"them":"it") " & try again!"

		ExitApp
	}
}

GetCurrentOPFile( oF )
{
	static i
	oF2 := GenNextOPFileName( oF )
	return ( ( i:=!i ) ? oF : oF2 )
}

GenNextOPFileName( oF ) ; generate 2nd file name
{	
	SplitPath oF, FileName, FileDir, FileExtension, FileNameNoExt
	return FileDir "\" FileNameNoExt "_2." FileExtension
}

Display( msg = "" )
{
	if ( A_CoordModeToolTip != "Screen" )
		CoordMode ToolTip, Screen

	ToolTip % msg, 10, 10
}

GetFileName( p )
{
	return ( a:= StrSplit( p, "\" ) )[ a.Length() ] 
}

DelayMinutes( Dur )
{
	Mis:=1000, Min:=(60*Mis)
	Return (Dur*Min)
}
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Re: New to AHK & script writing, need help on project

20 Jan 2020, 11:36

TLM -
TLM Wrote:
Just a FYI, my time has become severely constrained so I can only check in on the progress a few times today.
I completely understand and appreciate all of time time you have given and continue to give to this project with me.
TLM Wrote:
The function parameters are abbreviations:
ProcessLogFile( lf = log file, sf = standard pallet quantity file, oF = output file, cH = csv headings )
If you like I can change the parameters to global variables, then they can be removed from the function parameters entirely.
I recognized that they were abbreviations, I just don't understand how the script itself knows that since I didn't see anywhere were you defined the abbreviations specifically. Is that just an inherit benefit of the AHK scripting language that you can define an abbreviation simply by referencing it in a function name like you did? I am just trying understand why things work better as we work through this. Your insights and explanations have been most helpful.
TLM Wrote:
This was the exact problem. I updated the RegEx needle to match 3 to 5 numbers instead:
NeedleArray := [ "[-`,](?<quantity>\d{1,3})\D", "\D(?<prid>\d{3,5}(-\d{1,3}|))\D", "\D(?<spid>RO\d{8})\D" ]
That fixed the issue on my end based on the provided log, please test this.
Thank you for finding that! I will re-run the original script with this modification and verify that works before testing out the next revision with the batch files and report back if I still see any issues.
TLM Wrote:
The script will now:
scan the log file as it is saved or generated
create an output csv file, wait for 3 minutes
scan the log and create a second csv file
compare the 2 files and save the difference, sequentially named, to the CopyToFile directory.
It only loops twice so you can test it out for one `go around` feel free to remove 2 this from: Loop 2 to make the script process continuously.
Give everything a test and let me know how everything works out.
I will give this a test and let you know how it works out. However, after looking at what you did, I am wondering if it would be better to do the comparison on the raw log file vs. the ending output file instead. Maybe take a copy of the raw log file before running the parsing array on the first run of the script. Then on the next run of the script compare the current log file to the last copy of the log file (finding just the new entries in the raw log file since the last time it was opened) and then only run the parsing script on the new entries (appending the master output file and creating a batch output file at the same time). That way, the processing time is not wasted re-filtering the same data over and over again to create essentially the same output each time with just the new added data from the last run and you create the batch file and update the master output file within the same process. That would mean we don't have to delete/reset the output file at all (the following would be deleted from the script).

Code: Select all

;; Reset Output File
	if FileExist( oF )
		FileDelete % oF
Does that make sense?

Anyway, I am off to start testing the scripts again. I will circle back on both mods and let you know how things go. Thanks again for all you are doing to help me here. I hope this thread is helping others as much as it is me! :D

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

Re: New to AHK & script writing, need help on project

20 Jan 2020, 17:54

TLM -

I confirmed the Product ID fix worked, but I had to change D{3,5} to D{4,6} because the 3 was pulling in the 3-digit quantity as the ID, and our product IDs are never less than 4 digits, but can be up to 6 before any potential "-90" modifier. So that is good. However, the testing on the batch file did not go quite as expected (see attached excel file for details). Here is the script I ran to test:

Code: Select all

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


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;; Variables
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;LogFile 		:= "C:\Users\chickman\Documents\terminal-01-02-2020.log" 		; points to the log file
LogFile 	:= A_ScriptDir "\terminal-01-17-2020-Test.txt" 						; points to the log file
StdPltQty 		:= A_ScriptDir "\Std pallet qty-loc.xlsx" 						; points to the standard quantity file

OutputFile 		:= A_ScriptDir "\Scan Log 01-17-2020-TestB.csv"						; sets output file pattern
;CopyToFile      := "R:\Tolleson\DuroShare\SW Scan Logs\Scan Log 01-02-2020.csv" ; sets batch file pattern sequence
CopyToFile      := A_ScriptDir "\Scan Log 01-17-2020-TestbatchB.csv" ; sets batch file pattern sequence
Duration 		:= DelayMinutes( 1.5 ) 											; how many minutes to wait between loops
CSVHead 		:= "Pallet ID,Quantity,Product ID,Loc. Code"

Loop 4 			; remove the 2 to loop continuously 
{
	CurrentOutputFile := GetCurrentOPFile( OutputFile )

	ProcessLogFile( LogFile, StdPltQty, CurrentOutputFile, CSVHead )
	CompareOutputFiles( OutputFile, CopyToFile, CSVHead )

	Sleep % Duration
}

ExitApp 			; change to `Return` to keep script running after loop completion

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;; Functions
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;


ProcessLogFile( lF, sF, oF, cH := "" )
{
	PalletCnt := 1 ; init counter
	
	;; Check if log and/or location files exist
	CheckSourceFiles( sF, lF )

	;; Display "Processing..." status
	Display( "Processing..." )

	;; Open Workbook, Return Excel Object
	global xO := CreateExcelObject( sF )
	
	lFn := GetFileName( lF ), oFn := GetFileName( oF )
	
	;; Reset Output File
	if FileExist( oF )
		FileDelete % oF
	
	;; Assign Array of RegEx needles 
	NeedleArray := 	[ "[-`,](?<quantity>\d{1,3})\D", "\D(?<prid>\d{4,6}(-\d{1,3}|))\D", "\D(?<spid>RO\d{8})\D" ]

	;; Parse Log File, Save Output File
	Loop, Read, % lF, % ( oF, PidArray := [] )
	{
		;; Add CSV heading if there is one
		if ( A_Index = 1 && cH )
			FileAppend % cH "`n"

		Match := RegExMatch( A_LoopReadLine, "^.*?(?<pid>RO\d{11})", pallet_ )

		if ( pallet_pid )
			PidArray.push( pallet_pid )

		if ( Match && ( !InStr( A_LoopReadLine, PidArray[ PidArray.length()-1 ] ) || PidArray.length() = 1 ) )
		{	
			pallet_loc := "", PalletCnt++
			
			For Each, Needle in NeedleArray
				RegExMatch( A_LoopReadLine, Needle, pallet_ )

			QtyRowObj := xO.Range( "A1" ).End( -4162 ).Find( pallet_prid, xO.Cells( 1 ), -4163, 2, 1, 1, 0, 0 )

			Try
				pallet_loc := xO.Range( "D" QtyRowObj.Row ).Value
			Catch
				w .= RTrim( "Product ID: " pallet_prid " Location Not Found!`n" )

			if ( pallet_spid && !pallet_quantity )
				pallet_quantity := Floor( xO.Range( "B" QtyRowObj.Row ).Value )

			Display( RTrim( "Processing log file: " lFn . ( pC := "`nPallet Count: " PalletCnt )
					. 		"`n-----`n" ( lP := "Pallet ID: " pallet_pid "`nQuantity: " pallet_quantity
					. 		"`nProduct ID: " pallet_prid "`nLocation: " pallet_loc )
					. 		e := ( w ? "`n-----`n" w : "" ) ) )

			FileAppend % pallet_pid "," pallet_quantity "," pallet_prid "," pallet_loc "`n"
		}
	}

	;; Display "Complete..." status
	Display( "Processing complete!`nOutput saved to: " oFn . pC "`n----- Last Pallet -----`n" lP . e ), KillExcel()
}

CompareOutputFiles( oF1, bF, cH )
{
	if FileExist( oF1 ) && FileExist( oF2 := GenNextOPFileName( oF1 ) )
	{
		Loop 4 ; get max number of lines from compare files
		{
			Loop Read, % ( ( i:=!i ) ? oF1 : oF2 )
			   tl_lines := a_index

			mx_lines .= tl_lines "`,"
		}

		Sort, mx_lines, RND`,
		mx_lines := StrSplit( mx_lines, "`," ).1

		DetectHiddenWindows On
		Run 	% ComSpec,, Hide, pid
		WinWait % "ahk_pid " pid

		DllCall( "AttachConsole", "UInt", pid )

		wshObj 	:= ComObjCreate( "WScript.Shell" )
		excObj 	:= wshObj.Exec( "FC " "/c /lb" mx_lines
				.  " """ oF1 """ """ oF2 """" )
		stdOut	:= cH "`r`n" RegExReplace( excObj.StdOut.ReadAll()
				,  "s)^.*?\*{5}.*?(RO0.*?)`r`n\*{5}.*?$", "$1" )

		DllCall( "FreeConsole" )
		Process Close, % pid
		
		CreateBatchSequence( bF, stdOut )

		FileDelete % oF2
	}
}

CreateBatchSequence( bF, stdOut )
{
	if ( !InStr( stdOut, "no differences encountered" ) )
	{
		SplitPath bF, FileName, Dir, Ext, NameNoExt

		if FileExist( bFP := Dir "\" NameNoExt "_*." Ext )
		{
			Loop Files, % bFP
				bfNum 	:= A_LoopFileDir "\"  NameNoExt "_"
						.  SubStr((fn:=A_LoopFileName)
						,  InStr(fn,"_")+1,-(StrLen(NameNoExt)-1))+1 "." Ext 
		}
		else 
			bfNum := Dir "\" NameNoExt "_1." Ext

		if ( !FileExist( Dir ) )
			FileCreateDir % Dir

		FileAppend % stdOut, % bfNum
	}
}

CreateExcelObject( sF, Visible := false )
{
	if ( !IsObject( xO ) )
	{
		Try
		{
			xO := ComObjCreate( "Excel.Application" ), xO.Visible := Visible
			OnExit( "KillExcel" ), xO.Workbooks.Open( sF )
		}
		Catch Error
		{
			MsgBox, 0x10, Error!, Cannot open an Excel instance!`nScript will now exit.
			ExitApp 
		}

		return xO
	}
}

KillExcel()
{
	Global xO

	if ( !xO.Visible )
		DetectHiddenWindows, On

	if ( xO.Workbooks.Count )
		xO.Workbooks.Close 

	( xO.Quit ), xO := ""
}

CheckSourceFiles( sF, lF )
{
	s := FileExist( sF ), l := FileExist( lF )

	if (!s||!l)
	{
		MsgBox, 0x10, Error!, % "Cannot locate the " ( (b:=!s&&!l) ? "Log & the Standard Quantity" 
		: !s ? "Standard Quantity" : !l ? "Log" : "" ) " file" (b?"s":"") "!`nPoint to or install "
		. (b?"them":"it") " & try again!"

		ExitApp
	}
}

GetCurrentOPFile( oF )
{
	static i
	oF2 := GenNextOPFileName( oF )
	return ( ( i:=!i ) ? oF : oF2 )
}

GenNextOPFileName( oF ) ; generate 2nd file name
{	
	SplitPath oF, FileName, FileDir, FileExtension, FileNameNoExt
	return FileDir "\" FileNameNoExt "_2." FileExtension
}

Display( msg = "" )
{
	if ( A_CoordModeToolTip != "Screen" )
		CoordMode ToolTip, Screen

	ToolTip % msg, 10, 10
}

GetFileName( p )
{
	return ( a:= StrSplit( p, "\" ) )[ a.Length() ] 
}

DelayMinutes( Dur )
{
	Mis:=1000, Min:=(60*Mis)
	Return (Dur*Min)
}
What I did was increase the loop count to 4 so I could a few iterations and reduced the delay timer to 1.5 minutes so I did not have to wait as long. I manually overwrote the Test data with Test1 for loop 1, Test2 for loop 2 and Test 3, for loop 3 (and left at test 3 for loop 4) each time the script was idle to simulate the log file changing between runs. I put tabs for the output and batch files for reference. The first tab shows my methodology and notes for reference. Please review and let me know what your thoughts are on this. I can send you the test data files as well, but there were too large to post.

Thanks!

Chris
Attachments

[The extension xlsx has been deactivated and can no longer be displayed.]

User avatar
TLM
Posts: 1608
Joined: 01 Oct 2013, 07:52
Contact:

Re: New to AHK & script writing, need help on project

20 Jan 2020, 19:40

Chris70 wrote:
20 Jan 2020, 17:54
I had to change D{3,5} to D{4,6} because the 3 was pulling in the 3-digit quantity as the ID, and our product IDs are never less than 4 digits
I was actually wondering if that would happen. If 4,6 fails, also try 4,5.

Going to look through the results now. If whenever possible please also attach as many varying logs as you can.
You don't have to upload massive sets, just different configurations so I can add any additional logic.
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Re: New to AHK & script writing, need help on project

21 Jan 2020, 09:41

TLM -
TLM wrote:
If whenever possible please also attach as many varying logs as you can.
Please find zipped folders of the Test1, Test2, and Test3 data log files used to test the batching script. Test3 contains data collected from Friday, 1/17/2020 starting about 7am through Monday, 1/20/2020 at about 7am. Test2 is Test3 with data collected from 12am-7am on 1/20 removed. Test1 is Test 3 with data collected from 12am on 1/19 through 7am on 1/20 removed. These were used to simulate successive changes to the log file to test the batch file script. I could have used a much smaller sample set, but I like to use real data as much as possible when testing. I will upload the last 24 hour log file in a separate post since I am limited to 3 files per post. I had to zip them to get them under the 2MB file limit so sorry for that!

Thanks!

Chris
Attachments
terminal-01-17-2020-Test3.zip
(1.21 MiB) Downloaded 39 times
terminal-01-17-2020-Test2.zip
(1.09 MiB) Downloaded 40 times
terminal-01-17-2020-Test1.zip
(678.97 KiB) Downloaded 42 times
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Re: New to AHK & script writing, need help on project

21 Jan 2020, 09:46

TLM -

Here is the log file and resulting output file from the last 24 hours (~7am 1/20/2020 through ~7am 1/21/2020) for your reference and testing purposes. I'll just post these each day when I changeover the log file and script each day (manually changing the file name date extension at the moment). I would like to automate that process at some point as well (automatically change the log file and output file date extensions each day at 7AM for shift change so all files would contain 24 hours of data running 7am-7am).

Thanks!

Chris
Attachments
Scan Log 01-20-2020.csv
(9.51 KiB) Downloaded 45 times
terminal-01-20-2020.zip
(436.08 KiB) Downloaded 39 times
User avatar
Chris70
Posts: 46
Joined: 17 Dec 2019, 12:25

Re: New to AHK & script writing, need help on project

21 Jan 2020, 09:59

TLM -

By the way, I appreciate you adding the running/final pallet count and last pallet scanned data to the information display. One thing I noticed though is that the pallet counter is presently including the header line in it's count. What would be the change needed to take the current pallet count being calculated and subtract 1 (for the CSVHead) from that value to get the true pallet count? For now, I know to subtract one from the total in my brain, but it would be nice to have it be correct.

Thanks!

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

Re: New to AHK & script writing, need help on project

21 Jan 2020, 10:49

TLM -

Now I feel stupid for asking about the pallet count. All I had to do was change the starting pallet count to 0 and that fixes the problem.

So, I changed this:

Code: Select all

	PalletCnt := 1 ; init counter
to this:

Code: Select all

	PalletCnt := 0 ; init counter

Duh...

Thanks!

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

Re: New to AHK & script writing, need help on project

22 Jan 2020, 09:33

TLM -

I have attached a .zip folder with the log file and corresponding csv file for the past 24 hours. I also am attached an Excel file where I went through the data to do a quick verification check. I copied the data into a table and sorted it by pallet tag ID, then looked through the data line by line to see if I saw anything amiss and flagged the ones that looked to have errors (such as pallet ID's in a series that have differing quantities, product ID or locations, and/or duplicate entries for the same pallet ID). The only errors I found were a few pallets seem to have been duplicated for some reason (see list below).
RO00587898124
RO00587898124
RO00587898125
RO00587898125
RO00589162018
RO00589162018
I combed through the raw log file for the instances of these pallet ID's and it appears that these pallets may have been re-wrapped because there are other pallet ID's in between the instances of the each of these ones that were duplicated (this does not typically happen, but occasionally a pallet gets damaged and they have to re-wrap it). I thought the script was supposed to ignore duplicates of the same pallet ID, but evidently if the pallet ID repeats itself elsewhere in the log file (vs. just a repeat within the same block of scans without other pallets in between) it can create a duplicate entry. Take a look at that and let me know your thoughts.

Thanks!

Chris
Attachments
Scan Log 01-21-2020.zip
(489.68 KiB) Downloaded 39 times

[The extension xlsx has been deactivated and can no longer be displayed.]

User avatar
TLM
Posts: 1608
Joined: 01 Oct 2013, 07:52
Contact:

Re: New to AHK & script writing, need help on project

22 Jan 2020, 16:26

Hey Chris quick question, does the computer that you're running the ProcessLogFile script on have Ms Word standalone or in Ms Office?
Thanks
User avatar
TLM
Posts: 1608
Joined: 01 Oct 2013, 07:52
Contact:

Re: New to AHK & script writing, need help on project

22 Jan 2020, 17:35

Chris70 wrote:
22 Jan 2020, 09:33
if the pallet ID repeats itself elsewhere in the log file (vs. just a repeat within the same block of scans without other pallets in between) it can create a duplicate entry.
The script looks for duplicates that occur directly after the current scan as this was how the original log was structured. I'll see what can be done to fix this.

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: dunnerca, Google [Bot], TAC109, wilkster and 127 guests