Comparaison de 2 fichiers Excel

[wis]

Bonjour,

Je viens une nouvelle fois solliciter votre aide concernant un script qui me faciliterait la tache.

J'ai une base client sous excel que je dois comparer avec un fichier blackliste sous excel.
D un coté 12030 lignes et de l autre 25876

Je dois vérifier qu'aucun de mes clients ne figure sur cette liste ou sinon je dois aller vérifier les informations de cette personne.

Code : Tout sélectionner

#include <Excel.au3>
#include <MsgBoxConstants.au3>

$time = @MDAY &"/"& @MON &"/"& @YEAR &" - "& @HOUR &"H"& @MIN &" "& @SEC &" sec"

; Create application object
Local $path = "C:\Users\toto\Documents\blacklist"
Local $oAppl = _Excel_Open()
  FileOpen("log.txt")
If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $sWorkbook = $path & "\client.xls"
Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)


Local $nb_ligne   = $oAppl.ActiveSheet.UsedRange.Rows.Count    ; Compte le nombre de lignes actives

Local $oAppl1 = _Excel_Open()
Local $sWorkbook1 = $path & "\global.xls"
Local $oWorkbook1 = _Excel_BookOpen($oAppl1, $sWorkbook1, Default, Default, True)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook1 & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)



Local $nb_ligne1   = $oAppl1.ActiveSheet.UsedRange.Rows.Count    ; Compte le nombre de lignes actives
 FileWriteLine("log.txt", "Liste des personnes appartenant à la liste et qui sont clients:" & @CRLF )
 
For $i = 2 to $nb_ligne
; Recherche
local $cell = "B" & $i
local $search_keyword = _Excel_RangeRead($oWorkbook, Default, $cell )
$j = 2
While $j <> $nb_ligne1+1
   local $cellule = "B" & $j
Local $cellule1 = _Excel_RangeRead($oWorkbook1, Default, $cellule )
if $search_keyword = $cellule1 Then

   FileWriteLine("log.txt", $search_keyword & " Cellulee n° " &$cell & "et " &  $cellule)
   ExitLoop

EndIf
$j=$j+1
WEnd

Next
FileClose("log.txt")
_Excel_Close($oAppl)
_Excel_Close($oAppl1)
MsgBox(1, "fin de traitement", $time)

Le traitement prends près d'une heure et demi et me renvoie un resultat incohérent par exemple il me sort un nom qui n'appartient pas au 2 eme fichier dans une cellule ou ce nom n 'existe pas.
Y a t il un moyen d'optimiser les appels (310205580 comparaisons)?

J'ai essayé d'exporter en csv pour pouvoir travailler sur un fichier texte mais lorsque je segmente les lignes

Code : Tout sélectionner

 $line = StringSplit($read, @CR)

J'obtiens un

Error: Array variable has incorrect number of subscripts or subscript dimension range exceeded

Avez vous un chemin de solution?


Ps:

global.xls = http://ec.europa.eu/external_relations/ ... global.xml

client.xls :
Code NOM PRN
10001 Toto toto
10001 Titi titi
10003 Tata tata

[mikell]

Personnellement je tirerais 2 arrays 1D des colonnes NOM et je les comparerais pour trouver les noms présents dans les 2 listes

[TomAijerrie]

Si les noms sont triés, une recherche par dichotomie pourrait réduire énormément les calculs à faire.
(on compare le nom à chercher dans l'autre liste à celui du milieu de la liste, et on enlève la moitié qui ne nous intéresse pas, tant que le nom n'est pas trouvé et qu'il reste quelque chose dans la deuxième liste.)

Je regarde de mon coté, mais tu n'as pas un vraifichier .xls à fournir ? J'ai encore un peu de mal avec le xml.

[orax]

J'ai fait quelques essais en faisant la comparaison directement depuis le fichier XML. En comparant une liste de 50 000 noms (prénom inclus), ça met 4 minutes. Mais ce n'est pas la meilleure méthode, c'était juste pour voir quelles seraient les performances. Il serait préférable de construire les tableaux comme l'a suggéré mikell.
La conversion du XML en XLS ou CSV n'est pas nécessaire, on pourrait récupérer les valeurs directement depuis le XML.

Code: [Tout sélectionner] [Déplier/Replier]

$iNum=5000 ; 24 secondes pour 5000

Local $aClients[$iNum][2]
Local $aClients2[$iNum][2]
$t = TimerInit()
For $i = 0 To UBound($aClients) - 1
   $aClients[$i][0] = _randomString(3,30)
   $aClients[$i][1] = _randomString(3,30)
Next
ConsoleWrite(TimerDiff($t) & @CRLF)

Local $oXmlDoc = ObjCreate('Msxml2.DOMDocument.6.0')
$oXmlDoc.load('global.xml')

If $oXmlDoc.parseError.errorCode <> 0 Then
   $oErr = $oXmlDoc.parseError
   ConsoleWriteError($oErr.reason & @CRLF)
   Exit 1
EndIf

$oXmlDoc.setProperty('SelectionLanguage', 'XPath')

; recherche des noms directement dans le fichier (recherche sensible à la casse)
$t = TimerInit()
For $i = 0 To UBound($aClients) - 1
   $oNodes = $oXmlDoc.documentElement.selectNodes(StringFormat('/WHOLE/ENTITY/NAME[LASTNAME = "%s" and FIRSTNAME = "%s"]', $aClients[$i][0], $aClients[$i][1]))
   If $oNodes Then ConsoleWrite($aClients[$i][0] & " " & $aClients[$i][1] & @CRLF)
Next
ConsoleWrite(TimerDiff($t) & @CRLF)


; /////////////////////////////////////


For $i = 0 To UBound($aClients2) - 1
   $aClients2[$i][0] = _randomString(3,30)
   $aClients2[$i][1] = _randomString(3,30)
Next

; recherche des noms dans deux tableaux (Array) — évidemment, c'est beaucoup plus rapide
$t = TimerInit()
For $i = 0 To UBound($aClients) - 1
   If $aClients[$i][0] = $aClients[$i][1] Then ConsoleWrite($aClients[$i][0] & " " & $aClients[$i][1] & @CRLF)
Next
ConsoleWrite(TimerDiff($t) & @CRLF)

Func _randomString($iMin=1,$iMax=100)
   Local $sRandomString = ""

   $iLength = Random($iMin, $iMax, 1)

   $sRandomString = ""
   While StringLen($sRandomString) < $iLength
      $sRandomString &= Chr(Random(97, 122, 1))
   WEnd

   Return $sRandomString
EndFunc   ;==>_randomString

GeSHi © Extension Codebox Plus

[walkson]

Bonsoir,
Je suis étonné que personne n'ait proposé Sqlite. La fonction INTERSECT, sauf erreur, devrait répondre à la question (http://sql.sh/cours/intersect)

Pour le faire avec Excel (si on a du temps à perdre) je verrais bien la solution de regrouper toutes les données sur une même feuille, de faire un trie alphabétique et de se placer en bas des données.
Remonter ligne par ligne et de comparer N avec N - 1. S'ils sont égaux, de rajouter 1 en bout de ligne, sinon passer à la ligne supérieure. Une fois la boucle finie, de filter (filtre avancé) le tableau sur la colonne des 1 pour en sortir une nouvelle table. C'est la méthode la plus rapide à mon avis en dehors de Sqlite ou d'arrays.

@TomAijerrie ouvrir XMl avec Excel...

[wis]

Bonjour,

Merci pour vos réponses je vais essayé et voir ce que cela donne.

Pour walkson, il me semble que INTERSECT nécessite d'avoir le même nombre de ligne.


je vais tenté la solution d'orax en décortiquant les fonctions xml

Merci

[jchd]

Je suis étonné que personne n'ait proposé Sqlite.

Ce n'est pas l'envie qui m'en a manqué !

La fonction INTERSECT, sauf erreur, devrait répondre à la question

Intersect n'est pas la seule solution SQL, enfin si le problème se borne à la question posée.
L'idée est la suivante, où Nom est le critère unique de comparaison (j'utilise ici des prénoms par simplicité, mais il faut un identifiant non-ambigü et unique) :

Code : Tout sélectionner

CREATE TABLE "Liste" ("Nom" CHAR);
CREATE INDEX "ixNom" ON "Liste" ("Nom");

Ensuite on insère massivement les blacklistés :

Code : Tout sélectionner

INSERT INTO liste VALUES (''Anne'), ('Carmen'), ('Jacques'), ('Paul'), ('Pierre'), ('Sylvie');

On peut insérer les clients, qui vont se trouver naturellement en fin de table :

Code : Tout sélectionner

insert into liste values ('Anne'), ('Jacques');

Et voici ce qu'on cherche :

Code : Tout sélectionner

select nom, count(nom) "Clients blacklistés" from liste group by nom having "Clients blacklistés" > 1;

il me semble que INTERSECT nécessite d'avoir le même nombre de ligne.

En aucun cas !

Code : Tout sélectionner

with
    A(x) as (select 1 union all select x+1 from A limit 100),    
    B(x) as (select 5 union all select x+1 from B limit 11),    
    C(x) as (select 9 union all select x+1 from C limit 4),    
    D(x) as (select 31 union all select x+1 from D limit 3)
select x from A intersect select x from B except select x from C union all select x from D

[walkson]

Bonjour,

il faut un identifiant non-ambigü et unique

Je crois que c'est le problème le plus délicat et ce, quelque soit la méthode utilisé.
En regardant la base "Global", on ne peut utiliser que <WHOLENAME>Sab'awi Ibrahim Hassan Al-Tikriti</WHOLENAME> puisqu'il manque souvent le "premier" "dernier" ou "milieu" des noms.
Le problème est que si vous avez d'un coté Sab'awi Ibrahim Hassan Al-Tikriti et de l'autre Sab awi Ibrahim Hassan Al Tikriti la recherche vous donnera 2 individus.
Conclusion, il faut "normaliser" les données pour faire une recherche et une comparaison (supprimer les espaces, points, apostrophes, virgules etc...)

Sans titre.jpg (14.77 Kio) Vu 2511 fois

A titre d'exemple, le tableau Excel où la première et la dernière ligne semble identique mais en réalité, il y a, à la dernière ligne, 2 espaces à ABC à la fin du mot (=SI(A7=B7;"vrai";"faux"))
En espérant que de partager mes soucis du moment vous aide un peu

[orax]

Il faudrait savoir à quoi comparer les noms dans client.xls.
Faut-il les comparer à :
1. <LASTNAME> et <FIRSTNAME>
2. <WHOLENAME>
3. deux comparaison : une comparaison avec <LASTNAME> et <FIRSTNAME>, et une autre avec le <WHOLENAME> complet
4. autre chose... ?

[jchd]

Oui, bien sûr, il faut savoir ce que l'on se propose de traiter et comment conditionner les données pour que le traitement ait un sens. Dans le genre, normaliser les caractères spéciaux, désaccentuer, rendre la casse homogène, etc.
Même avec ces précautions, dans le cas d'identités de personnes (de noms d'entreprises, de lieux, ...), on peut trouver des prénoms plus ou moins tronqués (prénom courant vs état-civil), des changements de prénom ou de nom officiel, des orthographes ou traductions divergentes, des noms maritaux changés (mariage, divorce, remariage), des noms couplés ou découplés (idem), des noms usuels non officiels, des changement de genre et bien d'autres surprises qui peuvent en pratique rendre tout collationnement automatique très délicat, tant les occurences de faux positifs et de faux négatifs sont courantes quand les données proviennent de sources distinctes. L'exemple de la "no fly list" amerloque en est une démonstration douloureuse.

Le code initial de ce post ne mentionnait aucune de ces difficultés.