most efficient way to compare files and remove lines

[eekhelpspike]

I'll try not to get into too many boring details here.

Spoiler

Essentially I have software that generates orders to be filled. I have a new machine that is supposed to automate some of the filling of those orders. The software has the capability to create an export file (CSV) of orders that need to be filled, so that the machine knows what orders to fill. The software marks the orders as "exported". The machine evaluates the file, fills everything in its purview, then communicates back to the software what orders it fulfilled, and the software marks those orders as packaged, so that labels can be generated by the software for manual fulfilling of orders that haven't been packaged yet. The first problem was that while the software would mark an order as "exported", so that it knew not to send an order to the machine twice, the machine was not evaluating whether or not orders exported had been manually "packaged". We put in a request to both the software vendor and the machine manufacturer to fix this (software- include an option to not export items already "packaged", machine- query the software database before packing each order to make sure it wasn't already "packaged"). The software vendor will take six months before our requested fix is put in. The machine vendor turned it around very quickly, but now the orders are processed so slow that it makes it almost unusable (I guess from how they are performing their query).

OK, that was too many details.

I need to either:
take a file that I've generated by a sql query to the database that our software is running off of that contains a list of order numbers (not yet packaged). I've had luck using adosql. Compare it to an exported CSV file created by that software and remove any of the lines that correspond to order numbers in the first file.
so first file contains

123456
123458

second contains

123456,a,b,c,d
123457,e,f,g,h
123458,i,j,k,l
123459,m,n,o,p

I need the resulting file to contain

123456,a,b,c,d
123458,i,j,k,l

I could do it the other way around, and remove orders already completed. But by the end of the day, there will be a lot of them.

or
build my own export file from scratch. I would've done this from the start, but the export file contains something that I can't figure out how to recreate. Basically there is a field the user fills out when entering an order that uses a combination of quick codes and free text. The export file contains a combination of expanded quick codes, looked up in a quick codes table in the database, and free text.
The former seems more obtainable.

[hd0202]

My suggestion:

Code: Select all

file1 = %a_scriptdir%\datafile1.txt
file2 = %a_scriptdir%\datafile2.txt
file3 = %a_scriptdir%\outfile.txt
;
; generate testfiles
data1 =
(
123456
123458
)
filedelete, % file1
fileappend, % data1, % file1
data2 =
(
123456,a,b,c,d
123457,e,f,g,h
123458,i,j,k,l
123459,m,n,o,p
)
filedelete, % file2
fileappend, % data2, % file2
;
; processing file1
tab := {}
fileread, in1, % file1
loop, parse, in1, `n, `r
{
	if !a_loopfield
		continue
	tab[a_loopfield] := 1
}
;
; processing file2
out =
fileread, in2, % file2
loop, parse, in2, `n, `r
{
	if !a_loopfield
		continue
	stringsplit, field, a_loopfield, `,
	if tab.haskey(field1)		; or: if !tab.haskey(field1)
		continue
	out .= a_loopfield "`n"
}
filedelete, % file3
fileappend, % out, % file3
msgbox, % out		; test output
exitapp

Hubert

[T_Lube]

You could look into using regexMatch:

Code: Select all

 ; fill in your file paths below
FirstFilePath := ""
SecondFilePath := ""
ThirdFilePath := ""

Loop, Read, %FirstFilePath%
	preNeedle .= (preNeedle ? "|" : "") . A_LoopReadLine
Sort, preNeedle, UD|
fullNeedle := "^(" . preNeedle . ")"
Loop, Read, %SecondFilePath%
{
	if regexMatch(A_LoopReadLine, fullNeedle)
		FileAppend, %A_LoopReadLine%`n, %ThirdFilePath%
}
Return

I think that this is what you want, yes?

[eekhelpspike]

My suggestion:

I think that this is what you want, yes?

Lovely. Thanks, guys.