Moin Zusammen,
hier meine nächste "Auskopplung" aus meinem großen Programm, welches ich evtl. mal vorstelle.
Dieses Teil werde ich aber auch als eigenstädiges Programm weiterführen.
Der Grund für die Auskopplung ist folgender:
Wir arbeiten hier in der Firma mit Adressdaten, die sehr häufig massenweise Adressdubletten enthalten.
Natürlich sind die Adressen nicht 100% identisch (das wär ja auch einfach...).
Leider ist ein Dublettenabgleich auf Sicht nicht sehr zuverlässig und kostet auch noch Zeit.
Natürlich gibt es Programme für sowas. Die Kosten aber Geld und das Teilweise auch nicht zu knapp.
Und dann bin ich an Vorgaben des Herstellers gebunden.
Mein Ziel ist aber, eine Excel-Tabelle/Accessdatei/CSV-Datei einfach auf das Programm schieben und kurze
Zeit später habe ich ein paar Felder dranhängen, in denen die Dubletten markiert sind.
Genug geschwafelt, Zeit was zu zeigen.
Die Hauptfunktion habe ich https://autoit.de/hier%20im%20Forum gepostet.
Das Programm selber
Spoiler anzeigen
dubletten.au3
[autoit]
#include <sql_func.au3>
#include <soundex_koelner_phonetik.au3>
#include <Array.au3>
_SQL_Connect(False) ; False=SQLite starten
; Tabellen erstellen
;Tabelle adressen -------------------------------------------------------------------------------------------------------------------------------------------------
_SQL_Exec ( "CREATE TABLE `adressen` (`filename` VARCHAR(200) NOT NULL DEFAULT '',firstpage BIGINT NOT NULL DEFAULT 0,`refnr` VARCHAR(10) NULL, "& _
"volladresse TEXT DEFAULT '', land VARCHAR(20) NOT NULL DEFAULT '', "& _
"plz VARCHAR(5) NOT NULL DEFAULT '', ort VARCHAR(30) NOT NULL DEFAULT '', strasse VARCHAR(40) NOT NULL DEFAULT '', hausnr VARCHAR(20) NOT NULL DEFAULT '',"& _
"`zeile8` VARCHAR(50) NOT NULL DEFAULT '', `zeile7` VARCHAR(50) NOT NULL DEFAULT '',`zeile6` VARCHAR(50) NOT NULL DEFAULT '',"& _
"`zeile5` VARCHAR(50) NOT NULL DEFAULT '',`zeile4` VARCHAR(50) NOT NULL DEFAULT '', `zeile3` VARCHAR(50) NOT NULL DEFAULT '',"& _
"`zeile2` VARCHAR(50) NOT NULL DEFAULT '',`zeile1` VARCHAR(50) NOT NULL DEFAULT '', adr_gruppe BIGINT UNSIGNED DEFAULT 0, "& _
"pagecount_set BIGINT UNSIGNED DEFAULT 0);")
;Tabelle dubletten -------------------------------------------------------------------------------------------------------------------------------------------------
_SQL_Exec ( "CREATE TABLE dubletten (dbl_rowid BIGINT UNSIGNED, dbl_sdex_all TEXT NOT NULL DEFAULT '', dbl_sdex_ort VARCHAR(20) NOT NULL DEFAULT '', " & _
"dbl_sdex_strasse VARCHAR (25) NOT NULL DEFAULT '', dbl_sdex_fullname TEXT NOT NULL DEFAULT '', dbl_land VARCHAR(20) NOT NULL DEFAULT '', "& _
"dbl_plz VARCHAR(5) NOT NULL DEFAULT '', dbl_stage1 BINGINT UNSIGNED DEFAULT 0, dbl_stage2 BINGINT UNSIGNED DEFAULT 0, "& _
"dbl_stage3 BINGINT UNSIGNED DEFAULT 0);")
;Tabelle dblzeilen -------------------------------------------------------------------------------------------------------------------------------------------------
_SQL_Exec ( "CREATE TABLE dblzeilen (dblz_rowid BIGINT UNSIGNED, dblz_land VARCHAR(20) NOT NULL DEFAULT '', dblz_plz VARCHAR(5) NOT NULL DEFAULT '',"& _
"dblz_sdex_ort VARCHAR(20) NOT NULL DEFAULT '', dblz_sdex_strasse VARCHAR (25) NOT NULL DEFAULT '', dblz_sdex_zeile VARCHAR(30) NOT NULL DEFAULT '', "& _
"dblz_zeile VARCHAR(30) NOT NULL DEFAULT '' );")
Func SplitSqlZeilen () ; Wenn die Adresse nur in Zeile 8 bis 1 steht: Land, PLZ, Ort, Straße und Hausnummer versuchen zu extrahieren
Local $aRowData,$sCurSQL,$q,$n,$aRegEx,$iFoundPos
_SQL_Query("SELECT zeile1,zeile2,zeile3,zeile4,zeile5,zeile6,zeile7,zeile8,ROWID,land,plz,ort,strasse,hausnr,volladresse FROM adressen WHERE plz='' OR ort='' OR strasse='';")
$sCurSQL=""
While _SQL_FetchRow($aRowData)
; In volladresse alle Zeilen schreiben
If $aRowData[14]="" Then $sCurSQL&="UPDATE adressen SET volladresse="&_SQLite_FastEscape(_ArrayToString($aRowData,"",0,7))&" WHERE ROWID="&$aRowData[8]&"; "
$q=7
While Not StringRegExp($aRowData[$q],"\d{4,} ",0)
; Erste Zeile mit einer Zahl mit min. 4 Ziffern gefolgt von einem Leerzeichen suchen
; Die Zeile darunter sollte das ausgeschriebene Land sein
If $aRowData[9]="" Then $sCurSQL&="UPDATE adressen SET land="&_SQLite_FastEscape($aRowData[$q])&" WHERE ROWID="&$aRowData[8]&"; "
$q-=1
If $q<0 Then ExitLoop
WEnd
If $q>0 Then
$aRegEx=StringRegExp($aRowData[$q],"\d{4,} ",1)
If @error=0 Then ; 4stellige Zahl gefunden
$iFoundPos=@extended
If $iFoundPos>StringLen($aRegEx[0])+1 Then ;Nanu, vor der PLZ steht noch was => muss ein Länderkennzeichen sein
If $aRowData[9]="" Then $sCurSQL&="UPDATE adressen SET land="&_SQLite_FastEscape(StringReplace(StringLeft($aRowData[$q],$iFoundPos-1),$aRegEx[0],""))&" WHERE ROWID="&$aRowData[8]&"; "
EndIf
If $aRowData[10]="" Then $sCurSQL&="UPDATE adressen SET plz="&_SQLite_FastEscape($aRegEx[0])&" WHERE ROWID="&$aRowData[8]&"; " ; PLZ schreiben
If $aRowData[11]="" Then $sCurSQL&="UPDATE adressen SET ort="&_SQLite_FastEscape(StringMid($aRowData[$q],$iFoundPos))&" WHERE ROWID="&$aRowData[8]&"; " ; Alles hinter PLZ als ORT schreiben
$q-=1
If $q>=0 Then ; Ist da noch eine Zeile, dann muss das die Straße sein
$aRegEx=StringRegExp($aRowData[$q]," \d{1,}",1) ; Nach einem Leerzeichen gefolgt von min. einer Ziffer suchen => Hausnummer
If @error=0 Then ; Hausnummer gefunden
$iFoundPos=StringInStr($aRowData[$q],$aRegEx[0])
If $aRowData[12]="" Then $sCurSQL&="UPDATE adressen SET strasse="&_SQLite_FastEscape(StringLeft($aRowData[$q],$iFoundPos))&" WHERE ROWID="&$aRowData[8]&"; " ; Straßenamen schreiben
If $aRowData[13]="" Then $sCurSQL&="UPDATE adressen SET hausnr="&_SQLite_FastEscape(StringMid($aRowData[$q],$iFoundPos))&" WHERE ROWID="&$aRowData[8]&"; " ; Hausnummer schreiben
Else ; Keine Hausnummer => ganze Zeile als Straße verwenden
If $aRowData[12]="" Then $sCurSQL&="UPDATE adressen SET strasse="&_SQLite_FastEscape($aRowData[$q])&" WHERE ROWID="&$aRowData[8]&"; " ; Straßenamen schreiben
EndIf
EndIf
EndIf
;Alle Zeilen, die verarbeitet wurden, leeren; Wenn keine 4Ziffern mit Leerzeichen gefunden wurden ist es eh keine richtige Adresse
For $n=7 To $q Step -1
$sCurSQL&="UPDATE adressen SET zeile"&$n+1&"=''; " ; Zeile leeren
Next
EndIf
WEnd
_SQL_Exec($sCurSQL)
EndFunc ; ==> SplitSqlZeilen
Func Gen_SoundEx_From_SqlTab ()
Local $aRowData,$sCurSQL,$q,$sStr,$sPLZ,$sOrt,$sLand,$n,$sSndEx
Local Const $sLoeschworte="herrn;herr;frau;firma;familie; kg;co.kg;ggmbh;gmbh; mbh;co.; ohg;z.hd.;c/o;e.v.;gbr;a.g.; ag;e.g.; eg"
Local $aRplToken=StringSplit($sLoeschworte,";")
_SQL_Query("SELECT ROWID,zeile1,zeile2,zeile3,zeile4,zeile5,zeile6,zeile7,zeile8,ort,strasse,land,plz,hausnr FROM adressen;")
While _SQL_FetchRow($aRowData)
;Globalen SoundEx für Tabelle:dubletten
$sStr=_soundex_koelner($aRowData[10])
$sPLZ=_SQLite_FastEscape($aRowData[12])
$sOrt=_soundex_koelner($aRowData[9])
$sLand=_SQLite_FastEscape($aRowData[11])
$sCurSQL= "INSERT INTO dubletten (dbl_rowid,dbl_land,dbl_plz,dbl_sdex_ort,dbl_sdex_strasse,dbl_sdex_fullname,dbl_sdex_all) VALUES (" & _
$aRowData[0]&","&$sLand&","&$sPLZ&",'"&$sOrt&"','"&$sStr&"','"&_soundex_koelner(_ArrayToString($aRowData,"",1,8))&"','"& _
_soundex_koelner(_ArrayToString($aRowData,"",1,10))&"'); "
;SoundEx jede Zeile der Adresse für Tabelle:dblzeilen
For $q=1 To 8
If StringLen($aRowData[$q])>"" Then
$aRowData[$q]=StringReplace($aRowData[$q],". ",".")
$aRowData[$q]=StringReplace($aRowData[$q],", ",",")
For $n=1 To $aRplToken[0]
$aRowData[$q]=StringReplace($aRowData[$q],$aRplToken[$n],"")
Next
$aRowData[$q]=StringStripWS($aRowData[$q],7)
$sSndEx=_soundex_koelner($aRowData[$q])
If StringLen($sSndEx)>0 Then $sCurSQL&="INSERT INTO dblzeilen VALUES ("&$aRowData[0]&","&$sLand&","&$sPLZ&",'"&$sOrt&"','"&$sStr&"','"&$sSndEx&"',"& _
_SQLite_FastEscape($aRowData[$q])&"); "
EndIf
Next
_SQL_Exec($sCurSQL)
WEnd
EndFunc ; ==> Gen_SoundEx_From_SqlTab
Func Mark_Dbl_Grps ($iUseStage=1,$fWriteDblOrder=True)
Local $aRowData,$sql_query
; 1. Stufe : Die Doublettengruppen eintragen, die 100% gleich ("volladresse") sind
_SQL_Exec("CREATE TABLE dlbgrps AS SELECT volladresse,count(*) as anz FROM adressen GROUP BY volladresse HAVING anz>1;")
_SQL_Exec("UPDATE dubletten SET dbl_stage1=(SELECT dlbgrps.ROWID FROM dlbgrps INNER JOIN adressen WHERE adressen.volladresse=dlbgrps.volladresse AND dubletten.dbl_rowid=adressen.ROWID);")
_SQL_Exec("DROP TABLE dlbgrps;")
; 2. Stufe : Die Doublettengruppen eintragen, bei denen Land+PLZ+SoundEx-VolleAdresse gleich ist
_SQL_Exec("CREATE TABLE dlbgrps AS SELECT (dbl_land+dbl_plz+dbl_sdex_fullname) AS token,count(*) AS anz FROM dubletten GROUP BY dbl_land+dbl_plz+dbl_sdex_fullname HAVING anz>1;")
_SQL_Exec("UPDATE dubletten SET dbl_stage2=(SELECT dlbgrps.ROWID FROM dlbgrps WHERE dubletten.dbl_land+dubletten.dbl_plz+dubletten.dbl_sdex_fullname=dlbgrps.token);")
_SQL_Exec("DROP TABLE dlbgrps;")
; 3. Stufe : Die Doublettengruppen eintragen, bei denen das LAND+PLZ+SoundEx(Strasse+zeile8-1 in einer anderen Zeile) gefunden wird
_SQL_Exec( "CREATE TABLE dlbgrps AS SELECT (''+dblz_land+dblz_plz+dblz_sdex_strasse+dblz_sdex_zeile) AS token, count(*) as anz, dblz_zeile AS zeile FROM dblzeilen "& _
"GROUP BY dblz_land+dblz_plz+dblz_sdex_strasse+dblz_sdex_zeile HAVING anz>1;")
_SQL_Query("SELECT dlbgrps.ROWID,dblzeilen.dblz_rowid FROM dlbgrps INNER JOIN dblzeilen "& _
"WHERE (''+dblzeilen.dblz_land+dblzeilen.dblz_plz+dblzeilen.dblz_sdex_strasse+dblzeilen.dblz_sdex_zeile)=dlbgrps.token ORDER BY dlbgrps.anz DESC;")
While _SQL_FetchRow($aRowData)
_SQL_Exec("UPDATE dubletten SET dbl_stage3="&$aRowData[0]&" WHERE dubletten.dbl_stage3=0 AND dubletten.ROWID="&$aRowData[1]&";")
WEnd
_SQL_Exec("DROP TABLE dlbgrps;")
; Jetzt die gewünschte Suchtiefe in adressen eintragen
If $iUseStage>0 And $iUseStage<4 Then
_SQL_Exec("UPDATE adressen SET adr_gruppe=(SELECT dbl_stage"&$iUseStage&" FROM dubletten WHERE dubletten.dbl_rowid=adressen.ROWID);")
if $fWriteDblOrder Then _SQL_Exec("UPDATE adressen SET pagecount_set=(SELECT ROWID FROM dubletten WHERE dubletten.dbl_rowid=adressen.ROWID);")
_SQL_Exec("ALTER TABLE adressen RENAME TO tmp_adressen;")
_SQL_Exec("CREATE TABLE adressen AS SELECT * FROM tmp_adressen ORDER BY adr_gruppe DESC,pagecount_set;")
_SQL_Exec("DROP TABLE tmp_adressen;")
EndIf
EndFunc ; ==> Mark_Dbl_Grps
Und dazu ein paar "Hilfroutinen"
Spoiler anzeigen
sql_func.au3
[autoit]
#include-once
#include <SQLite.au3>
#include <SQLite.dll.au3>
Global $sql_hConnect
Global $sql_hQuery
Func _SQL_Connect ($fMysql=False)
If Not $fMysql Then ; Kein MySQL => SQLite starten
$sql_hConnect=_SQLite_Startup(@ScriptDir&$_sqlite_dll)
If @error Then
MsgBox(16, "SQLite Error", $_sqlite_dll&" konnte nicht geladen werden!")
Exit
EndIf
If _SQLite_LibVersion()<>"3.7.10" Then
MsgBox(16,"SQLite Error", $_sqlite_dll&" wurde ausgetauscht!"&@CRLF&"Dieses Programm wurde für SQLite Version 3.7.10 programmiert.")
Exit
EndIf
_SQLite_Open() ; :memory:-DB erstellen
Else ; MySQL-Datenbank öffnen
Return False
EndIf
EndFunc ; ==> _SQL_Connect
Func _SQL_Exec ($sQuery)
If StringInStr($sql_hConnect,$_sqlite_dll)>0 Then ; Es wurde SQLite-Datenbank gestartet
If Not _SQLite_Exec(-1,$sQuery)=$SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg())
Else ; Es wurde eine MySQL-Datenbank gestartet
Return False
EndIf
EndFunc ; ==> _SQL_Exec
Func _SQL_Query ($sQuery,$hQuery="")
If StringInStr($sql_hConnect,$_sqlite_dll)>0 Then ; Es wurde SQLite-Datenbank gestartet
If Not _SQLite_Query(-1,$sQuery,$hQuery)=$SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg())
If @NumParams=1 Then
; Es wurde keine Queryhandle übergeben => Globalen Queryhandle verwenden
$sql_hQuery=$hQuery
Else
Return $hQuery
EndIf
Else ; Es wurde eine MySQL-Datenbank gestartet
Return False
EndIf
EndFunc ; ==> _SQL_Exec
Func _SQL_FetchRow (ByRef $aRowdata,$hQuery="")
If StringInStr($sql_hConnect,$_sqlite_dll)>0 Then ; Es wurde SQLite-Datenbank gestartet
If @NumParams=1 Then $hQuery=$sql_hQuery
If Not _SQLite_FetchData($hQuery,$aRowdata,False,False)=$SQLITE_OK Then
; Bei einem Fehler ist das Ende erreich => QueryFinalize und False zurück, da keine weiteren Daten kommen
_SQLite_QueryFinalize($hQuery)
Return False
EndIf
Else ; Es wurde eine MySQL-Datenbank gestartet
Return False
EndIf
Return True
EndFunc ; ==> _SQL_FetchRow
Func _SQL_FetchSingleRow ($sQuery,ByRef $aRowdata)
Local $hQuery_hdl,$fResult
If StringInStr($sql_hConnect,$_sqlite_dll)>0 Then ; Es wurde SQLite-Datenbank gestartet
$hQuery_hdl=_SQL_Query($sQuery,$hQuery_hdl)
$fResult=_SQL_FetchRow($aRowdata,$hQuery_hdl)
_SQLite_QueryFinalize($hQuery_hdl)
Return $fResult
Else ; Es wurde eine MySQL-Datenbank gestartet
Return False
EndIf
EndFunc ; ==> _SQL_FetchSingleRow
Func _SQL_FetchSingleField ($sQuery)
Local $aResult
If _SQL_FetchSingleRow($sQuery,$aResult) Then Return $aResult[0]
Return ""
EndFunc ; ==>_SQL_FetchSingleField
Ich hör die Frage schon: "Wozu denn dieses Doppeltgemoppelte?"
SQLite ist leider nicht Serverfähig. Wenn ich jetzt aber mit mehreren Leuten einen Dublettenabgleich zugleich(!)
bearbeite (s.u.), muss ich irgendwo einen Datenserver haben. Da MySQL frei ist, werde ich auch eine MySQL-Schnittstelle einbauen.
ToDo:
- eine GUI für das Ganze
- Import von min. Excel, Access & CSV
- Export in selbige Formate
- noch tiefere Unschäfestufen, die dann sogar ...
- manueller Abgleich der zu unscharfen Dubletten und das ...
- von mehreren Arbeitsplätzen aus (MySQL)
Da das "Programm" bis dato nur aus einem Skelett besteht,
werd den Beitrag wohl noch öfters editieren müssen 
Aber trotzdem sind mir Verbessungen/Kritik und viel wichtiger Vorschläge und Anregungen sehr willkommen!