New to AHK & script writing, need help on project

[Chris70]

terminal-12-17-2019.log

(107.63 KiB) Downloaded 64 times

terminal-12-17-2019-modified.txt

(93.72 KiB) Downloaded 61 times

Hello. I am new to writing scripts and found AHK online and it looked to be exactly what I need for a work task. I am a process engineer at a manufacturing plant and this is a long post because I am not sure how much information to provide to get the help I need, so sorry in advance for that!

I have a project where I installed a barcode reader at a machine to read the pallet tags as they pass by on a conveyor to collect the information on each pallet of product (specifically the unique pallet ID, pallet quantity, and product ID). The barcode reader has an application that came with it, but it is lacking in any control over the output file format. I am looking to build a script that will do several things eventually, but I have to start with some basics and build from there. I am attaching a several files for reference.

Step 1 Problem (scripts for simple iterative tasks)
The raw file is the .log file. The .txt file is the modified log file where I replaced all “:” with a “,” and removed extraneous characters “->” “<-“ and “[CR]” to be able to create a clean CSV (did this manually in Notepad using ctrl-H to find and replace all for each item, looking for script that will do this). The .CSV (which I left off the attachment due to the 3-file limit) file is just the .txt file with the extension changed to .CSV (did this manually in Notepad doing a file Save As and changing file extension to ".csv" - looking for script that will do this after the first script runs to create the clean .CSV file). Finally, the .xlsx file is created from the .CSV file converted to an excel file table with headers (created by opening the .CSV file in Excel, saving as an .xlsx file, then adding the headers and converting the data to a table format, looking for a script to do that).

Step 2 Problem (combine scripts into one cohesive script for intended workflow)
My initial workflow idea was to write a script that will run in the background that will periodically open the .log file, save the log file to a temporary new .txt file (since the active log file is constantly being written to and I do not want to overwrite it). Then, open the temporary text file and perform a find and replace for all the unwanted characters and save that file as a .CSV file. Then, use the .CSV file as the data source for a master excel file that will troll the .CSV file for correct scan information. The pallet tag data is consistent in its format - Pallet ID is a unique 13-character code (format "RO###########"), the pallet quantity will always be <999, and the pallet ID will always be a 4-6 digit number or a 4-6 digit number with a -ID modifier code (for example and ID will be 14858, but may be 14858-90 with a 1-3 digit modifier after the "-"). The issue is that the order of the barcode scans is not consistent. Unfortunately, there can be multiple barcodes within the scan window that the scanner can read. The target is a single pallet tag label that contains three barcodes (a unique serialized pallet ID, palet quantity, and product ID), but I have found that some of the bale labels have code 128 or code 39 barcodes on them like our pallet tags, so the ID will be read from the bale label instead of the pallet tag (or some other random information replaces the pallet quantity). If just the pallet tag is read, the string sequence will consistently contain this data in the correct order (Pallet ID > Quantity > Product ID), but the order will get fouled if there is another barcode captured in the scan.

Step 3 Problem (now it gets more convoluted and I know what I need to do, but the how to is way beyond my experience or knowledge)
To further complicate matters, sometimes there is a 10-digit RO# in a skinny barcode along the top of a bale label that the reader catches instead of the bale quantity. It looks for 3 barcodes, completes the scan, then starts a new string. The longer the barcode, the easier it is to read I think, which is why the quantity barcode is the first one that gets ignored. Those data lines appear with a 10-character (format "RO########"), the normal 13-character serialized pallet number (format "RO###########"), and the product ID (typically 4 to 5 digits) (but drops the quantity barcode because it filled the 3-buckets for the scan string). Absent a pallet quantity scan, I will have to force in the standard pallet quantity to compensate and ignore/replace the extraneous 10-character barcode (via a lookup function in Excel keyed on the Product ID). I need to be able to extract the good data (good scans that match the correct format of Pallet ID > Quantity > Product ID), dump the bad lines (any lines that don't include a 13-character pallet ID in the format "RO###########"), delete any repeated data lines (the pallet tags each have a unique 13-character string in the format of "RO###########" but the scanner is constantly reading so it often will dump numerous scans that are virtually identical, so I only want to keep one and only one instance of each pallet tag), re-format any non-conforming data lines to match the correct format (for example, if the scan comes in Product ID > Pallet ID > Quantity, then rearrange data to correct format Pallet ID > Quantity >Product ID), and finally, use the Product ID to lookup and fill in the standard pallet quantity if pallet quantity data is missing from the scan string (such as with the above example).

There will be more added to this project once I have done the above, so if anyone out there likes a challenge and doesn't mind working for free to help out, this is the thread for you! I am willing to test anything out and report back what works and what doesn't. I want to learn and I tend to pick up new things quickly, so I hope this will be a productive post for everyone.

Thank you in advance for any assistance!!!!
Chris70

[TLM]

Welcome to AutoHotkey

Based off of:

...Pallet ID is a unique 13-character code (format "RO###########"), the pallet quantity will always be <999, and the pallet ID will always be a 4-6 digit number or a 4-6 digit number with a -ID modifier code (for example and ID will be 14858, but may be 14858-90 with a 1-3 digit modifier after the "-"). ...

Here's a test:
Change LogFile := A_ScriptDir "\terminal-12-17-2019.log" to the path of a given log file
Also.... OutputFile := A_ScriptDir "\test-output.txt" saves the extracted data to the script's directory
NOTE: I added a condition that ignores duplicate RO#'s, please let me know if this is desired

Code: Select all

LogFile 	:= A_ScriptDir "\terminal-12-17-2019.log" 	; points to the log file
OutputFile 	:= A_ScriptDir "\test-output.txt"			; points to the output file

if FileExist( OutputFile ) 
	FileDelete % OutputFile

Loop, Read, % LogFile, % ( OutputFile, PidArray := [] )
{
	Match 	:= 	RegExMatch( A_LoopReadLine
			, 	"^.*?(?P<pid>RO\d{11}).*?`,.*?(?P<quantity>\d{1,3}).*?`,.*?(?P<id>\d{4,6}.*?)(\[CR\]|:)"
			, 	pallet_ )
	
	if ( pallet_pid )
		PidArray.push( pallet_pid )

	if ( Match && ( !InStr( A_LoopReadLine, PidArray[ PidArray.length()-1 ] ) || PidArray.length() = 1 ) ) 
	{
		FileAppend 	% "`n-------"
					. "`nLog Line#: " 	a_index
					. "`nRO#: "			pallet_pid
					. "`nQuantity: " 	pallet_quantity
					. "`nID#: " 		pallet_id 
	}
}

Does this test extract the correct data?

Also, is the xlsx book used for record keeping or are you just using it to process the csv file?

[Chris70]

TLM -

Thank you for the post. I will work with what you have sent, but I have to make sense of all of it first (this is all Greek to me right now). I have scripts saved on my desktop currently, as well as in some other folder locations, that I have been testing and using to familiarize myself with the coding language. So, there is not one specific directory that the scripts are saved in, so I am not sure if I need to keep all my scripts in one folder location for what you sent to work or not. Also, do I need to have the log file saved to that directory first or how does the script know where the log file is to work with it?

Also, the Excel file would be for record keeping purposes, yes, but I also saw the need for it possibly to enable the data fixing portion mentioned in my Problem 3 with having to lookup the standard pallet quantity (which I have in an Excel table that can be extracted using the Product ID as the lookup key) and insert the missing pallet quantity data accordingly.

Lastly, would it be possible for you to comment in every line of code what it does so I can follow it? I am not a programmer other than using ladder logic for PLC automation, so I am all over AHK tutorials trying to learn, but it's kicking my butt!

Thanks!

Chris

[TLM]

The test-output.txt file saves to the same directory as where the script is run.

...how does the script know where the log file is...

Change the LogFile variable to point to the log file.
For instance if the log file is on the Desktop:LogFile := A_Desktop "\terminal-12-17-2019.log"
............. if the log file is in My Documents:LogFile := A_MyDocuments "\terminal-12-17-2019.log"
.............. if the log file is on the C:\ Drive:LogFile := "C:\terminal-12-17-2019.log"
........ if the log file is in a program's folder:LogFile := "C:\Program Files (x86)\Some Program\Some Directory\terminal-12-17-2019.log"

You can change the log file name to test the extraction on different logs ( recommended ):
LogFile := A_ScriptDir "\some-other-log-name.log"

It would be a good idea to test this out first then move on to other aspects of what you're after

[Chris70]

TLM -

Ok, I just moved the log file to my desktop where the script file is located. I tested it and it did extract the data, however it did miss some scans that fall into the problem 2 & problem 3 categories. Meaning, your scrip grabbed all of the "ideal" scans (the ones with the data in the exact format desired, Pallet ID > Pallet Quantity > Product ID). So, for all of the scans that had data in different arrangements, your script ignored them. What can be done to capture those scans as well? For example, check the log file for Pallet ID RO00585501012. This ID is only in the log file two times and both times it demonstrates to the two outlier scans that we seem to encounter - 1) The scan order varies from the desired format or 2) the pallet quantity is missed altogether and replaced with a 10-character "RO########" string. In the 1) scenario, I would want to grab the data and re-order it before appending it to the output file. In the 2) scenario, I would want to replace the 10-character text string with the associated standard pallet quantity associated with the Product ID (I could provide an excel file with this data if that would help).

BTW - I modified your code a bit to make the output file format look like a .csv file (code below and output file attached for reference).

Please let me know what your thoughts are to address scenarios 1) & 2) above.

Thanks for all your help!

Chris

Updated code:

Code: Select all

LogFile 	:= A_ScriptDir "\terminal-12-17-2019.log" 	; points to the log file
OutputFile 	:= A_ScriptDir "\test-output2.txt"			; points to the output file

if FileExist( OutputFile ) 
	FileDelete % OutputFile

Loop, Read, % LogFile, % ( OutputFile, PidArray := [] )
{
	Match 	:= 	RegExMatch( A_LoopReadLine
			, 	"^.*?(?P<pid>RO\d{11}).*?`,.*?(?P<quantity>\d{1,3}).*?`,.*?(?P<id>\d{4,6}.*?)(\[CR\]|:)"
			, 	pallet_ )
	
	if ( pallet_pid )
		PidArray.push( pallet_pid )

	if ( Match && ( !InStr( A_LoopReadLine, PidArray[ PidArray.length()-1 ] ) || PidArray.length() = 1 ) ) 
	{
		FileAppend 	% pallet_pid
					. "," 	pallet_quantity
					. "," 	pallet_id 
					. "`n"
	}
}

[TLM]

I tested it and it did extract the data, however it did miss some scans that fall into the problem 2 & problem 3 categories.

Ok I changed the test script from trying to match all the criteria at once,
to 1st matching just the 13 character product ID and filtering duplicates,
then searching each line for the other matches ( in any order ).
I also added a search for the 10 character product ID's,
matched the time stamp ( in separate units ), flags, ACC, and CR,
and left a placeholder condition that can be used to search the standard quantity workbook ( which will be needed ).
Everything is saved to a .csv file ( test-output.csv ) for record keeping etc.

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
CSVHead 	:= "Hour,Min.,Sec.,Pallet ID,Quantity,Product ID,Short Pal. ID,Flag,Acc,CR" ; column headings

if FileExist( OutputFile ) ; if output file exists...
	FileDelete % OutputFile ; ...delete it

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 
	{
		; Regular Expression Array to search for each value
		NeedleArray := { 	timeunits 	: "^(?<hour>\d+):(?<min>\d+):(?<sec>\d+).*?-"
					, 		quantity 	: "[-`,](?<quantity>\d{1,3})\D"					
					, 		prid 		: "\D(?<prid>\d{5}(-\d{1,3}|))\D"
					, 		spid 		: "\D(?<spid>RO\d{8})\D"
					, 		flag 		: ":(?<flag>\d+):\d+%"
					, 		acc 		: "\D(?<acc>\d+%)"
					, 		cr 			: ":(?<cr>\d+)\[CR\D" }

		For Each, Needle in NeedleArray ; matches each value in current line
			RegExMatch( A_LoopReadLine, Needle, pallet_ )
 
/*
		if ( pallet_spid && !pallet_quantity ) ; short pallet ID & no quantity placeholder
		{
			; open psid workbook and search for standard quantity
		}

*/
		FileAppend 	% 	pallet_hour "," pallet_min "," pallet_sec ","
					. 	pallet_pid "," pallet_quantity "," pallet_prid ","
					. 	pallet_spid "," pallet_flag "," pallet_acc "," pallet_cr "`n"
	}
}

I'll explain how everything works but for now please test again and let me know if there's missing in the CSV.

I would want to replace the 10-character text string with the associated standard pallet quantity associated with the Product ID (I could provide an excel file with this data if that would help).

In the commented section the condition looks for the "short pallet ID" pallet_spid and no quantity !pallet_quantity.
This section can be used to search the excel file with the standard pallet quantities.
It would be great if you could provide it

[TLM]

just noticed by chance that in some scans where there's a `short` pallet id and no quantity,
it shows up in a later line with a duplicate pallet id ( which is ignored ).

it shouldn't be too hard to add logic/functionality for this though

[Chris70]

TLM -

The latest revision worked to capture all of the unique pallet IDs and none were missed that I could find by coming both files. This is excellent! Thank you.

As far as the data to include in the .CSV file, I really only need the Pallet ID, Pallet Quantity, Product ID, and a new lookup reference for Location (the rest is unnecessary information and can be dropped including the short pallet id). I have attached an Excel file that provides the data table for cross referencing the standard pallet quantity and default location by Product ID. The location value will be one of three location codes at the moment ("TBD", "RD", or "WF"), but the data table will change as we move product around and assign new default locations. The next iteration I would like to use the lookup table to provide the missing pallet quantity information (when needed) and the default location. In other words, each line in the .CSV will need to have the corresponding location for each product ID (this data is not contained in the barcode data at all).

I would also like to perform some log file/output file cleanup and archive function so as to create batch files to use for automating the production scan receipt. Something like: use a standard file name for "live" log file as "terminal.log" and each time the source file is accessed it appends the data to a master log file called "master.log" and then performs the filtering to the output file and saves the output file to "Scan_Log_{date}_{24-hr time}" to create a unique batch file and then clears the "live" log file so the next time it runs it only outputs the data gathered since the last batch. Does that make sense?

This has been tremendously helpful, thank you so much for all your efforts!

Have a Merry Christmas (or whatever holiday you may celebrate this time of year)!

Chris

[TLM]

Test calling the std pallet qty-loc records.

I just want to be 100% sure here so plz correct me if I'm wrong:
Scan > ( RO00585289079 ), ( *missing qty* ), ( 70255 )
Reference > ( 70255 ), ( 24 ), ( 4S ), ( TBD )

This would be the standard qty for that pid ??

[Chris70]

I just want to be 100% sure here so plz correct me if I'm wrong:
Scan > ( RO00585289079 ), ( *missing qty* ), ( 70255 )
Reference > ( 70255 ), ( 24 ), ( 4S ), ( TBD )

This would be the standard qty for that pid ??

TLM -

Yes, the "24" would be the quantity and "TBD" would be the associated location code to insert at the end of that data line. You can ignore the column for warehouse that either has "4S" or "5S" as a value (that is used for another purpose).

Thanks!

Chris

[TLM]

I placed the standard quantity sheet in the script's folder and pointed to it like this:StdPltQty := A_ScriptDir "\Std pallet qty-loc.xlsx"
Feel free to adjust this location to where your Std pallet qty-loc.xlsx is!

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
StdPltQty 	:= A_ScriptDir "\Std pallet qty-loc.xlsx" 	; points to the standard quantity file

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

if FileExist( OutputFile ) ; if output file exists...
	FileDelete % OutputFile ; ...delete it

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

		if ( pallet_spid && !pallet_quantity ) ; short pallet ID & no quantity
		{
			if ( !IsObject( xlObj ) )
			{
				xlObj 		:= ComObjCreate( "Excel.Application" )
				xlObj.Workbooks.Open( StdPltQty )
			}		

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

			pallet_loc 		:= xlObj.Range( "D" QtyRowObj.Row ).Value
			pallet_quantity := Floor( xlObj.Range( "B" QtyRowObj.Row ).Value )
		}

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

xlHwnd := xlObj.Hwnd
( xlObj.Quit ), xlObj := ""
WinGet, xlPID, PID, % "ahk_id " xlHwnd
Process, Close, % xlPID

This should grab the missing quantities & associated location codes only for the missing qtys.
I stress tested the log with success:18:07:37 <-RO00565230,RO00585289078,20707:01:100%:82[CR]

[Chris70]

TLM -

OK, so the quantity lookup appears to be OK. However, the resulting file only put location codes for 3 of the pallets for some reason (see file attached). And, those three pallets all had the same data configuration: 1st scan = short Pallet ID (10-characters starting with "RO"), 2nd scan = full pallet ID (13-characters starting with "RO"), and 3rd scan = product ID. Is there a way to get it to input the location code reference for every pallet keyed off the product ID to the lookup file? Otherwise, this looks great!

Thanks again. You really are a lifesaver on this!

Chris

[TLM]

I held back all the location codes on purpose to make sure everything was working lol

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
StdPltQty 	:= A_ScriptDir "\Std pallet qty-loc.xlsx" 	; points to the standard quantity file

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
}

if FileExist( OutputFile ) 	; if output file exists...
	FileDelete % OutputFile ; ...delete it

if ( !IsObject( xlObj ) )
{
	xlObj := ComObjCreate( "Excel.Application" ), xlObj.Visible := False
	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 )

		Try ( pallet_loc := xlObj.Range( "D" QtyRowObj.Row ).Value )
		Catch
			TrayTip, Warning!, % "Product ID: " pallet_prid " Skipped!`nLocation Code Not Found!",, 0x2

		if ( pallet_spid && !pallet_quantity ) ; short pallet ID & no quantity
			pallet_quantity := Floor( xlObj.Range( "B" QtyRowObj.Row ).Value )

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

DetectHiddenWindows, On

xlHwnd := xlObj.Hwnd
xlObj.Workbooks.Close
( xlObj.Quit ), xlObj := ""

WinGet, xlPID, PID, % "ahk_id " xlHwnd
Process, Close, % xlPID

If for some reason the product ID doesn't exist or can't be found it's skipped & you'l be alerted

[Chris70]

TLM -

Sorry for the delayed response, I was off the past couple of days for Christmas. I came in this morning and tested the latest revision (Test Filter-5.ahk and resulting output file test-output5.csv attached for reference). There is some sort of error that has been introduced into the output file on this version as I get the following error when I try to open the file:

Test Filter-5 file error message 12-26-2019.JPG

(9.25 KiB) Downloaded 178 times

I am looking through the code to see if I can make enough sense of it to see where the error is being created, but I am still very new at this. If you can figure out what when wrong, that would be awesome!

Thanks!

Chris

[TLM]

I'm unable to reproduce that error in 2 different versions of Excel ..

....I ran a version diff and one thing that may affect the CSV is the addition of a carriage return:
FileAppend % pallet_pid "," pallet_quantity "," pallet_prid "," pallet_loc "`r`n"
Revert to this:
FileAppend % pallet_pid "," pallet_quantity "," pallet_prid "," pallet_loc "`n"

CSV files are plain text files that can be opened in a text editor. If the problem persists,
try opening an output file into a text editor, save it, then try opening it into Excel again.
This will indicate whether corruption is coming from the script or not.

Let me know what happens..

[Chris70]

TLM -

OK, so I tried running the script again with the modification and didn't even try opening it with Excel, but opted to open in Notepad first to test it. It gave an error saying that it could not open because it was still being used by another process. Then, I noticed the script was still running in the system tray. Looking back at the code I copied from the latest version you posted, it was missing a final return at the very end and therefore I think the script was just not ending and holding the output file hostage as a result. I simply added a return statement at the end and that fixed it! All appears to be there now with the inserted missing quantities and the correct ID-Location mapping. Awesome!!!

Now, I would also like to perform some log file/output file cleanup and archive function so as to create batch files to use for automating the production scan receipt. Something like: use a standard file name for "live" log file as "terminal.log" and each time the source file is accessed it appends the data to a master log file called "master.log" and then performs the filtering to the output file and saves the output file to "Scan_Log_{date}_{24-hr time}" to create a unique batch file and then clears the "live" log file so the next time it runs it only outputs the data gathered since the last batch. Does that make sense? There may be a better way to do that, but what I believe I want is to have a unique batch file for each time the script is run (this will be used in sending unique batch data to our ERP system at regular intervals) and a master file that keeps appending all the data for archive purposes.

Lastly, and this may be a super dumb question, but I am used to ladder logic programming, so I process everything linearly (left to right, top to bottom). PLC code just runs top to bottom (only jumping around for function calls or nested goto statements, etc.). I assume scripts run similarly, correct? The reason I am asking is that I already have a script that is running on a work station and I want to have this script of yours run in sequence with that one. I was thinking I could just insert this file output filter script in the same code (which just loops on a timer) to automatically save the output files to the workstation and a copy to a network file location (which is where our ERP system would pick it up and process it) periodically.

My continued many thanks for all your work on this!

Chris

[TLM]

Sorry for the slow response as I became extremely busy and wasn't able to reply.

I'm glad you tested this out again, you confirmed a some important things I think you should be aware of.

...I noticed the script was still running...

I've also run into an erroneous issue that I believe stems from the script's usage of an Excel COM object.
This object is used to:

• Read the Standard Pallet Quantity Location worksheet:

  Code: Select all

  	xlObj := ComObjCreate( "Excel.Application" ), xlObj.Visible := False
  	xlObj.Workbooks.Open( StdPltQty )

• Take advantage of Excel's Find functionality to quickly look up and return a Row Object:

  Code: Select all

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

• Use this Row Object to find the associated location codes:

  Code: Select all

  			pallet_loc := xlObj.Range( "D" QtyRowObj.Row ).Value

• Use this Row Object find the missing quantities:

  Code: Select all

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

While all of this is happening, the CSV file ( OutputFile ) is `held` open and only accessible by the script: Loop, Read, % LogFile, % ( OutputFile...

OutputFile
(Optional) The name of the file to be kept open for the duration of the loop, .....

Within the loop's body, use the FileAppend command with only one parameter (the text to be written) to append to this special file. Appending to a file in this manner performs better than using FileAppend in its 2-parameter mode because the file does not need to be closed and re-opened for each operation.

The problem is that sometimes the script does not destroy the Excel client when it should for some reason.
This can potentially make the script `hang` and `hold up` the CSV file.
As a temporary precautionary workaround, I added commands to kill the Excel client:

Code: Select all

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

This should work most of the time but may fail.
If it does, there may be left over Excel clients in memory ( check windows task manager for lingering Excel.exe )
and the CSV file may never be released until the script exits.

I'm looking into using some specific events and methods in Excel ensure it's client closes as it should....

I would also like to perform some log file/output file cleanup and archive function so as to create batch files to use for automating the production scan receipt.

Do you mean perform the same sanitation as in the CSV script?
Can you provide an example of the `batch file` format?

...I process everything linearly (left to right, top to bottom). PLC code just runs top to bottom (only jumping around for function calls or nested goto statements, etc.). I assume scripts run similarly, correct?

The script reads and looks for pallet ids in the log file line by line, then looks for the quantity, the product id matches and the location per line. Not sure if this helps clarify how the work flows differ but I'm sure we can figure out a way to make everything work together.

[Chris70]

TLM -

I got tied up on the floor today. I will walk through the steps and provide sample files of what I think I am looking for and let you figure out how to make it happen! So far, you have been on point with everything and I really appreciate it.!

[Chris70]

TLM -

I have attached an Excel file which shows what I believe would be the progression of the file sequence I would want. So, here is how I am seeing this working:

1. Assuming I am saving live data to a .log file like before and it is updating constantly, every three minutes the script will interrupt the script that is saving the data (need interrupt script logic for this), read the log file, create a .csv file of the data and write it to a master data file.
2. The first time it runs, the master data file (Startup Batch File 1) will be the initial batch file (sample is tab "Startup Batch File 1). This file will be copied to a network folder for batch processing in our ERP system (and later archived by the ERP system once processed, outside the scope of this script).
3. The second time it runs, the updated master data file is compared to the startup master data file (Startup Batch File 1), and a new batch file is created (Batch File 2) from just the new scans added since the last update (see tabs "Master File Update 1" and "Batch File 2" for sample of this). The Batch File 2 will be copied to a network folder for processing by the ERP system.
4. The third time it runs, the updated master data file (Master File Update 2) is compared to the previous master data file (Master Update 1), and a new batch file is created (Batch File 3) from just the new scans added since the last update (see tabs "Master File Update 2" and "Batch File 3" for sample of this). The Batch File 3 will be copied to a network folder for processing by the ERP system.
5. The process would repeat (step 4) until the script is stopped and re-started. So, I see the script creating a sort of shift register to compare the previous master file to the current master file and extracting just the new scan data with each iteration to create a unique batch file for the new scans only.

I hope this makes sense.

Let me know if you have any questions and then of course help me with the script logic!

Thanks as always for your assistance!

Chris

[TLM]

Sorry about my absence I was away for the latter part of the holidays.

With regards to the project, I separated the existing script into the least amount of redundant functions.
This way, reading and cleaning logs, grabbing missing quantities/locations and saving the output can be approached in easy to debug steps.
I also figured out that the reason the script wasn't fully closing clients and holding up the output file was because I had unreleased object references.
I 1st cleared each reference ( which worked ), then only assigned object references within functions, limiting their scope to a given function.

New Wrapped Script To Test

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

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

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

ExitApp

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;;; 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() ] 
}

Anyway onto the next step...

...I am saving live data to a .log file like before and it is updating constantly, every three minutes the script will interrupt the script that is saving the data

This is hard to answer as it would depend on whether the log can be read while it's being written.
A simplified way to determine if AutoHotkey can achieve this would be to try to open the log into a text editor.
You can also try this:

Code: Select all

LogFile 	:= A_ScriptDir "\terminal-12-17-2019.log" ; << change this to the name of the log file being written
FileRead, LogContents, % LogFile 

if ( LogContents )
	Msgbox % "Log:`n" SubStr( LogContents, 1, 500 ) ; << displays the 1st 500 characters of the log file 
else 
	Msgbox No log contents found!

change terminal-12-17-2019.log to the name of the log file being written ( if any ).

....master data file is compared to the startup master data file... The process would repeat ... until the script is stopped and re-started...

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?

You can test a line by line comparison by running this command in a command line:
Keep the file paths in quotes

Code: Select all

FC "**path to output file 1**" "**path to output file 2**"

Lemmi know!