[SOLVED] Fast compare between 2 text files

marko001 · July 4, 2011

Hi all,

I have two text files I need to compare:

Every file contains a "converted" list of records separated by proper prefix:

<z:row First_Name='aaa' Last_Name='bbb' Country =' ' Age=' '

I need to check File A (Source) and File B (Dest) and do the following:

check line by line if record is equal (so check First Name, Last Name, Country, Age).

If Equal - do nothing

If different or doesn't exist, ADD at bottom of Dest file.

I did it with IF cycle:

Func _merge($user)
    $DBfile         = @ScriptDir & "\" & $user & ".xml"
    $mergingfile    = @ScriptDir & "\exported.xml"
    ProgressOn("Merge", "Merging DB", "0 %")
    FileOpen($mergingfile,0)
    FileOpen($DBfile,1)
    $lines_export   = _FileCountLines($mergingfile)
    $lines_db       = _FileCountLines($DBfile)
for $i = 34 to _FileCountLines($mergingfile)-2 ; first 34 lines and last 2 are unuseful
    $line_export = FileReadLine($mergingfile,$i)
    $FirstName_exp =    _StringBetween($line_export,"<z:row c0='","' Last")
    for $k = 34 to _FileCountLines($DBfile)-2 ; first 34 lines and last 2 are unuseful
        $line_db = FileReadLine($DBfile,$k)
        $FirstName_db =     _StringBetween($line_db,"<z:row c0='","' Last")
        if $FirstName_exp[0] = $FirstName_db[0] Then;   SAME NAME
            ; and here I go down searching every item for that record. if all "IF" are verified, the record is equal, if not needs to be added
        Else
            if $k = _FileCountLines($DBfile)-2 Then
                _FileWriteToLine($DBfile,_FileCountLines($DBfile)-1,$line_export,0)
                ConsoleWrite("Added - " & $FirstName_exp[0] & @CR)
            EndIf
        EndIf
    Next
    ProgressSet(round($i/$lines_export*100,0),Round($i/$lines_export*100,0) & " %")
Next
FileClose($mergingfile)
FileClose($DBfile)
ProgressSet(100 , "Done", "Merging Complete")
ProgressOff()
FileDelete($mergingfile)
EndFunc

But for 1.500 records file it takes over 15 mins. I need to operate also with +10.000 lines files.

Is there a fastest way?

Thanks,

Marco

Edited July 4, 2011 by marko001

Melba23 · July 4, 2011

marko001,

I would read the files into arrays and then deal with it in memory.

I created a 1500-line file with this code:

$hFile = FileOpen("List_1.txt", 1)
For $i = 1 To 1500
    FileWriteLine($hFile, "<z:row First_Name='" & $i & "' Last_Name='" & $i & "' Country =' ' Age=' '")
Next
FileClose($hFile)

I copied it to a second file and deleted a few lines to force some errors. Then I ran this code to compare the two files:

#include <File.au3>
#include <Array.au3>

; Declare the 2 arrays
Global $aFile_1, $aFile_2

; Start a timer
$iBegin = TimerInit()

; Read the 2 files into the arrays
_FileReadToArray("List_1.txt", $aFile_1)
_FileReadToArray("List_2.txt", $aFile_2)

; For the lines you want
For $i = 35 To $aFile_1[0] - 2
    ; Does this line in the forst file exist in the second
    _ArraySearch($aFile_2, $aFile_1[$i])
    If @error Then
        ; If not then add to the second
        _ArrayAdd($aFile_2, $aFile_1[$i])
        $aFile_2[0] += 1
    EndIf
Next

; Write the new file = second file plus additional entries
_FileWriteFromArray("List_Combined.txt", $aFile_2, 1)

; Display the time
ConsoleWrite("Time taken = " & Round(TimerDiff($iBegin) / 1000, 2) & " secs" & @CRLF)

Takes less than 3 secs for it to check the 1500 lines and the final file has all the missing entries added.

Will that do?

M23

Aktonius · July 4, 2011

Damn it Melba, do you ever get tired of helping? lol

Melba23 · July 4, 2011

do you ever get tired of helping?

No, or I would no longer be here.

M23

marko001 · July 22, 2011

Melba, I came here again since I was trying again what you wrote me, but:

if the two lines differs for just a value (aaa|bbb|ccc and aaa|bbb|ddd) it's an error for me to duplicate it.

Instead, if "aaa" AND "bbb" exists, I have to subst "ccc" with "ddd"

SO with your solution i'd have:

line n : aaa|bbb|ccc

line m : aaa|bbb|ddd

when I need

line n: aaa|bbb|ddd

IS it possible without spending hrs with nested for?

M.

Zedna · July 24, 2011

You may use SQLite instead of arrays:

Here are links to some of my SQLite examples:

Edited July 24, 2011 by Zedna

Kenna · July 24, 2011

You may use SQLite instead of arrays:
Here are links to some of my SQLite examples:

Thing is, pure memory reads and writes between 1500-3000mb/s depending on your speed.

Way faster than anything else period.

Melba23 · July 24, 2011

marko001,

It makes life much easier if you explain what you want at the beginning of the thread rather then moving the goalposts as we go along.

So you only want to check the first 2 elements and then overwrite any existing matches. Then you need to do a partial search of the existing file.

I created 2 files to test - this is the original list:

$hFile = FileOpen("List_1.txt", 2)
For $i = 1 To 1500
    FileWriteLine($hFile, "<z:row First_Name='" & $i & "' Last_Name='" & $i & "' Country =' ' Age=' '")
Next
FileClose($hFile)

and this is the one with changes and one new line at the end:

$hFile = FileOpen("List_2.txt", 2)
For $i = 1 To 33
    FileWriteLine($hFile, "ignore")
Next
For $i = 1 To 1600 Step 100
    FileWriteLine($hFile, "<z:row First_Name='" & $i & "' Last_Name='" & $i & "' Country ='New' Age='New'")
Next
FileClose($hFile)

Then we can use this script to produce a consolidated file where the original names have been updated and the new one added:

#include <File.au3>
#include <Array.au3>

; Declare the 2 arrays - List_1 is the orginal file, List_2 is the newer one we are checking
Global $aFile_1, $aFile_2

; Read the 2 files into the arrays
_FileReadToArray("List_1.txt", $aFile_1)
_FileReadToArray("List_2.txt", $aFile_2)

; Create master array to hold result by copying file 1 contents
$aFile_Result = $aFile_1

; Run through the newer
For $i = 34 To $aFile_2[0]
    ; Extract the first elements of the line from the new file
    $sExtract = StringRegExpReplace($aFile_2[$i], "(?i)(?U).*\x20(.*)\x20country.*\z", "$1")
    ; Does this extract from the new file exist in the original file
    $iIndex = _ArraySearch($aFile_1, $sExtract, 0, 0, 0, 1)
    If @error Then
        ; Extract does not exist so add to the result
        _ArrayAdd($aFile_Result, $aFile_2[$i])
        $aFile_Result[0] += 1
    Else
        ; Extract exists so we need to overwrite current line
        $aFile_Result[$iIndex] = $aFile_2[$i]
    EndIf
Next

; Write the new file = second file plus additional entries
_FileWriteFromArray("List_Result.txt", $aFile_Result, 1)

Are you happy now?

M23

P.S. SRER explanation:

(?i)             - case insensitive
(?U)             - inverse greediness so we look for the smallest matches
.*\x20           - any number of chars followed by a space (this reads the "<z:row " part before the bit we want)
(.*)             - capture the characters (the bit we want) up to...
\x20country.*\z  - a space followed by "country" and any other characters to the end of the line (this reads everything after the bit we want)

$1               - use the first captured group - we only have one in this case

marko001 · July 25, 2011

Yes, it was what I needed, this solved me the first (hard part).

But now the progrem is growning:

As I told before, I use ADO objects and .xml file to populate a ListView ( I use this since I need some fields to be editable by user):

Func _PopulateListView()
$counter = _FileCountLines($Mydb)
Local $I, $J, $K, $Txt, $c = 1
   _GUICtrlListView_DeleteAllItems($hlistView)
   If $adoRsItems.EOF() Then
      ;MsgBox(64, "Filter Failed", "Filter Excluded All Items." & @CRLF & "Filter=" & $adoRsItems.Filter & @CRLF & @CRLF & "Reverting to All Items Display.", 5)
      $adoRsItems.Filter = 0
      ;GUICtrlSetState($I, $Button1)
   EndIf
GUICtrlSetData($progress1,0)
   $adoRsItems.MoveFirst()
   $J = $adoRsItems.Fields.Count() - 1
   While NOT $adoRsItems.EOF()
     $K = $adoRsItems.Fields("First Name").Value
     GUICtrlSetData($progress1,0)
        For $I = 0 To $J

         $Txt = $adoRsItems.Fields($I).Value
         If $I = 0 Then
           $K = _GUICtrlListView_AddItem($hlistView, $Txt, $K)
         Else
           _GUICtrlListView_SetItemText($hlistView, $K, $Txt, $I)
         EndIf
         ReDim $sets[$k+1]
         $sets[$k] = $adoRsItems.fields(2).value
     Next; $I
     $c +=1
GUICtrlSetData($progress1,$c/$counter*100)
    $adoRsItems.MoveNext()
  Wend
    GUICtrlSetData($LVNumber,_GUICtrlListView_GetItemCount($hlistView))
EndFunc ; PopulateListView

This function, called everytime I need to show the LV, takes around 90 secs to load for 4000 records.

Since I need to work with 40/50k records, it will be really time consuming.

Case $Mydbopen ; it's a button
            $odbc = 1
            _status(2,"Opening Database...")
            if FileExists(Mydb) Then
                if $call_ado = 0 Then
                    _ADOCalls(Mydb)
                    $call_ado = 1
                EndIf
                _PopulateListView()
                GUISetState(@SW_SHOW,$Mydb_GUI)
                GUICtrlSetData($progress1,0)
            Else
            EndIf

Is there a way to minimize that timing before opening/reopening? Or shall I change method and abandon ADO?

What I need is infact a list where I can click some cells and edit values

Func WM_NOTIFY($hWnd, $iMsg, $iwParam, $ilParam)
if $odbc = 1 Then
    Local $tNMHDR, $hWndFrom, $iCode
    $tNMHDR = DllStructCreate($tagNMHDR, $ilParam)
    $hWndFrom = DllStructGetData($tNMHDR, "hWndFrom")
    $iCode = DllStructGetData($tNMHDR, "Code")
    Switch $hWndFrom
        Case $hListView
            Switch $iCode
                Case $NM_DBLCLK ; used for sub item edit
                    Local $aHit = _GUICtrlListView_SubItemHitTest($hListView)
                    If ($aHit[0] <> -1) Then
                        $Item = $aHit[0]
                        $SubItem = $aHit[1]
                        Local $iSubItemText = _GUICtrlListView_GetItemText($hListView, $Item, $SubItem)
                        Local $iLen = _GUICtrlListView_GetStringWidth($hListView, $iSubItemText)
                        Local $aRect = _GUICtrlListView_GetSubItemRect($hListView, $Item, $SubItem)
                        if $SubItem = "5" OR $SubItem = "7" Then
                            $hEdit = _GUICtrlEdit_Create($Mydb_GUI, $iSubItemText, $aRect[0] + 10, $aRect[1]+96, 50, 17, BitOR($WS_CHILD, $WS_VISIBLE, $ES_AUTOHSCROLL, $ES_LEFT))
                            _GUICtrlEdit_SetSel($hEdit, 0, -1)
                            _WinAPI_SetFocus($hEdit)
                            $hDC = _WinAPI_GetWindowDC($hEdit)
                            $hBrush = _WinAPI_CreateSolidBrush(0)
                            FrameRect($hDC, 0, 0, $iLen + 10, 17, $hBrush)
                        else
                        EndIf
                    EndIf
                Case $LVN_ENDLABELEDITA, $LVN_ENDLABELEDITW ; Used for 1st column edit
                    Local $tInfo = DllStructCreate($tagNMLVDISPINFO, $ilParam)
                    Local $tBuffer = DllStructCreate("char Text[" & DllStructGetData($tInfo, "TextMax") & "]", DllStructGetData($tInfo, "Text"))
                    If StringLen(DllStructGetData($tBuffer, "Text")) Then Return True
                Case $LVN_COLUMNCLICK
                    _GUICtrlListView_SimpleSort($hListView, $B_DESCENDING, 0)
            EndSwitch
    EndSwitch
    Return $GUI_RUNDEFMSG
endfunc

Any Hint?

Marco

Zedna · July 25, 2011

Few optimize hints:

- don't use ReDim inside loop ==> do it once before loop if you know number of items

- dont call _GUICtrlListView_SetItemText() for each column separately ==> set all row by one call with $iSubItem = -1

marko001 · July 25, 2011

I'll try. Do you think it can speed up the LV populating?

Now it's 95 secs for 4500 records.

in case is there a similar solution that could allow me aswell the opportunity to modify single cells?

Thanks

Zedna · July 25, 2011

I'll try. Do you think it can speed up the LV populating?
Now it's 95 secs for 4500 records.
in case is there a similar solution that could allow me aswell the opportunity to modify single cells?
Thanks

Look here

marko001 · July 26, 2011

@ Zedna:

Seems it can load them faster then my solution,

Can you help me in setting my "Func _PopulateListView()" so that I can use your solution? I tried but got so many errors and can't find a way out. (I have 9 columns, 1 item and 8 subitem)

Thanks a lot,

Marco

[Edit]: I use _GUICtrlListView_Create() and not GUICtrlCreateListView()

Edited July 26, 2011 by marko001

marko001 · July 26, 2011

Got it.

Using the standard commands I get 1.64 secs loading time for 4100 records, 8 subitems.

Func _PopulateListView()
    _GUICtrlListView_DeleteAllItems($hlistView)
    If $adoRsItems.EOF() Then
        $adoRsItems.Filter = 0
    EndIf
    $adoRsItems.MoveFirst()
    $J = $adoRsItems.Fields.Count() - 1
    $start = TimerInit()
    While NOT $adoRsItems.EOF()
        $K = $adoRsItems.Fields("First Name").Value
        For $I = 0 To $J
            If $I = 0 Then
                $Txt = $adoRsItems.Fields($I).Value
            Else
                $txt = $txt & "|" & $adoRsItems.Fields($I).Value
            EndIf
        Next
        GUICtrlCreateListViewItem($txt,$listview)
        $adoRsItems.MoveNext()
    Wend
    MsgBox(0,"",'Time: ' & Round(TimerDiff($start)/1000,2))
    GUICtrlSetData($LVNumber,_GUICtrlListView_GetItemCount($hlistView))
EndFunc

If you still can optimize just let me know,

M.

Zedna · July 26, 2011

Your code looks good now.

Just one minor optimization, change:

$txt = $txt & "|" & $adoRsItems.Fields($I).Value

to

$txt &= "|" & $adoRsItems.Fields($I).Value

EDIT: and this line is not used, can be removed

$K = $adoRsItems.Fields("First Name").Value

Edited July 26, 2011 by Zedna

Zedna · July 26, 2011

Also try this modification:

While NOT $adoRsItems.EOF()
       $Txt = ''
        For $I = 0 To $J
            $txt &= $adoRsItems.Fields($I).Value & "|" 
        Next
        GUICtrlCreateListViewItem(StringTrimRight($txt,1), $listview)
        $adoRsItems.MoveNext()
    Wend

marko001 · July 26, 2011

WoW ... 0.61 secs to load, included an

if $sets[UBound($sets)-1] <> $adoRsItems.fields(2).value Then _ArrayAdd($sets,$adoRsItems.fields(2).value)

marko001 · July 26, 2011

Now it's time to save datas If I decide to change some value in the LV (only C4 and C6 editable):

I need to save them :

a ) in the LV (so that I can see when I reload it the changed value)

b ) in the .xml file

The file format is (exlcuding first 38 and last 2 lines (xml header and footer):

...
<z:row c0='Antony Hopkins' Sex='M' Ack='C32' Present='No' c4='0' c5='4' c6='5' c7='12'/>
...

I use these functions:

Func _savedatas($DataFile)
    $temp_number = _GUICtrlListView_GetItemCount($hlistView)
    If Not IsDeclared("iMsgBoxAnswer") Then Local $iMsgBoxAnswer
    if $temp_number > 100 Then
        $iMsgBoxAnswer = MsgBox(308,"Too many records","You have " & _GUICtrlListView_GetItemCount($hlistView) & " records on the list." & @CRLF & "If you save now it will take lot of time." & @CRLF & "Are you sure you filtered correctly before?")
        Select
            Case $iMsgBoxAnswer = 6 ;Yes
                ProgressOn("Saving Data", "Saving Data", "0 %")
                with $adoRsItems
                    .MoveFirst()
                    for $i = 0 to _GUICtrlListView_GetItemCount($hlistView) -1
                        $Number_one = _GUICtrlListView_GetItem($hlistView,$i,4)
                        $Number_two  = _GUICtrlListView_GetItem($hlistView,$i,6)
                        .Fields(4).Value = $Number_one[3]
                        .Fields(6).Value = $Number_two[3]
                        .update
                        .movenext()
                    next
                EndWith
                _GUICtrlListView_SaveXML($hListView, $DataFile)
            Case $iMsgBoxAnswer = 7 ;No
                Sleep(1)
        EndSelect
    Else
        ProgressOn("Saving Data", "Saving Data", "0 %")
        with $adoRsItems
            .MoveFirst()
            for $i = 0 to _GUICtrlListView_GetItemCount($hlistView) -1
                $Number_one = _GUICtrlListView_GetItem($hlistView,$i,4)
                $Number_two  = _GUICtrlListView_GetItem($hlistView,$i,6)
                .Fields(4).Value = $Number_one[3]
                .Fields(6).Value = $Number_two[3]
                .update
                .movenext()
            next
        EndWith
        _GUICtrlListView_SaveXML($hListView, $DataFile)
    EndIf
EndFunc

and

Func _GUICtrlListView_SaveXML($hListView, $sFile)
    Local $iColumnCount = _GUICtrlListView_GetColumnCount($hListView)
    Local $iItemCount = _GUICtrlListView_GetItemCount($hListView)
    local $sXMLBody
    dim $aColumns[8]
    $aColumns[0] ="<z:row c0='"
    $aColumns[1] ="' Sex='"
    $aColumns[2] ="' Ack='"
    $aColumns[3] ="' Present='"
    $aColumns[4] ="' c4='"
    $aColumns[5] ="' c5='"
    $aColumns[6] ="' c6='"
    $aColumns[7] ="' c7='"

    For $A = 0 To $iItemCount - 1 ; ListView Items.
        For $B = 0 To $iColumnCount - 1
            $sItem = _GUICtrlListView_GetItemText($hListView, $A, $B)
            if StringInStr($sItem,"'") Then $sItem = StringReplace($sItem,"'","&#x27;")
            $sXMLBody &= $aColumns[$B] & $sItem
        Next
        $sXMLBody &= "'/>"
        $modstring = StringReplace($sXMLBody, "Æ","&#xC6;",1) ; Exotic Ascii Chars
        $modstring = StringReplace($modstring,"ù","&#xF9;",1) ; Exotic Ascii Chars
        $modstring = StringReplace($modstring,"ú","&#xFA;",1) ; Exotic Ascii Chars
        $modstring = StringReplace($modstring,"û","&#xFB;",1) ; Exotic Ascii Chars
        $foundc5 = StringInStr($modstring,"' c5")
        $newstring = StringLeft($modstring,$foundc5)
        _changeLine($modstring,$newstring,$sFile)
        $sXMLBody = ""
        ProgressSet(round($A/$iItemCount*100,0),Round($A/$iItemCount*100,0) & " %")
    Next
    ProgressSet(100 , "Done", "Complete")
    sleep(1000)
    ProgressOff()

EndFunc   ;==>_GUICtrlListView_SaveXML

and

Func _changeLine($text,$cutstring,$sFile)
    FileOpen($sFile,1)
    $countlines = _FileCountLines($sFile)
    for $i = 39 to $countlines ; first 38 unuseful
        $line = FileReadLine($sFile,$i)
        $foundc4_in_original = StringInStr($line,"' c4")
        $newstring_in_original = StringLeft($line,$foundc4_in_original)
        if StringCompare($newstring_in_original,$cutstring,2) = 0 then
            _FileWriteToLine($sFile,$i, $text,1)
            $i = $countlines
        Else
        EndIf
    Next
    FileClose($sFile)
EndFunc

It's really time consuming.

Maybe it's better to check the common part of the string (<z:row c0='Antony Hopkins' Sex='M' Ack='C32' Present='No' c4=') with a stringcompare? _arrayserach? and then, found the line, subst directly in file .xml with a

stringreplace(stringbetween(LINE,"c4='","' c5='"),$adoRsItems.Fields(4).Value and nesting it in a cycle.

If you think my 3 functions solution is suitable is it possible to optimize it?

Thanks again, mate!

marko001 · July 26, 2011

Solved too..

Func _savedatas($DataFile)
    _FileReadToArray($DataFile,$file)
    $temp_number = _GUICtrlListView_GetItemCount($hlistView)
    with $adoRsItems
        .MoveFirst()
        for $i = 0 to _GUICtrlListView_GetItemCount($hlistView) -1
            $c4= _GUICtrlListView_GetItem($hlistView,$i,4)
            $c6  = _GUICtrlListView_GetItem($hlistView,$i,6)
            .Fields(4).Value = $c4[3]
            .Fields(6).Value = $c6[3]
            .update
            $stringtosearchfor = "<z:row c0='" & _normalize(.Fields(0).Value) & "' Sex='" & .Fields(1).Value & "' Ack='" & .Fields(2).Value & "' Present='" & .Fields(3).Value
            $pos = _ArraySearch($file,$stringtosearchfor,39,UBound($file-1),0,1)
            if @error Then
                MsgBox(0,"error","impossible")                                  
            Else
                $strinbw_1 = _StringBetween($file[$pos],"c4="," c5='")
                $string2replace = StringReplace($file[$pos],$strinbw_1[0],"'" & .Fields(4).Value & "'",1)
                $strinbw_2 = _StringBetween($string2replace,"c6="," c7='")
                $string2replace_2 = StringReplace($string2replace,$strinbw_2[0],"'" & .Fields(6).Value & "'",1)
                _FileWriteToLine($DataFile,$pos,$string2replace_2,1)
            EndIf
            .movenext()
        next
    EndWith
EndFunc

Function _normalize() simply convert Exotic chars into xml-readable ones and returns the converted name

I don't like too much that stringbetween/stringreplace.. but didn't find a better way..

M.

Edited July 26, 2011 by marko001

Zedna · August 1, 2011

Solved too..

- use _GUICtrlListView_GetItemText() instead of _GUICtrlListView_GetItem()

- look here how to optimize Listview functions

- don't use _FileWriteToLine()

- use directly StringRegExp() instead of _StringBetween()

- don't call .Fields(4).Value and .Fields(6).Value inside loop twice - instead use variable in second call

[SOLVED] Fast compare between 2 text files

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members