Excel-Makro liest CSV-Dateien in Vorlagen ein

Problembeschreibung

Sie bekommen Daten in CSV-Dateien geliefert und müssen diese in ein vorhandenes Excel-Datei eintragen und ggf. auch Grafik neuerzeugen. Ab und zu hat eine dieser Dateien auch mehr als 255 Spalten oder 65536 Zeilen.

Lösung

Das Excel-AddIn csvimport.xla hilft in diesen Fällen, bei dem die Excel- Möglichkeiten csv-Dateien zu importieren mehrere Arbeitsschritte benötigen oder gar gänzlich scheitern.

Vertauschen von Importspalten
Berechnung von zusätzlichen Spalten und Erstellung von Grafiken
bis zu 1.200 Spalten breite csv-Dateien
mehr als 65.535 Zeilen lange csv_datei vollständig laden (auch ohne die neuste Excel-Version)
Die Position eines Eintrages anhand eines Schlüsselwortes in derDatendatei bestimmen zu können.

Dazu verwendet das Excel-Makro Vorlagen, die in ihren Excel-Zellen später näher beschrienbene Keywörter besitzen. Mittels drei Menü-Einträge, die das Makro erzeugt, können die csv-Dateien in diese Vorlagen importiert werden. Der zweite Eintrag erspart das Anlegen von dieser Vorlage für das Einlesen von csv-Datein, mit mehr als 255 Spalten besitzen. Der dritte Eintrag unterstützt Sie dabei, eine beladene Vorlage von den eventuell nicht genutzen Markierungen zu bereinigen.

Import in Vorlage

In einer Vorlage können die Zeilen einer CSV-Datei als Werte in Spalten eines Blattes oder als feste Position in einem oder mehrere Blätter einer Mappe eingetragen werden.

CSVImport $COLUMN Nach dem Kopieren der Vorlage wird die Vorlage darauf untersucht, ob sie in der letzten beschriebenen Zeile eines beliebigen Blattes Einträge in der Form "$COLUMN <Zahl>" besitzt. In der Zelle wird die entsprechende Spalte der Daten-Datei eingetragen. Alle anderen Zellen der letzten Zeile werden 1:1 kopiert, sodaß auch Excel-Funktionen herunterkopiert werden (siehe Spalte B).

CSVImport $COLUMN Wenn keine Zellen mit "$COLUMN ..." gefunden werden, wird auf allen Blättern nach Zellen mit Einträgen der Form "$MATRIX <Wert>" gesucht.

Die gleiche Daten-Datei wie beim Beispiel von vorhin ergibt dann ein anderes Bild.

1;234
2;345

Erhält diese Daten-Datei eine dritte Zeile:

1;234
2;345
5;456

würde diese in der ersten Vorlage in Zeile 4 eingetragen, in der zweiten Vorlage würde ein Fehlen des Elementes "$MATRIX 5" bemerkt. Die Werte hinter dem Keywort $MATRIX müssen übrigens nicht nummerisch sein, es können auch andere Texte sein.

Eine Mischung der beiden Keywörter $COLUMN und $MATRIX ist nicht möglich.

CSVImport Einstellung Das gewünschte Trennzeichen zwischen den einzelnen Spalten kann über den Menüeintrag "Einstellung" "Trennzeichen CSV" eingestellt werden. (Hinweis: das Bild ist etwas veraltert, es fehlen die Trennzeichen $ und ~).

Hier kann man [unten links] auch einstellen, dass die Datenzeilen mit einem "D" in der ersten Spalte gekennzeichnet sein müssen. Die letzte Zeile, die mit einem "X" in der ersten Spalte gekennzeichnet ist, wird dann überprüft. Wenn Sie nicht vorhanden ist, ist bei der Erzeugung oder beim Transport der Datei ein Fehler (wie fehlender Plattenplatz) wahrscheinlich.

Auch kann man einstellen, ob die markierten Positionen, zu den keine Werte in der CSV-Datei gefunden wurden, gelöscht werden sollen. Diese Markierungen kann mit dem dritten Menüeintrag "Löschen der Positionsmerker" auch nachträglich in einer Kopie der Datei gelöscht werden. Dies kann dazu genutzt werden sukzessive in eine Jahresvorlage die Monatswerte einzulesen, ohne dass immer der gesamte Zeitraum berichtet werden muß. Wenn die Daten eines Monats eingelesen werden, löscht man die Positionsmerker nicht und speichert das Ergebnis als Vorlage für den nächsten Monat ab. Zur zwischenzeitlichen Präsentation der Ergebnisse (, bei der die "$MATRIX ..."-Einträge nur stören würden, ) löscht man alle noch existierenden Positionsmerker durch den entsprechenden Menüeintrag.

Die Einstellungen, die Sie über das Trennzeichen treffen, werden auch nach Beendigung gespeichert, sodaß Sie diese nicht jedes Mal erneut einstellen müssen.

> 255 Spalten

Bei diesem Unterpunkt wird nur nach der Datendatei gefragt. Danach wird die Datei komplett eingelesen. Die 256. Spalte wird auf dem 2. Blatt in Spalte 1 geschrieben. Es können dabei bis zu 1200 Spalten eingelesen werden, die maximale Anzahl der Spalten wird gemeldet.

> 65.536 Zeilen

Das Makro liest bei einer Vorlage mit $COLUMN auch mehr als 65.536 Zeilen ein. Es legt automatisch ein weiteres Blatt an und fängt in der ersten Zeile mit den Daten an, die nicht auf das letzte Blatt passten.

Wenn Sie keine Spalten weglassen oder in ihre Reihenfolge drehen wollen, können Sie auch einfach eine leere Excel-Mappe als Vorlage nehmen. Am besten speichern Sie hierfür eine Mappe mit nur einem Blatt ab, da nur das erste Blatt verwendet und schon beim nächsten benötigten auf jeden Fall ein neues angehängt wird.

Dateien

Die Datei csvimport.zip enthält das Excel-AddIn in der Version 0.26 zum Einlesen von CSV-Dateien.

Hinweis

Die Daten können auch direkt aus einem Archiv gelesen werden (siehe entsprechender Excel-Tipp).

Es wird ein Fehler gemeldet, wenn Sie für den Namen der abzuspeichernden Datei einen Namen ohne die Extension ".xls" verwenden. Dieser Fehler wird demnächst korrigiert.

Tests mit Dateien über eine Millionen Zeilen ergaben eine Einschränkung des Makros dessen Ursache noch nicht vollständig geklärt wurde. Nach etwas mehr als 1.047.000 Zeilen bricht das Makro mit einem Laufzeitfehler 1004 (bekannt auch als Notbremsen Fehler bei Excel) ab. Diese Anzahl ist unabhängig von der Breite der eingelesenen Zeilen, der Hauptspeicher des Rechners ist in der Regel noch nicht ausgelastet. Das Problem wird noch weiter untersucht.

Änderung von Version 0.26 zu 0.27:

Als zusätzliches Trennzeichen wurde die Tilde ~ eingebaut.
Die Performance wurde gesteigert, in dem überflüssige Debug-Ausgaben auskommentiert wurden.

Änderung von Version 0.25 zu 0.26:

Bei der Matrixschreibweise können jetzt auch Texte oder Uhrzeiten für die einzufügenden Werte verwendet werden. Dadurch geht die Möglichkeit verloren, mehrfach vorkommende Werte in der Datendatei aufzusummieren.
Werden mehrere Zellen mit den gleichen Bezeichnung (z.B. "$MATRIX Datum") gekennzeichnet,wird immer nur die erste gefundene Zelle ersetzt. Soll ein eingelesener Wert mehrmals in der Exceldatei verwendet werden, wird die Verwendung von Excelverweisen empfohlen.

Änderung von Version 0.24 zu 0.25:

Als zusätzliches Trennzeichen wird der Dollar zugelassen.

Änderung von Version 0.22 zu 0.24:

Unix-Dateien werden automatisch erkannt und korrekt eingelesen (noch nicht bei > 255 Zeilen).

Änderung von Version 0.21 zu 0.22: Einige Kleinigkeiten wurden geändert

die Fileextension *.dat wurde ergänzt,
eine kleinere Anzahl Trennzeichen als Felder wird jetzt aktzeptiert
Vorlagen ohne Markierungen werden auf dem ersten Arbeitsblatt befüllt.

Geplant sind weitere Kontrollen und Informationserweiterungen bei der Kennzeichnung der Datenzeilen.

Kommentare dazu sind -wie immer- erwünscht.