Excel-Dateien in mySQL importieren

[Alf]

Hi,

ich habe folgendes Problem: ich muss eine mySQL verwenden.

In diese mySQL werden täglich zeitgesteuert Exceldateien importiert, die von verschiedenen Usern auf den Server gespeichert werden. Für den Import verwenden wir "Navicat". Das Tool funktioniert, solange es kleine Tabellen (bis ca. 3000 Zeilen) sind. Es sind aber auch Tabellen mit 15.000 Zeilen und mehr dabei. Dann kriegt man Probleme mit Datums und-Zeitangaben und der Import bleibt irgendwann stehen.

Die Dateien als .csv speichern und dann in die DB laden geht nicht, da die mySQL nur ein einziges Datums-/Zeitformat lesen kann und wir nicht alle Maschinen, mit denen die Exceldateien erstellt werden, entsprechend anpassen können. Die Exceldateien in mehrere kleine Tabellen zu zerlegen ist ebenfalls keine Lösung.

Wenn man im Google sucht, findet man ein paar Tools, die den Import angeblich hinkriegen. Die Realität ist dann leider anders. Kennt jemand ein Tool, das nachweislich auch größere Exceldateien (trotz Datums- und Zeitinformationen) in eine mySQL importieren kann?

Bin für jeden Tip dankbar.
Alf

[fastix®]

Moin!

ich habe folgendes Problem: ich muss eine mySQL verwenden.

Nein! Das Problem ist vielmehr: Du hast vorher etwas anderes, ungeeignetes, verwendet (Excel).

Das Tool funktioniert, solange es kleine Tabellen (bis ca. 3000 Zeilen) sind. Es sind aber auch Tabellen mit 15.000 Zeilen und mehr dabei. Dann kriegt man Probleme mit Datums und-Zeitangaben und der Import bleibt irgendwann stehen.

Hm. Was bitte soll eine Software bei der 3001 oder 15.000en Zeile anders machen? Das Problem wird vielmehr sein, dass in den Feldern mit Fünfach höherer Wahrscheinlichkeit kein Datum steht. Alternativ ist die Software mit so großen Tabellen nicht ausreichend getestet und hat ein Problem, z.B. in der Speicherverwaltung. Ich kenne die Software nicht....

Die Dateien als .csv speichern und dann in die DB laden geht nicht, da die mySQL nur ein einziges Datums-/Zeitformat lesen kann

So ein Quatsch! Natürlich geht das, es ist der Königsweg. Schreibe ein VBA- Script, welches das Datumsformat zuverlässig ändert und bei illegalen (falschen) Datums- oder Zeitangaben diese reklamiert.

und wir nicht alle Maschinen, mit denen die Exceldateien erstellt werden, entsprechend anpassen können.

Doch! Die Vorlage fertig machen und jedem User zur Verfügung stellen.

Die Exceldateien in mehrere kleine Tabellen zu zerlegen ist ebenfalls keine Lösung.
Wieso?

Ich sagte schon: ich vermute falsche/illegale Datumsangaben als Grund. Aber wissen kann ich das gegenwärtig nicht.

Die Realität ist dann leider anders.

Die Realität ist, dass Microsoft sich über die Internas seiner Dateiformate ausschweigt, damit kein Mitbewerber einfach mal so eine Software schreiben kann. Beschwere Dich also in Redmond, USA. Und grüße gleich Deinen Abgeordneten im Europaparlament, damit die Interoperabilitätsklausel nicht aus der Patentvorlage verschwindet, so wie es der Europarat unter Umgehung des Parlamentes und der Regeln der Demokratie vorhat.

Kennt jemand ein Tool, das nachweislich auch größere Exceldateien (trotz Datums- und Zeitinformationen) in eine mySQL importieren kann?

Die Realität ist noch anders: Excel ist _grundsätzlich_ nicht geeignet um mit derart großen Datenmengen umzugehen. Es muss über das gesamte Konzept neu nachgedacht werden.
15000 Zeile und Excel: Das ist sowieso an der Grenze des noch machbaren: Bald ist sowieso Schluß mit dieser "Lösung"! Wenn dieser Pfusch beibehalten wird gibt es irgendwann ein sehr teures Aufwachen.

Bin für jeden Tip dankbar.

Königsweg bei Beibehaltung des Konzeptes ist der VBA- gesteuerte Export von CSV unter Umwandlung und Prüfung des Datumsformates.

-> -> -> Der _bessere_ Königsweg mit einer geissen Zukunft ist aber konsequent zu sein und die Datenverarbeitung von Anfang an mit der Datenbank (also MySQL) zu machen. Das geht nicht? Doch das geht. Wunderprima sogar. Und um bunte Diagramme zu zaubern kann man die verarbeiteten Daten in Excel einlesen. Via ODBC zum Bleistift. Excel ist ein Tabellenkalkulationsprogramm nicht mehr und nicht weniger. Dafür ist es recht gut geeignet....

Eventuell wird jemand benötigt, der sich mit sowas auskennt.

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix®

[Alf]

Hi fastix,

Hm. Was bitte soll eine Software bei der 3001 oder 15.000en Zeile anders machen? Das Problem wird vielmehr sein, dass in den Feldern mit Fünfach höherer Wahrscheinlichkeit kein Datum steht. Alternativ ist die Software mit so großen Tabellen nicht ausreichend getestet und hat ein Problem, z.B. in der Speicherverwaltung. Ich kenne die Software nicht....

Keine Ahnung, was die Software beim 3001. bzw 15000. Datensatz anders macht. Die Daten sind definitv korrekt, also wird's wohl die Speicherverwaltung sein, ist mir aber auch absolut egal. Bin seit über einem Monat im Dialog mit deren Entwicklern. Die wissen´s auch nicht aber suchen nach 'ner Lösung...

Die Dateien als .csv speichern und dann in die DB laden geht nicht, da die mySQL nur ein einziges Datums-/Zeitformat lesen kann
So ein Quatsch! Natürlich geht das, es ist der Königsweg. Schreibe ein VBA- Script, welches das Datumsformat zuverlässig ändert und bei illegalen (falschen) Datums- oder Zeitangaben diese reklamiert.

Für jedes seit Jahren bekannte und vor Jahren gelöste Standardproblemchen das Rad neu zu erfinden _kann_ keine Lösung sein. Trotzdem danke für den Tip. Wird wohl darauf hinauslaufen.

und wir nicht alle Maschinen, mit denen die Exceldateien erstellt werden, entsprechend anpassen können.
Doch! Die Vorlage fertig machen und jedem User zur Verfügung stellen.

Wenn wir vorher wüssten, wer die Daten erstellt, könnten wir das in der Tat machen...

Die Exceldateien in mehrere kleine Tabellen zu zerlegen ist ebenfalls keine Lösung.
Wieso?

Ende der 80er war es noch unvermeidbar, Systeme umständlich und kompliziert zu gestalten. Da hat das Basteln auch noch Spaß gemacht. Aber früher war eh alles besser ;-). Inzwischen ist die Technik weiter. Warum also zurück zur Steinzeit?

Ich sagte schon: ich vermute falsche/illegale Datumsangaben als Grund. Aber wissen kann ich das gegenwärtig nicht.

Die Daten sind definitiv richtig. Das System läuft bei anderen Kunden auf _richtigen_ Datenbanken (Views, Stored Procedures, Trigger, SQL-Statements etc.) seit über zwei Jahren problemlos.

Die Realität ist dann leider anders.
Die Realität ist, dass Microsoft sich über die Internas seiner Dateiformate ausschweigt, damit kein Mitbewerber einfach mal so eine Software schreiben kann. Beschwere Dich also in Redmond, USA. Und grüße gleich Deinen Abgeordneten im Europaparlament, damit die Interoperabilitätsklausel nicht aus der Patentvorlage verschwindet, so wie es der Europarat unter Umgehung des Parlamentes und der Regeln der Demokratie vorhat.

Kennt jemand ein Tool, das nachweislich auch größere Exceldateien (trotz Datums- und Zeitinformationen) in eine mySQL importieren kann?
Die Realität ist noch anders: Excel ist _grundsätzlich_ nicht geeignet um mit derart großen Datenmengen umzugehen. Es muss über das gesamte Konzept neu nachgedacht werden.
15000 Zeile und Excel: Das ist sowieso an der Grenze des noch machbaren: Bald ist sowieso Schluß mit dieser "Lösung"! Wenn dieser Pfusch beibehalten wird gibt es irgendwann ein sehr teures Aufwachen.

Wie gesagt. Mit _richtigen_ Datenbanken läuft´s seit Jahren problemlos. Aber ich lass Dir gerne Deine Meinung.

Bin für jeden Tip dankbar.
Königsweg bei Beibehaltung des Konzeptes ist der VBA- gesteuerte Export von CSV unter Umwandlung und Prüfung des Datumsformates.

-> -> -> Der _bessere_ Königsweg mit einer geissen Zukunft ist aber konsequent zu sein und die Datenverarbeitung von Anfang an mit der Datenbank (also MySQL)

was jetzt? Datenbank oder mySQL? :-)

zu machen. Das geht nicht? Doch das geht. Wunderprima sogar.

Natürlich geht das. Wunderbar sogar. Drum machen wir das ja auch!

Und um bunte Diagramme zu zaubern kann man die verarbeiteten Daten in Excel einlesen. Via ODBC zum Bleistift. Excel ist ein Tabellenkalkulationsprogramm nicht mehr und nicht weniger. Dafür ist es recht gut geeignet....

Und genau deswegen wird es für die Erfassung der Daten verwendet. Die Daten dann später wieder in´s Excel exportieren ist aber auch wieder viel zu umständlich. Solltest Du nochmal überdenken. Wir erzeugen die "bunten Diagramme" und Listen lieber ohne Excel direkt aus der Datenbank ;-). Ich hoffe, es freut Dich, das zu hören.

Eventuell wird jemand benötigt, der sich mit sowas auskennt.

Klar. Dieser "jemand" muss es nur schaffen, einer Konzernleitung zu verkaufen, dass sie für teures Geld und mit viel Zeitaufwand ein Datenerfassungssystem programmieren soll, obwohl Excel alles das, was man braucht, bereits seit Jahren kann. Dann müsste er die Konzernmitarbeiter davon überzeugen, dass sie für teures Geld und mit viel Zeitaufwand Schulungen für ein Datenerfassungssystem besuchen müssen, obwohl sie die Daten auch bequem mit Excel erfassen können, mit dem sie schon seit Jahren arbeiten (Man könnte das Datenerfassungssystem natürlich auch so prgrammieren, dass es genauso aussieht wie Excel. Dann könnte man den Schulungsaufwand minimieren...).
Wenn es diesen "jemand" gibt, ziehe ich den Hut!

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix®

Na dann grins mal freundlich friedfertig weiter :-) Falls Du 'ne Lösung weisst, die nicht so fatal an die 80er erinnert, freue ich mich über jeden Hinweis ;-). Trotzdem danke für´s Feedback (auch wenn´s vielleicht in einem "Meinung"-Thread besser aufgehoben wäre!)

Grüße
Alf

[fastix®]

Moin!

Klar. Dieser "jemand" muss es nur schaffen, einer Konzernleitung zu verkaufen, dass sie für teures Geld und mit viel Zeitaufwand ein Datenerfassungssystem programmieren soll, obwohl Excel alles das, was man braucht, bereits seit Jahren kann.

Wie man sieht kann es genau das eben nicht. Es ist als Datenerfassungsprogramm absolut ungeeignet. Zum "herumspielen" mit Daten schon eher.

Du hast MySQL angeprochen. Natürlich ist das keine Datenbank, sondern ein Datenbank- Management- System. Was jetzt die genannten Futures betrifft: Brauchst Du diese und weisst, dass MySQL sie nicht hat, dann setze es einfach nicht ein. Brauchst Du diese nicht, dann spare Dir die Lizenzgebühren.

Übrigens: wie bekommt man Daten aus MySQL nach Excel: ganz einfach... Export als CSV oder HTML- Tabelle. Beides kann Excel dann wieder öffnen. Die HTML- Tabelle sogar via Webserver direkt und man kann in der Abfrage auch einbauen, wie die Datumsformate sein sollen....

Voraussetzung auch hier: nicht zu viele Zeilen, sonst hat Excel ein Problem.

Was jetzt das Problem betrifft: Wie übertragt Ihr denn die Daten? Etwa via Webserver? Dann könnte die Dateigröße schlicht das Problem sein.

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix®

[Alf]

Hi,

obwohl Excel alles das, was man braucht, bereits seit Jahren kann.

Wie man sieht kann es genau das eben nicht. Es ist als Datenerfassungsprogramm absolut ungeeignet. Zum "herumspielen" mit Daten schon eher.

Einigen wir uns darauf, dass das gesamte System bereits seit mehreren Jahren problemlos läuft. Excel ist aus verschiedenen Gründen exakt das Tool, das für den Schritt der Datenerfassung benötigt wird und es funktioniert. Erklärungen hierzu führen uns aber bei meiner Frage nicht weiter. Die Entscheidung für mySQL wurde vom Kunden getroffen, aus welchen Gründen auch immer...

Übrigens: wie bekommt man Daten aus MySQL nach Excel: ganz einfach... Export als CSV oder HTML- Tabelle. Beides kann Excel dann wieder öffnen.

Ja, so kriegt man Daten aus mySQL in´s Excel. Meine Frage war aber genau das Gegenteil: Daten von Excel in mySQL.

Was jetzt das Problem betrifft: Wie übertragt Ihr denn die Daten? Etwa via Webserver? Dann könnte die Dateigröße schlicht das Problem sein.

Die Daten werden direkt von den Anwendern im konzerninternen Netz auf dem Server abgelegt, auf dem die mySQL läuft. Kein Web oder sonst irgendwas dazwischen. Die Daten liegen dort vollständig und korrekt vor. Wie gesagt funktioniert das auch schon seit Jahren mit anderen Datenbanken. Nur der Import in die mySQL klappt nicht mit größeren Dateien. Und darum meine Frage, ob jemand ein Tool kennt (und auch schon benutzt hat), mit dem man größere Excel-Sheets in die mySQL importieren kann.

Grüße
Alf

[fastix®]

Moin!

HUnd darum meine Frage, ob jemand ein Tool kennt (und auch schon benutzt hat), mit dem man größere Excel-Sheets in die mySQL importieren kann.

Wenn Du über CSV nachdenken möchtest (genau genommen wohl auch wenn nicht...):

Eine weitere Lösung könnte Perl sein. Der Transfer soll doch skriptgesteuert stattfinden (ich nehme mal an: AT- Jobs: da wäre Perl sehr gut geeignet)

Perl kann mit CSV, bei installiertem Modul auch mit Excel umgehen, Datumsformate anpassen und mit MySQL wunderbar kommunizieren. Perl- Programmierer findest Du massenhaft am Markt, das Ergebnis derer Bemühungen ist zwangsläufig OpenSource (= Investitionssicherheit). Dies erscheint mir als eine mögliche und verkaufbare Lösung (wer auch immer sie strickt). Und direkt teuer ist diese Lösung auch nicht... Ich vermute, der Aufwand hält sich in Grenzen.

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix®

[fastix®]

Moin!

Wir können es ja auch anders machen:

Du schickst mir den Dump, mit dem man die Datentabelle erstellt, eine Beispieldatei, die beim Import Probleme macht, ich schreibe das Skript und verate Dir, wenn es läuft, was es kosten soll :)

Ich habe gelesen, dass Du 1,2 davon brauchst (Insert + Update) (Der Unterschied ist minimal)

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix®

[Alf]

Danke Dir!

Das Script schreiben kann ich. Dann haben wir aber wieder die Situation, dass wir ausserhalb unseres eigentlichen Systems ein hausgemachtes Zusatzscript laufen lassen, das nur der Programmierer kennt. Führt also zu Frickellösungen und ist genau das, was ich vermeiden möchte.

Grüße
Alf

[fastix®]

Moin!

Das Script schreiben kann ich. Dann haben wir aber wieder die Situation, dass wir ausserhalb unseres eigentlichen Systems ein hausgemachtes Zusatzscript laufen lassen, das nur der Programmierer kennt. Führt also zu Frickellösungen und ist genau das, was ich vermeiden möchte.

Nein, eben nicht außerhalb eures Systems: Die Daten werden nach wie vor (ohne jede Änderung an den Vorlagen) auf dem Server abgelegt.
Vormittags wir ein Perl- Skript gestartet, dass aus den Excel- Dateien die Daten ausließt und in die Datenbank einliest, nachmittags eines, dass diese Daten dann updatet.

Dazu muss das (Perl-)Skript nur folgendes können:

Alle Dateien im Verzeichnis einlesen (Dateinamen)
Für jede Datei {
  öffnen
  Für jeden Datensatz(Zeile) {
     Daten auslesen und bearbeiten
     SQL- String (Insert oder Update, eventuell mit Entscheidung)bilden
     in DB einpflegen
  }
}

"Literatur": http://computing.ee.ethz.ch/sepp/perl-excel-1.0-ds.html

Das sieht nicht nach wirklich viel Arbeit aus...

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix®

[fastix®]

Moin!

Klar. Dieser "jemand" muss es nur schaffen, einer Konzernleitung zu verkaufen, dass sie für teures Geld und mit viel Zeitaufwand ein Datenerfassungssystem programmieren soll, obwohl Excel alles das, was man braucht, bereits seit Jahren kann.

Wie man sieht gibts mit der Lösung ganz offensichtlich heftig Probleme. Dir ist aber klar, dass man, so man in die Optik von Microsoft- Programmen verliebt ist auch Access als MySQL- Client verwenden kann? Das kann wenigstens mit größeren Datenmengen umgehen.

Dann müsste er die Konzernmitarbeiter davon überzeugen, dass sie für teures Geld und mit viel Zeitaufwand Schulungen für ein Datenerfassungssystem besuchen müssen, obwohl sie die Daten auch bequem mit Excel erfassen können, mit dem sie schon seit Jahren arbeiten (Man könnte das Datenerfassungssystem natürlich auch so prgrammieren, dass es genauso aussieht wie Excel. Dann könnte man den Schulungsaufwand minimieren...).

Ja, klar. Das mit dem Aussehen ist das kleinste Problem. Aber wie mache ich das mit den Unzulänglichkeiten von Excel gerade in dieser Angelegenheit? Ganz schön aufwendig... Und warum sollte so aussehen wie Excel? Es gibt Gestaltungsmöglichkeiten, die den Lernaufwand erheblich reduzieren.
Ich würde ja fast dazu neigen ein Datenerfassungssystem hinzustellen, welches deutlich weniger universell ist, Fehler sofort reklamiert ("im Jahr 004 hat dieses Unternehmen noch nicht bestanden"), dessen Verteilung kein Problem darstellt (womöglich webbasiert)- also auch keine Sorgen mit den Updates macht, dessen Bedienung eben nicht via vom Benutzer selbstgestrickter Excelsheets geschieht, was zweifelsfrei zu Problemen und Schulungsaufwand führt (ok: ich habe das Teil noch nicht gesehen).

Aber um den Weg zur Lösung zu kennen müsste man erst mal das Problem definieren... Deines ist/war die Software, die scheinbar das Problem verursacht. Die in meinen Augen beste Lösung ist halt das VBA-Script, welches das bestehende Problem im Augenblick löst. Aber bei 15000 Datensätzen wird die Arbeit mit Excel schon lang keinen "Spaß" mehr machen. Mit jedem weiteren Datensatz steigt die Verarbeitungsdauer expotentiell an und bei 64536 ist definitiv Schluß. Darüber würde ich mir jedenfalls langsam Sorgen machen- 25% davon sind erreicht.

Du hast irgendwo geschrieben "Zurück zu den 80ern- gar keine so schlechte Idee:

[Aus meinem Leben:]
Einbuchen im Hotel mit einer textbasierten Oberfläche und Menüsystem: 1 Minute
Einbuchen im Hotel mit Windows- Klicki- Bunti Oberfläche (naja: die war/ist wirklich schlecht): 3 Minuten

Ach so: schon mal getestet, was passiert, wenn die Excel- Sheets nicht mit Excel, sondern mit OpenOfficeCalc geschrieben werden? Irgend jemand hat mir ins geflüstert, das würde "bessere" Excel- Dateien schreiben als Excel selbst - und es kann ganz gut mit Datenbanken direkt oder per ODBC oder JDBC umgehen, mit MySQL sogar nativ :)

Eins noch: Ich habe früher einmal auch hardcore- mäßig allerhand mit Excel gemacht. Heute bin ich davon überzeugt für vieles, was ich damals aufwendig strickte, wesentlich geeignetere Mittel zu haben. Da ging es _auch_ um Datenerfassung. Glaube mir, ich kenne Excel verdammt gut. Und tja, es war auch ein Konzern. Irgendwie scheinen die ein Fable für Frickellösungen (und genau das habt Ihr vor Euch) zu haben, solange die auf irgendwas von Microsoft basieren :)

Ach so: Ich behaupte nicht, Excel sei eine schlechte Software. Ich sage: "Excel ist für diesen Zweck nicht geschaffen und nicht geeignet".

Sag mal: Der Konzern ist nicht etwa ziemlich gelb? Aber wahrscheinlich sind die alle gleich...

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix®

[Alf]

:-)

Nein, der gelbe Konzern ist bisher noch nicht dabei. Aber die sind wirklich alle gleich.

Nachdem wir jetzt bei "Meinung" sind, ein wenig Hintergrund:

Die Rohdaten werden bei einer großen deutschen Konsumforschungsgesellschaft eingekauft. Sie werden dort vom Server abgeholt und mit einem Excel-Makro in die Form gebracht, die wir brauchen. Dieser Excel-Makro wird ebenfalls von dieser Gesellschaft geliefert. D.h. wir haben definitiv Excel-Dateien vor uns liegen, ohne bisher Einfluß darauf gehabt zu haben. Jetzt haben wir die Möglichkeit, die Daten noch einmal anzufassen oder sie so wie sie sind in die DB einzulesen. Also haben wir sie bisher einfach in die DB importiert und dort weiter verarbeitet. Der Output ist dann ein Web-basiertes Reporting-System ("Bunte Diagramme", Listen etc.). Wir brauchen also weder Excel, noch sonst ein externes System für die eigentliche Arbeit. Es gibt für uns nur zwei Umgebungskonstanten, an denen wir nichts ändern können:

Importdaten: Excel (anders kriegen wir die Daten nicht)
Datenbank: mySQL (Kundenwunsch)

Mit dem Datenvolumen mache ich mir keine Sorgen, da wir rein rechnerisch von den Datenart her nicht über maximal 20000 kommen können.

Und ja, das Problem ist dieses Tool, das wir für den Import benutzen. Die Lösung mit dem VBA-Script wird´s wohl auch werden. Hätte ich nur gerne vermieden, da ich die Daten eigentlich nicht anfassen will, bevor sie in der DB sind. Ausserdem werden die Daten zweimal täglich importiert. Vormittags kommen vorläufige Daten, die dann nachmittags mit überprüften und bestätigten Daten überschrieben werden. Das Tool bietet eine "Update"-Funktionalität, die extrem komfortabel ist. Wenn wir auf die VBA-Lösung zurückgreifen, müssen wir die Update-Funktionalität auch noch extra schreiben. Und jeder zusätzliche Schritt bietet Potenzial für zusätzliche Fehler...

Naja, so hat halt jeder seine Sorgen.

Grüße
Alf