Einlesen von riesigen Excel Tabellen

Schönen guten Tag zusammen,

(Wer sich nicht alles durchlesen möchte kann auch direkt zu den eigentlichen Fragen übergehen)

bevor ich zu meiner Frage komme erstmal kurz etwas über mich:
Ich bin momentan 19 Jahre alt und im 2. Ausbildungsjahr als Fachinformatiker für Systemintegration.
Ich arbeite in einem Mittelständischen IT-Dienstleister Betrieb und habe mit vielen Kunden aus verschiedenen Branchen zu tuen.

Wie es zu meiner Frage/meinem Problem gekommen ist:
Ich wurde heute bei einem normalem Kundentermin angesprochen und gefragt, ob wir als Unternehmen auch kundenspezifische Programme erstellen.
Für gewöhnlich gehört sowas nicht zu unserem Aufgabenbereich aber da ich schon einige Abläufe mit AutoIt automatisiert und nützliche Tools erstellt habe,
habe ich mir eine kurze Beschreibung/Problemschilderung des Kunden zeigen lassen.

Zur Problembeschreibung:
Der beschriebene Kunde ist ein Großhändler und arbeitet nun seit einigen Wochen mit Lexware (Warenwirtschaftssystem).
Er bekommt jede Wochen von verschiedenen Lieferanten aktuelle Preis- und Bestandslisten als Excel Datei.
Darin aufgeführt sind Produkte des Lieferanten unterteilt in verschiedene Kategorien wie z.B. Artikelnummer, Produktname, Brutto- und Netto Preis.
Diese Listen kann man so in Lexware importieren, allerdings werden dabei vorhandene Werte aus von ihm selbst erstellten Kategorien (z.B. eigener Lagerbestand) einfach gelöscht.
Dass lässt sich verhindern, indem er einen Export seiner Artikel anfertigt und dann die Artikelnummer aus den von den Lieferanten zugesendeten Dateien vergleicht
mit denen, die in seinem Export sind. Wird eine Übereinstimmung gefunden, dann werden die restlichen Kategorien in seinem Export mit den aktuellen Daten überschrieben.
Wenn keine Übereinstimmung gefunden wurde, wird eine neue Zeile angelegt.
Anschließend kann er seine aktualisierte, exportierte Datei ohne Probleme wieder importieren.

Meine Ideen zur Realisierung:
Ich habe gedanklich schon einen ungefähren Plan entwickelt, wie ich das ganze realisieren könnte.
Die Spalte mit den Artikelnummern aus der Export Datei wird in einem Array eingelesen.
Anschließend wird Artikel für Artikel aus dem Dokument des Lieferanten mit dem Array verglichen und bei einer Übereinstimmung werden die Daten in die Exportdatei übertragen.
Wird keine Übereinstimmung gefunden, dann wird wie weiter oben beschrieben eine neue Zeile mit dem Artikel angelegt.

Wie wurde diese Aufgabe bisher gelöst:
Das Übertragen der Daten wurde bislang immer von einem Azubi der Kaufmännischen Abteilung per Hand durchgeführt.
Dieser Vorgang dauert jede Woche zwischen 2-5 Stunden (Je nach Menge der einzupflegenden Artikel).
Allerdings ist das sehr kostenintensiv und auch nicht das gewünschte Ziel der Ausbildung (Aussage des Kunden)
Außerdem das allgemein bekannte Problem "Wo Menschen sind, da sind auch Fehler"

Was muss beachtet/nicht beachtet werden:
- Die Zeit die das Programm benötigt spielt keine Rolle
- Extreme Benutzerfreundlichkeit muss gegeben sein (Der Kunde seine Mitarbeiter sind die absoluten ober DAUs)

Nun zu den eigentlichen Fragen:
In dem Export befinden sich rund 50.000 Artikel und jede Woche werden zwischen 400-1000 Artikel aktualisiert.
- Ist es überhaupt möglich, dass alle Artikel in einem Array gespeichert werden?
- Hat jemand Erfahrung damit, so große Datenmengen miteinander vergleichen zu lassen?
- Falls ja, wenn ich mal hochrechne (Artikelnummer hat 8 Stellen, Pro Zahl = 1Byte, 8Byte x 50.000 Artikel = 400.000Byte) komme ich auf ~400KB an benötigten Arbeitsspeicher.
Das erscheint mir allerdings etwas wenig. Hat eventuell jemand durch bereits realisierte Projekte genauere Werte sammeln können?

Worum bitte ich euch:
Ich bitte euch recht herzlich darum, meine gestellten Fragen zu beantworten.
Die vollständige Ausarbeitung und Realisierung des Programms werde ich vermutlich selbst auf die Reihe bekommen.
Sollte ich Logik Fehler gemacht haben oder wenn ihr nützliche Tipps habt bin ich für alles offen und auch herzlich dankbar.

Damit das ganze hier jetzt auch mal ein Ende findet bedanke ich mich recht herzlich bei allen, die sich die Mühe gemacht haben meinen Roman durchzulesen
und ich bedanke mich auch für jeden nützlichen Kommentar, sowie konstruktive Kritik.

Liebe Grüße,
xGreenryder

Vielen Dank erstmal für eure Antworten.

Direkt an SQL traue ich mich nicht ran. Falls mal was schief geht, zerschießt es mir die ganze Datenbank. Das Programm soll ja letzten Endes vom Kunden selbst ausgeführt werden.
Außerdem hab ich mich selbst noch nie mit SQL befasst, was auch wieder einiges an Zeit kosten würde.

Ich werde übers Wochenende hinweg mal einen ersten Prototypen erstellen und einfach mal schauen ob es geht.

Tipps und Ratschläge sind natürlich weiterhin willkommen

Gruß
xGreenryder

Schönen guten Tag miteinander,

hier ist mal mein versprochenes Update:

1. Ich habe zum testen die aktuellen Exports von dem Kunden bekommen. Die 50.000 Artikel die ich bei ihm in der Lexware gesehen hatte bezogen sich nur auf eine Produktgruppe.
Die Exportdatei besteht aus 2 Tabellen. Tabelle 1: 41,3MB und hat 704989 Artikel. Die 2. Tabelle ist 30,4MB groß und hat nochmals 511314 Artikel.

2. Die ersten Tests wurden durchgeführt. Zum kompletten einlesen der Spalte mit den Artikelnummern aus der 1. Tabelle benötigt mein kleines Test-Script 97 Minuten und belegt 133MB im RAM
Einen eingegebenen Wert mit dem Array zu vergleichen dauert hingegen nur knappe 6 Sekunden.

3. Sobald ich auch noch die Datei der Lieferanten bekomme welche die aktualisierten Artikel enthält kann ich das Script fertig stellen und auf mögliche Bugs prüfen.

4. Falls es jemanden interessiert, ich habe _ExcelReadSheetToArray um das Array einzulesen.

Weitere Updates werden folgen

Gruß
xGreenryder

Zitat von alpines

Du hast doch sicherlich genug Speicherplatz um die Datenbank zu backuppen bevor du ihr ne Liste an Queries an den Kopf schmeißt?

Selbstverständlich werden Backups durchgeführt. 2x am Tag wird eine Vollsicherung des Servers durchgeführt und alle 30 Minuten Inkrementell. Aber wie ich oben erwähnt habe, der Kunde soll den kompletten Import selbst durchführen können. Wenn er beim Copy+Paste eine Zeile übersieht o.ä. kann das unter Umständen die Datenbank zerstören. Das bedeutet, dass das Unternehmen einen Arbeitsausfall hat für die Zeit der Wiederherstellung des Backups.
Es ist klar, dass das Aktualisieren der Datenbestände durch ein SQL Script sehr viel schneller wäre aber einen Arbeitsausfall möchte ich dennoch nicht verantworten.

Zitat von BugFix

Das wäre auch sehr leichtsinnig, mache ich auch nie bei einem noch nie gelaufenen Programm. Ich habe in einer VM eine Kopie der Warenwirtschaft laufen und teste selbstverständlich neue Dinge dort, bevor es an das Original geht.

Klar, das fertige Programm wird vorher ausgiebig getestet um mögliche Bugs auszuschließen. Außerdem finde ich die Idee den Import per SQL zu machen ja recht gut, solange es im Privaten bereich bleibt. Sobald man allerdings an gewerbliche Tätigkeiten heran geht hat man sehr schnell eine Schadensersatzklage am Hals, sollte da etwas schief gehen.

Zitat von alpines

Der langsamere Weg über Excel z.B. ist in keiner Weise schlecht - das mag niemand bezweifeln - aber wie kommst du denn auf ganze 97 Minuten? Das hört sich ein bisschen nach zu viel an.

Ich kann dir leider auch nicht sagen, warum das 97 Minuten dauert. Ich habe die Zeit mittels TimeInit+TimeDiff ausgeben lassen. Für 1000 Zeilen benötigt das Script etwa 10 Sekunden
und wenn man das dann auf die ~700.000 Zeilen hochrechnet kommt das in etwa hin.
Gemessen wurde auch nur das wirkliche einlesen ins Array also Dokument aufrufen, etc. ist da noch gar nicht bei.
Die Zeiten hab ich dem Kunden auch schon mitgeteilt und er sieht darin kein Problem, da das Script von Samstag Nachmittag bis Montag Morgen dafür Zeit hat.

Zitat von alpines

Kannst du grob zusammenfassen was er in diesen 10 Sekunden für die 1000 Zeilen macht? Das kommt mir dennoch irgendwie viel vor.

Es wird nur die Spalte mit den Artikelnummern aus der Export Datei in ein Array gespeichert.

#include <Excel.au3>
#include <Array.au3>


$File = @DesktopDir & "\test.xlsx"
$oExcel = _ExcelBookOpen($File)


$hTimer = TimerInit()


$aArray = _ExcelReadSheetToArray($oExcel, 2, 1, 0, 1) ;Fängt in der 1. Spalte und in der 2. Zeile an


$fDiff = TimerDiff($hTimer)
$sec = $fDiff / 1000
$min = $sec /60
MsgBox(0, "Benötigte Zeit", "Sekunden: " & $sec & @CRLF & "Minuten: " & $min)


_ExcelBookClose($oExcel)

Zitat von alpines

Welche AutoIt-Version verwendest du?

Version 3.6.6 May 31 2016

Zitat von alpines

Ich finde die _ExcelReadSheetToArray in meiner Excel.au3 nicht.

Ich habe nach Möglichkeiten Gegoogelt um eine ganze Spalte in ein Array einzulesen. Bin dabei auf der Autoit Seite gelandet und in den Tutorials dort waren andere Befehle als in meiner Excel.au3. Nach kurzer Suche bin ich dann auf eine andere Excel.au3 gestoßen, welche die Befehle aus den Tutorials hat. Siehe Anhang.

Zitat von Espyre

Du solltest unbedingt updaten.

In der aktuellen Version der Excel-UDF gibt es die Funktion _Excel_RangeRead.
Diese ist um ein vielfaches schneller als _ExcelReadSheetToArray.

Ok, ich habe mein kleines Test-Script mal umgeschrieben mit der aktuellen Excel.au3:

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


$File = @ScriptDir & "\test.xlsx" ; Pfad zur Excel Datei
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oExcel, $File) ; Exceldokument öffnen


$sFilePath = @ScriptDir & "\test.txt" ; Pfad der KJntrolldatei


$hTimer = TimerInit() ; Timer Start für Zeitmessung


$aArray = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A")) ; Spale A wird ausgelesen


;---------Zeitmussung mit Rückmeldung---------
$fDiff = TimerDiff($hTimer)
$sec = $fDiff / 1000
$min = $sec /60
MsgBox(0, "Benötigte Zeit", "Sekunden: " & $sec & @CRLF & "Minuten: " & $min)
;---------------------------------------------


_FileWriteFromArray($sFilePath, $aArray) ; Erstellen der Kontrolldatei


_Excel_BookSaveAs($oWorkbook, @ScriptDir & "\TestNew.xlsx", "xlsx", 0, 1) ; Speichern der Excel Datei
_Excel_BookClose($oWorkbook) ; Excel schließen

Einlesen von der 1. Excel Tabelle mit ~700.000 Zeilen dauert genau 1,86 Sekunden!!!!!!

Vielen Dank für deinen Beitrag und auch an alpines für den Hinweis mit den veralteten Funktionen.