most efficient way to compare files and remove lines

Get help with using AutoHotkey and its commands and hotkeys
eekhelpspike
Posts: 10
Joined: 05 Jul 2015, 17:51

most efficient way to compare files and remove lines

Post by eekhelpspike » 06 Jul 2015, 09:53

I'll try not to get into too many boring details here.
Spoiler
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
Posts: 61
Joined: 04 Oct 2013, 03:07
Location: Germany near Cologne

Re: most efficient way to compare files and remove lines

Post by hd0202 » 06 Jul 2015, 11:42

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
User avatar
T_Lube
Posts: 67
Joined: 22 Oct 2014, 00:57

Re: most efficient way to compare files and remove lines

Post by T_Lube » 07 Jul 2015, 02:49

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
Posts: 10
Joined: 05 Jul 2015, 17:51

Re: most efficient way to compare files and remove lines

Post by eekhelpspike » 07 Jul 2015, 17:29

hd0202 wrote:My suggestion:
T_Lube wrote:I think that this is what you want, yes?
Lovely. Thanks, guys.
Post Reply

Return to “Ask For Help”