New to AHK & script writing, need help on project

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

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

22 Jan 2020, 17:51

TLM -
TLM Wrote:
Hey Chris quick question, does the computer that you're running the ProcessLogFile script on have Ms Word standalone or in Ms Office?
Thanks
The computer is running MS Office suite (office 360 under a corporate license).

As for the duplicate tag issue mentioned before, this is the first time I have seen this happen, so if it's going to be a major pain to figure that one out, don't worry about it. Our system will only allow us to receive one unique pallet at a time and will reject any attempts to post a 2nd pallet. So, while it would be nice to avoid all duplicates so as to avoid posting errors, it isn't the end of the world if we get a few here and there. I am really hoping to get the batch file thing figured out. I have a couple of ideas I am testing with what you gave me, but so far I am not having any luck.

Thanks!

Chris
User avatar
TLM
Posts: 1582
Joined: 01 Oct 2013, 07:52
GitHub: TLMcode
Contact:

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

22 Jan 2020, 19:07

Thanks for that info! I'm trying to find tricks for the comparison function ;)

It's okay regarding the the duplicate issue, I've already fixed it.
This is actually a good find as every line from a log was being pushed to the array PidArray.
Depending on the size of the log, the script could have potentially consumed a lot of memory.

I will post an updated script once I work out the new comparison function.
User avatar
Chris70
Posts: 43
Joined: 17 Dec 2019, 12:25

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

23 Jan 2020, 09:32

TLM -

Please find the attached zipped log file with corresponding output CSV file along with an Excel file with my data review for the last 24 hours. In this batch, there were only two bad data points and both captured the pallet ID and the quantity, but failed to find the product ID and subsequently the location was missing since there was no value for the product ID to look up.

These are the two bad data points:
Pallet ID
RO00589630014
RO00587839086

In looking at the raw data (snippets quoted below for these two pallets), all of the captured scan lines had errors which the script is designed to correct, but there is a flaw somewhere that allows for the filter to grab a line that just has the pallet ID and quantity, but no product ID. So, the resulting filtered output lists only the pallet ID and quantity, but not the product ID (and consequently no location since there is no product ID to reference the lookup table, giving a missing location message on the display as well).
18:05:27 <-RO00572669,RO00589630014,84[CR]
18:05:28 ->LOFF[CR]
18:05:28 ->LON[CR]
18:05:28 <-RO00572669,RO00589630014,70851[CR]
18:05:29 ->LOFF[CR]
03:03:03 <-RO00576252,RO00587839086,24[CR]
03:03:04 ->LOFF[CR]
03:03:05 ->LON[CR]
03:03:05 <-RO00576252,RO00587839086,33595[CR]
03:03:06 ->LOFF[CR]
03:03:06 ->LON[CR]
03:03:06 <-RO00576252,RO00587839086,33595[CR]
03:03:07 ->LOFF[CR]
03:03:07 ->LON[CR]
The subsequent scans show the pallet ID and product ID (which would then would be able to fill in the missing quantity info from the std. pallet file). If I delete the first scans bolded above from the log file, the filter works correctly.

First, I would like to add a default location of "TBD" in the event there is no location that can be determined from the lookup file (for when the ID is missing or there is a new ID that isn't in the lookup file for some reason, which could happen if sales creates a new product ID in our ERP system which needs to be manually updated in the reference lookup file). That way, each output file line is guaranteed to always have pallet ID, quantity and location even if the product ID is missing.

Second, is there a way to grab a subsequent scan in the log (assuming one exists) for the situation like this one where the filter could have corrected the error if it hadn't locked in on the first one that had palled ID and quantity, but no product ID (which it cannot fix) vs. the one with only palled ID and product ID (which it can fix using the lookup table)?

BTW, how goes the battle with the batch file? I am still testing on my end, but not getting anywhere yet.

Thanks!

Chris
Attachments
terminal-01-22-2020.zip
(511.06 KiB) Downloaded 9 times
Scan Log 01-22-2020.xlsx
(31.29 KiB) Downloaded 15 times
User avatar
TLM
Posts: 1582
Joined: 01 Oct 2013, 07:52
GitHub: TLMcode
Contact:

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

23 Jan 2020, 09:52

Please update the ProcessLogFile() function.
With fixes for the above issue and improves the duplicate filter:

Code: Select all

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


ProcessLogFile( lF, sF, oF, cH := "" )
{
	pallet_cnt := 0 ; 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, PidList := "" )
	{
		;; Add CSV heading if there is one
		if ( A_Index = 1 && cH )
			FileAppend % cH "`n"

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

		if ( pMatch && pLoc ) && !InStr( pid_list, pallet_pid )
		{	
			pid_list .= pallet_pid, pallet_loc := "", pallet_cnt++

			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: " pallet_cnt )
					. 		"`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()
}
You could also go if ( RegExMatch( A_LoopReadLine, "^.*?(?<pid>RO\d{11})", pallet_ ) && A_LoopReadLine ~= NeedleArray.2 ) && !InStr( pid_list, pallet_pid )

I have a little extra time to work on the batch files today.
User avatar
Chris70
Posts: 43
Joined: 17 Dec 2019, 12:25

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

23 Jan 2020, 10:30

TLM -

Thanks for the updated script, I will test it today and then start using it on the machine. Don't forget to change the product ID string dimension for:

Code: Select all

NeedleArray := 	[ "[-`,](?<quantity>\d{1,3})\D", "\D(?<prid>\d{3,5}(-\d{1,3}|))\D", "\D(?<spid>RO\d{8})\D" ]
change d{3,5} to d{4,6} to avoid the filter mistaking the 3-digit quantity for the product ID. I fixed that in my version, but noticed you still had it.

Also, when you say,
You could also go if ( RegExMatch( A_LoopReadLine, "^.*?(?<pid>RO\d{11})", pallet_ ) && A_LoopReadLine ~= NeedleArray.2 ) && !InStr( pid_list, pallet_pid )
What part of the script would that replace? I am researching the AHK manual for a lot of what you are sending me to understand it better. I am having a hard time with some of it only because I am not a programmer and trying to determine what is AHK commands, and what is custom is making my head spin a little, but I am getting there. As I keep saying, I really appreciate all the time you are giving to this and I am very grateful for the imparted knowledge that is accompanying each post.

Thanks!

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

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

23 Jan 2020, 12:54

TLM -

OK, I tested your updated ProcessLogFile() function and it did solve the problem with the missing product ID on the data I sent you this morning from the past 24 hours (thank you!). However, I clocked the time it took to run the file using the old script vs. the updated one and the updated one actually took about 10s longer to process, so I am not sure if it accomplished the intended goal or not for that purpose. I did not see where you included a default location of "TBD" in the event the lookup function does not find a location for some reason (such as if the product ID is missing from the scan OR the product ID does not exist in the lookup table). Perhaps on the message line about the missing location info, we could put something like "Pallet"<pid>"does not have a valid location, assigned to TBD" for every pallet that it has no location found (not sure the syntax is right there, but I hope that's clear).

I have attached the two test scripts for reference (A1 is the previous version, A2 is the new version). (NOTE: I commented out the network file copy lines and changed the local log directory to be the script directory for testing on my office computer by putting all files in the script directory).

On a separate note, the part where you added the last pallet info at the end of each loop, how hard would it be to list the last (5) pallets (listing the last five lines in the output file) instead? That would give a snapshot of the all the pallets currently on the machine (at most there would be 5 pallets scanned, one at the scanner waiting to be wrapped, one in the wrapper, and three on the exit conveyor waiting to be picked up) for auditing purposes. I also foresee putting a larger display of this information on the machine for drivers to verify if a pallet was scanned or not.

Thanks!

Chris
Attachments
Test Filter-7A2 w save loop.ahk
(4.48 KiB) Downloaded 17 times
Test Filter-7A1 w save loop.ahk
(4.5 KiB) Downloaded 15 times
terminal-01-22-2020.zip
(509.32 KiB) Downloaded 17 times
User avatar
Chris70
Posts: 43
Joined: 17 Dec 2019, 12:25

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

24 Jan 2020, 09:18

TLM -

Attached is the log file and output file for the past 24 hours (zipped together) along with my analysis xlsx file. This batch only had one repeated issue for one ID (48663) in that it captured the pallet ID and product ID (and subsequent lookup of the location), but it did not fill in the missing pallet quantity like it is supposed to for some reason.

Here is a list of the affected pallet ID's:
Pallet ID Quantity Product ID Loc. Code
RO00589356001 48663 TBD
RO00589356002 48663 TBD
RO00589356003 48663 TBD
RO00589356004 48663 TBD
RO00589356006 48663 TBD
RO00589356012 48663 TBD
RO00589356011 48663 TBD
And here is a sample of the raw log file for this series of pallets. There is a long number string that I have not seen before showing up in place of the pallet quantity (not the usual 10 character "RO" that normally gets captured). Is there some mod we need to do that will say something like If found <pid> & <prid> but Not <qty>, then lookup <qty> from <sF>? I know the syntax there is all wrong, but I am not sure why the lookup function is not filling in the standard quantity in this case.
15:16:57 <-RO00589356001,48663,90004403960001412831[CR]
15:16:58 ->LOFF[CR]
15:16:58 ->LON[CR]
15:16:58 <-90004403960001412831,RO00589356001,48663[CR]
15:16:59 ->LOFF[CR]
15:16:59 ->LON[CR]


I hope all is well with you. I hope to hear from you again soon.

Chris
Attachments
terminal-01-23-2020.zip
(515.78 KiB) Downloaded 15 times
Scan Log 01-23-2020.xlsx
(27.98 KiB) Downloaded 17 times
User avatar
Chris70
Posts: 43
Joined: 17 Dec 2019, 12:25

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

24 Jan 2020, 14:26

TLM -

Ok, so I think I figured out how to fix the situation when the output file has a product ID but no quantity as I detailed in my earlier post from today (1/24/2020).

This section of code in the original script is where the issue appears to be:

Code: Select all

			if ( pallet_spid && !pallet_quantity )
				pallet_quantity := Floor( xO.Range( "B" QtyRowObj.Row ).Value )
In the above code, you had defined <spid> in the NeedleArray as "\D(?<spid>RO\d{8})\D" which I figured out is the situation where we find a 10-character scan that starts with "RO" that would replace the scanned pallet quantity in the log. So, that line does insert the standard pallet quantity in that situation. However, the log file I uploaded this morning has a completely different random barcode that is replacing the quantity barcode (while the other two scans do include both the product ID and the pallet ID). So, rather than try and imagine every other possible extraneous barcode that could displace the quantity barcode in the data register, I decided to try replacing the <spid> from the needle with <prid> instead. That way I am just filling in the missing quantity anytime the script detects a product ID but no quantity. I tried this change and it seems to work beautifully on the log with this issue. This is the change I made:

Code: Select all

			if ( pallet_prid && !pallet_quantity )
				pallet_quantity := Floor( xO.Range( "B" QtyRowObj.Row ).Value )
Let me know if you see any issues with this change.

Thanks!

Chris
User avatar
TLM
Posts: 1582
Joined: 01 Oct 2013, 07:52
GitHub: TLMcode
Contact:

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

28 Jan 2020, 13:53

Hi Chris, I have so time today to work on the script and have some updates.
Chris70 wrote:
24 Jan 2020, 14:26
I tried this change and it seems to work beautifully on the log with this issue. This is the change I made:

Code: Select all

			if ( pallet_prid && !pallet_quantity )
				pallet_quantity := Floor( xO.Range( "B" QtyRowObj.Row ).Value )
This is fine but is redundant as at this point there's always a product id due to the `master filter`.
Looking for just the missing quantity like this if ( !pallet_quantity ) should work ( untested ).

I found these anomalous scans and patched the filters against them:
10:38:23 <-70255,RO00589595001,51032[CR]
13:12:32 <-71130,71130,RO00589535023[CR]

I overhauled the product id filter logic to a `white list` pattern ( from bad scans to valid scans ).
This future proofs against trying to add an additional filter for every unforeseen scan error possibility.

After extensive testing and getting inconsistent comparison data using the FC command and several MS Office comparison features, I ended up building my own.
My initial worry about a custom comparison function was that it would be slow and memory intensive.
After benchmarking, it seems to perform much better than expected with reliable results for the scripts specific needs.
Comparison is now streamlined and performed on the original output file and a secondary scan, rather than having to create/read another output file.

Anyway give this latest version a test run and let me know if you run into any issues.

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 4 	; remove 4 to continue looping
{
	ProcessLogFile( LogFile, StdPltQty, OutputFile, CopyToFile, CSVHead )	
	Sleep % Duration
}

ExitApp ; remove if continuing looping

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


ProcessLogFile( lF, sF, oF, bF, cH = "" )
{
	static cycle 	:= 0 ; 1st or 2nd cycle init
	pallet_cnt 		:= 0 ; pallet count init
	
	;; check if log and/or location files exist
	CheckSourceFiles( sF, lF )

	Display( "Processing..." )

	;; open workbook, return excel object
	global xO := CreateExcelObject( sF )
	
	lFn := GetFileName( lF ), oFn := GetFileName( oF )

	;; reset output file
	if FileExist( oF ) && !cycle
		FileDelete % oF
	
	;; assign array of regex needles for log line filter and data extraction
	NeedleArray := 	[ "[-`,`t](?<quantity>\d{1,3})\D", "s)[-`,`t](?<prid>\d{4,5})(?!.+?[-`,`t]\d{4,5}[,\[])", "\D(?<spid>RO\d{8})\D" ]

	;; read the log file and loop through each line, toggle cycle
	Loop Read, % lF, % ( oF, cycle:=!cycle )
	{
		;; the log file line passes if it contains a pallet id, contains 1 product id and the pallet id isn't in the pallet id list 
		if ( RegExMatch( A_LoopReadLine, "^.*?(?<pid>RO\d{11})", pallet_ ) && A_LoopReadLine ~= NeedleArray.2 ) && !InStr( pid_list, pallet_pid )
		{	
			pid_list .= pallet_pid, pallet_loc := "", pallet_cnt++

			;; extract quantity, product id & short pallet id from each filtered line 
			For Each, Needle in NeedleArray
			{
				RegExMatch( A_LoopReadLine, Needle, pallet_ )
			}

			;; find location code based on product id
			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" )

			;; grab quantity if it's missing ; *commented out* with short pallet id
			if ( !pallet_quantity ) ; << untested! original >> if ( pallet_spid && !pallet_quantity )
				pallet_quantity := Floor( xO.Range( "B" QtyRowObj.Row ).Value )

			;; extracted scan data 
			extr_scan 	:= pallet_pid "," pallet_quantity "," pallet_prid "," pallet_loc

			;; assigns display string 
			disp_str 	:= RTrim( "Filtering Log: " lFn . ( pC := "`nExtracted Scans: " pallet_cnt )
						.  "`n-----`n" ( lP := "Pallet ID: " pallet_pid "`nQuantity: " pallet_quantity
						.  "`nProduct ID: " pallet_prid "`nLocation: " pallet_loc )
						.  e := ( w ? "`n-----`n" w : "" ) )

			;; 1st run writes output file, 2nd checks/retrieves updates 
			if cycle
			{
				Display( disp_str )				
				FileAppend % ( pallet_cnt = 1 && cH ? cH "`n" : "" ) . extr_scan "`n"
			}
			else
			{
				Display(  "Checking For Log Updates..." . "`nLog File: " lFn "`n-----`nCurrent Scan: " extr_scan )
				log_updates := GetLogUpdates( oF, extr_scan )
			}
		}
	}

	KillExcel()

	;; updates from the log are saved to batch if any
	if ( log_updates )
	{
		GetLogUpdates( "reset" )
		Display( "Log File Changed!`nCreating Batch!" )
		CreateBatchSequence( bF, cH "`n" log_updates )
	}
	else
	{
		Display( "Log Filter Complete!`nLog File: " lFn "`nOutput CSV: " oFn . pC "`n----- Last Pallet -----`n" lP . e )
	}
}

CreateBatchSequence( bF, data )
{
	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 % data, % bfNum

	bCnt := StrSplit( data, "`n" ).Length()-1
	Display( "Batch File Created!`n-----`nBatch File: " GetFileName( bfNum ) "`nUpdate Count: " bCnt )
}

GetLogUpdates( oF, pS = "" )
{
	static cL

	if ( oF = "reset" )
	{
		cL := "" 
	}
	else 
	{
		Loop Read, % oF
		{
			if ( pS = A_LoopReadLine, match := false )
			{
				match := true
				break
			}
		}

		if ( match = false )
		{
			cL .= pS "`n"
		}	

		return RTrim( cL, "`n" )	
	}
}

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
	}
}

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)
}
BTW I've been reading the log files so much than I'm getting use to the file structure :lol:
User avatar
Chris70
Posts: 43
Joined: 17 Dec 2019, 12:25

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

28 Jan 2020, 14:21

TLM -

I hadn't heard from you in a while, I was beginning to think perhaps this project was taking up too much of your time and you stopped following it. I appreciate your latest update and I will definitely work on testing it soon. In the meantime, I posted a couple other forum threads asking for help with the batching script and displaying the last five lines from the output file and am working with those as well (I had asked you about those recently, but never got a response). Search for Chris70 in the forum posts and you'll see those. So, I am running separate scripts for each of those functions right now and they are working OK so far, but I definitely would like to incorporate more into one script if possible, but building it in pieces is working out well enough for now.

Your previous version of your script has been work great for generating the Scan Log output file. Here is what is currently running. I just update the file name each day to the current date. It would be nice to automate indexing the log file date at some point, but that's not the highest priority yet. I use the output file from your script as the source file for the batching script (posted by someone named Just Me) and testing is going well with that 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-24-2020.log" 	; points to the log file
OutputFile 	:= A_ScriptDir "\Scan Log 01-24-2020.csv"			; points to the output file
Delay 		:= DelayMinutes( 0.5 ) 						; 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-24-2020.log" ; Network location to copy LogFile
CopyToFile	:= "R:\Tolleson\DuroShare\SW Scan Logs\Scan Log 01-24-2020.csv"	; Network location to copy OutputFile
CSVHead 	:= "Pallet ID,Quantity,Product ID,Loc. Code"

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

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

	if FileExist( LogFile ) 	; if output file exists...
        FileCopy, %LogFile%, %CopyLogFile%,1

	Sleep % Delay
}

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

return

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

ProcessLogFile( lF, sF, oF, cH := "" )
{
	pallet_cnt := 0 ; 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, PidList := "" )
	{
		;; Add CSV heading if there is none
		if ( A_Index = 1 && cH )
			FileAppend % cH "`n"

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

		if ( pMatch && pLoc ) && !InStr( pid_list, pallet_pid )
		{	
			pid_list .= pallet_pid, pallet_loc := "", pallet_cnt++

			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_prid && !pallet_quantity )
				pallet_quantity := Floor( xO.Range( "B" QtyRowObj.Row ).Value )

			Display( RTrim( "Processing log file: " lFn . ( pC := "`nPallet Count: " pallet_cnt )
					. 		"`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()
}

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)
}
TLM wrot:
BTW I've been reading the log files so much than I'm getting use to the file structure :lol:
I actually stopped uploading the daily log files when I hadn't heard form you. Do you want me to send you the ones you've missed? Just let me know.

Thanks!

Chris
User avatar
TLM
Posts: 1582
Joined: 01 Oct 2013, 07:52
GitHub: TLMcode
Contact:

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

28 Jan 2020, 18:19

@Chris70 Please see your private messages.

Return to “Ask For Help”

Who is online

Users browsing this forum: Albireo, Bing [Bot], boiler, Eureka, Google [Bot], JawGBoi, just me, Zeppy and 126 guests