Compare Excel file and make a list of new value

[maitresin]

Hi,

I want to make a script and compile it into executable and run it through task scheduler each +1hour 10 minutes
Idea is to compare file.xls with the previous one and create a .txt with the new value
Loop column "A" of file.xls with the previous one and store the new value and past them into the file.txt (value not found in the previous file.xls)
Values in column "A" are text EX: 222-1111, 222-4444
Average of 6000 rows per file

IE: compare 2020--> 08 --> 27 --> 01-00 -->file.xls vs 2020--> 08 --> 27 --> 00-00 -->file.xls

How to identify the file to compare? check the windows clock. If clock is 15:XX compare file from 15-00 vs 14-00

file.txt can be saved into the same folder of the current file.xls


I have 1 excel file generated each +1 hour.

They are saved in folder Year --> Month --> Date --> Hour
EX:
folder-->2020--> 08 --> 27 --> 00-00 -->file.xls
folder-->2020--> 08 --> 27 --> 01-00 -->file.xls
folder-->2020--> 08 --> 27 --> 02-00 -->file.xls
folder-->2020--> 08 --> 27 --> 03-00 -->file.xls
...
2020--> 08 --> 27 --> 23-00 -->file.xls


Thanks!

[AlphaBravo]

this code is based on the assumption that Column "A" does not have gaps on any of your excel sheets, it will find values listed on newest "file.xls" not found on previous "file.xls"

Code: Select all

SetBatchLines, -1
Filelist := ""
Loop, Files, % A_Desktop "\Folder\*.*", FR		; Change "A_Desktop" to your desired folder path
	if InStr(A_LoopFileFullPath, "file.xls")
		Filelist .= A_LoopFileFullPath "`n"
Sort, Filelist, R
; -----------------------
Loop, parse, Filelist, `n, `r
{
	file%A_Index% := A_LoopField
	if A_Index = 2
		break
}
; -----------------------
xl := ComObjCreate("Excel.Application")
;~ xl.visible := true
; -----------------------
xl.Workbooks.Open(file2)						; previous file
row := 0, values := []
while (v := xl.cells(++row, 1).value)
	values[v] := true
xl.ActiveWorkbook.Close
; -----------------------
xl.Workbooks.Open(file1)						; current file "newest"
row := 0, Text := ""
while (v := xl.cells(++row, 1).value)
	if !values[v]
		text .= v "`n"
; -----------------------
Sort, Text										; optional
xl.ActiveWorkbook.Close
xl.quit()
SplitPath, file1, FileName1, Folder
FileDelete, % folder "\file.txt"
FileAppend, % text, % folder "\file.txt"
Run % folder "\file.txt"						; optional
ExitApp
return

[maitresin]

Perfect it's working!

However, I'm wondering why it show value with 6 digit in the file.txt ? EX: 23.000000

Value should be as text and return 23 and not 23.000000

With a Xl.Range("A:A").copy it return 23 and not 23.000000

Anyway to fix this in your code?

Thanks!

[flyingDman]

The problem with this approach is that it is slow if the files are 6000 rows long ... Try using safearrays instead like this:

Code: Select all

xl := ComObjCreate("Excel.Application")
file1 := a_scriptdir "\test123A.xlsx"
file2 := a_scriptdir "\test123B.xlsx"

xl.Workbooks.Open(file2)						; previous file
sarr1 := xl.range("A:A").value
xl.ActiveWorkbook.Close

xl.Workbooks.Open(file1)						; current file "newest"
sarr2 := xl.range("A:A").value
xl.ActiveWorkbook.Close

xl.quit()

while (sarr1[a_index,1] or sarr2[a_index,1])
	if (sarr1[a_index,1] != sarr2[a_index,1])
		text .= sarr1[a_index,1] " <> " sarr2[a_index,1] "`trow: " a_index "`n"

msgbox % Text

Instead of 15 seconds it takes less than 4.
Regarding 23.000000 vs 23. You said your entries were text.... In @AlphaBravo 's script replace .value with .text. That will not work in my script. But you can round or use strreplace in the output.

[AlphaBravo]

@flyingDman, I totally agree SafeArray would be a faster approach however your code is comparing row to row - which maybe what the OP is asking for - it's just not what I understood from reading his request :

(value not found in the previous file.xls)

my approach finds values in newer file not found in previous file regardless of their rows!

@maitresin please clarify.
also regarding your request, as flyingDman suggested, replace all .value with .text

[flyingDman]

@AlphaBravo yes, that's correct, the OP needs to clarify. In my script if new rows are added, they will be reported but also any changes on a row by row basis, but if the existing values get resorted or if a row is inserted somewhere, these changes will also be reported. That's how I first interpreted it, but that's not necessarily what was meant.

[maitresin]

Hi,

Thanks for help on this script it is very appreciated

Clarification:

my approach finds values in newer file not found in previous file regardless of their rows!

Yes, regardless of the rows

Code: Select all

.text

Yes, because everything paste in the file is in "TEXT" value, regardless if it is numbers or text.

[flyingDman]

This uses the same safearray technique but looks for any new item in the file rather than looking a any differences on a row by row basis. The speed benefits still apply:

Code: Select all

xl := ComObjCreate("Excel.Application")

for x,y in ["test123A.xlsx","test123B.xlsx"]                   ;A:new, B:previous 
	{
	xl.Workbooks.Open(a_scriptdir . "\" . y)
	lstrw%x% := xl.Range("A" xl.Rows.Count).End(-4162).row     ;last row in the column this allows for blank rows 
	sarr%x% := xl.range("A1:A" lstrw%x%).value                 ;creates an array for each of the files
	xl.ActiveWorkbook.Close
	}
xl.quit()
xl := ""

loop, % lstrw2
	mtchlst .= sarr2[a_index,1] ? sarr2[a_index,1] "," : ""    ; creates a comma separated match list

loop, % lstrw1
	if (needle := sarr1[a_index,1])
		if needle not in %mtchlst%
			text .= needle "`n"                                ; if cell consist of integers even if cell is text use strreplace(needle,".000000")

msgbox % Text