Messdaten (US-Format) nach DE konvertieren

Xtract: Messgeräte, Daten aus dem Internet, wissenschaftliche Auswertungen sind oft in dem US-Format (Zahlen mit Dezimalpunkt, Datum in MM/TT/JJJJ, etc) vorhanden. Mit Power Query gibt es einen gut nachvollziehbaren Weg, diese in das nationale-Format umzuwandeln.

  Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung   

Viele Messgeräte können zwar Daten in einem spezifizierten Format ausgeben, aber sehr oft basiert das Ergebnis auf der US-Norm. Das bedeutet, dass es einen Dezimalpunkt, ein Komma als Tausendertrenner gibt und das Datum im Format MM/DD/YYYY bzw. MM.DD.YYYY vorliegt und die Zeiten eventuell auch noch im am/pm-Format.

In einem Excel-Forum habe ich gerade eine heiße Diskussion mit erlebt, wie denn nun solche Daten am besten in das deutsch Format umzuwandeln seien. Einige wichtige Eckpunkte dabei sind, dass täglich neue Daten dazu kommen, die Daten als *.csv vom Messgerät exportiert werden und dass es sich um sehr große Datenmengen handelt.

Ich räume ein, dass ich da manches hinein interpretiert bzw. ergänzt habe, aber das Ganze könnte so der Realität entsprechen. Im Forum wurde eine Excel-Datei eingestellt, wo das Datum am 05.09.2016 begann und dann in 2-Minuten-Abständen Messwerte dokumentiert wurden. Das eigentliche Problem war, dass das US-Datum (Schreibweise: 09.05.2016 oder 09/05/2016) automatisch als Datum erkannt wurde und ab dem 13.9. natürlich die Probleme beginnen, weil (im Original) 09.13.2016 einfach kein gültiges Datum ist. Und Excel merkt keineswegs von alleine, dass es sich um das US- und nicht das DE-Format handelt.

▲ nach oben …

Ich habe die Muster-Datei einmal etwas verändert; damit das Ganze etwas übersichtlicher ist, sind es jetzt Abstände von ca. 6 Stunden. Das Prinzip aber ist ja gleich. Und am besten, Sie laden die Datei gleich hier von unserem Server herunter.

Die auf den ersten Blick einfachste Möglichkeit, die Daten gleich beim Einlesen so zu importieren, dass ein deutsches Datum in Excel steht, wurde (Stand 12.09.16) mangels genauer Angaben des Fragestellers hin und her diskutiert und nicht abschließend bewertet. Den Weg (Text in Spalten) können Sie hier im Blog nachlesen. Insbesondere unter dem Aspekt, dass die Datenmenge extrem groß ist und die Tabelle auch andauernd aktualisiert werden muss halte ich einen anderen Weg für zielführender: Power Query heißt das „Zauberwort“. Unter Umständen ist auch PowerPivot wegen der unter Umständen extrem hohen Zahl an Datensätzen sinnvoll.

Aktivieren Sie die Menüleiste für Power Query (PQ) und rufen Sie die Daten aus der *.csv ab. Das geht über den Weg Aus Datei und natürlich Aus CSV. Es öffnet sich das Vorschaufenster:

Vorschau auf die zu importierenden Daten mit Einstellungsmöglichkeiten

Vorschau auf die zu importierenden Daten mit Einstellungsmöglichkeiten

Auch wenn es verführerisch ist, der Dateiursprung ist korrekt, auch wenn Sie wissen, dass die Zahlen im US-Format sind. In der Vorschau werden die Dezimalpunkte zwar einfach unterschlagen, aber bei dieser Einstellung geht es um Einstellungen wie beispielsweise Umlaute. – Da Sie noch einiges an den Daten bearbeiten müssen, klicken Sie auf die Schaltfläche Bearbeiten. Es öffnet sich der Abfrage-Editor, wo Sie die ganze Arbeit verrichten werden:

Der Query-Editor ist die Werkbank für die Daten

Der Query-Editor ist die Werkbank für die Daten

Wie schon in der Vorschau zu sehen war, die aus dem US-Messgerät stammenden Dezimalpunkte wurden nicht durch ein Komma ersetzt sondern einfach entfernt und die Zahlen sind dadurch natürlich zu hoch im Wert. Da hilft nur eine „Radikalkur“. 😉 Die erste Überschrift ist ja schon markiert, Shift und ein Klick in die letzte Überschrift. Jetzt sind alle Spalten ausgewählt. In der Gruppe Transformieren ist der Datentyp Beliebig eingestellt. Ein Klick auf den DropDown-Pfeil DropDownPfeil und wählen Sie Text. Und schon sieht das alles wieder so aus, wie es in der *.csv steht.

Hinweis: Eine Spalte können Sie nur markieren, wenn Sie auf die Überschrift klicken. Wählen Sie nun die zweite bis letzte Spalte, also alle außer Date/Time. Jetzt ein Rechtsklick in eine Überschrift:

Treffen Sie genau diese Auswahl

Treffen Sie genau diese Auswahl

Umgehend wird sich dieses Fenster öffnen. In der Abbildung sind schon die erforderlichen Änderungen vorgenommen:

Hier wird der Datentyp geändert

Hier wird der Datentyp geändert

Auch wen die Null-Werte Ganzzahlen sind, Dezimalzahl ist die bessere Auswahl. Und das ursprüngliche Gebietsschema ist Englisch (USA). Ein Klick auf OK und Sie erkennen schon an der Ausrichtung, dass es nun numerische Werte sind. Und vor allen Dingen mit Komma und wirklich als Dezimalzahl.

Bevor Sie sich die erste Spalte vornehmen, scrollen Sie einmal nach ganz unten in der Liste. Markieren Sie die erste Spalte und da es sich ja um den Datentyp Datum/Uhrzeit handelt, ändern Sie doch probehalber einmal den Datentyp dieser Spalte auf genau diesen Typ. Autsch, das ging schief, denn den 09.13.2016 gibt es nun einmal nicht.  💡 

Also wieder als Text formatieren. Und dann den gleichen Weg wie bei den anderen Spalten einschlagen, nur dass dieses Mal der Datentyp Datum/Uhrzeit ist. Und das haut dann auch hin. – Was ich bislang verschwiegen habe: Datum und Uhrzeit sollen in zwei getrennten Spalten stehen. Die erste Spalte ist ja immer noch markiert. Menü Spalte hinzufügen | Doppelte Spalte und schon wird ein Duplikat der Spalte als letzte Spalte eingefügt. Rechtsklick in die Überschrift, im Kontextmenü Verschieben | An den Anfang. Die nun erste Spalte als Datum formatieren und die zweite als Zeit. Dann bleibt nur noch, die beiden Überschriften sinnvoll umzubenennen. Das geht am besten, wenn Sie in die Überschrift klicken, F2 und dann schreiben.

▲ nach oben …

Ergänzung vom 14.04.2017 Hinweis: Dass Excel mit seinem „vorauseilenden Gehorsam“ mitunter ganz schön nerven kann das merken Sie vor allen Dingen dann, wenn beim Import von Text- oder CSV-Dateien Inhalte oder eingegebene Texte wie beispielsweise 11-4 eigenwillig in ein Datum umgewandelt werden. Das wird dann einfach als 11. April oder 4. November interpretiert. Und sie haben nach dem Import auch nicht die geringste Chance, das einfach in das eigentliche gewünschte Format umzuwandeln. Das ginge nur per Hand. Hier zeigen sich aber auch ganz klar die Stärken von Power Query, wo sie mit wenigen Mausklicks diese Probleme umgehen können. Wenn Sie diese hier verwendeten Daten nicht per Power Query sondern über den klassischen Weg importieren, werden sie die eine oder andere Überraschung erleben. 😕 

Da wird Ihnen beispielsweise die CSV-Datei als Japanische Kodierung angeboten. Und es ist scheinbar so, dass sie keine Chance haben, die kalendarischen Daten der 1. Spalte korrekt zu importieren. Ich bin auch erst dank des Hinweises eine aufmerksamen Nutzerin des Blogs (danke Sabine) über diesen Umstand gefallen. Und ich brauchte auch mehrere Anläufe, um diese Datei so in ein Excel-Sheet zu bringen, dass die korrekten Daten dort enthalten sind.

Ich nutze hier einfach mal die Gelegenheit Ihnen zu zeigen, wie ich das ganze (in Excel 2013) ohne VBA aber doch mit einigen kleinen Umwegen gelöst habe:

  • In einem leeren Tabellenblatt Menü Daten | Externe Daten abrufen | Aus Text
  • Im erschienen Dialogfenster werden Sie die entsprechende Datei auswählen und es zeigt sich kurz danach der Textkonvertierungs-Assistent mit diesem Dialog:

Der erste Schritt des Textkonvertierungs-Assistenten

Der erste Schritt des Textkonvertierungs-Assistenten

  • Die Einstellung Getrennt ist korrekt.
  • Import beginnt in Zeile 1 stimmt auch.
  • Datei Ursprung 932 : Japanisch ist ja nicht unbedingt korrekt. Dazu gleich mehr.
  • Das Häkchen bei Die Daten haben Überschriften sollte gesetzt werden.

Zum Thema „Japanisch“: ich kann nur vermuten, dass entweder das °-Zeichen oder aber (was wahrscheinlicher ist) die Kombination von kalendarischem Datum und Uhrzeit in einer Spalte verantwortlich sind. Unwahrscheinlich ist, dass beim Export der Daten etwas anders gelaufen ist als üblich. Denn CSV ist eine reine Text-Datei ohne irgendwelche unsichtbaren Marker oder so. Und ich habe das File dahingehend auch auf versteckte Inhalte untersucht… Wie dem auch sei, sie könnten ist dabei belassen, wenn keine Sonderzeichen bzw. Umlaute im Text vorhanden sind. Ich belasse es erst einmal aus Gründen der Demonstration bei dieser Einstellung. Sie werden später beispielsweise Windows (ANSI) oder den letzten Eintrag der Liste 1252: Westeuropäisch (Windows) wählen und bei der Gelegenheit gleich feststellen, dass das °-Zeichen in der Überschrift auch korrekt dargestellt wird.

  • Im 2. Schritt des Assistenten werden sie in jedem Fall das Semikolon als Trennzeichen für die Spalten festlegen. Anschließend Weiter >.
  • Im 3. Schritt des Assistenten wird die 1. Spalte der Daten markiert:

3. Schritt des Import-Assistenten

3. Schritt des Import-Assistenten

  • Hier werden sie bei der Option Datum den Eintrag MTJ für die Reihenfolge Monat Tag Jahr wählen. Anschließend ein Klick auf die Schaltfläche Weitere… und dort legen Sie fest, dass das Dezimaltrennzeichen (in den Originaldaten) ein Punkt . und das 1000er-Trennzeichen ein Komma , ist.

So weit, so gut. Vielleicht sind sie bei ihren eigenen Versuchen auch genau diesen Weg gegangen und haben dennoch Schiffbruch erlitten. Die kalendarischen Daten der 1. Spalte wurden großenteils von Excel falsch interpretiert. Und ist einmal den Import abgeschlossen, lässt sich auch mit keiner Tastenkombination oder anderen Tricks etwas daran ändern.

Hier hilft nur ein etwas aufwendiger Umweg, der mit zusätzlicher Arbeit verbunden ist. Die beiden Alternativen: VBA bzw. Import der Daten über Oppen Office bzw. Libre Office mag ich Ihnen nicht antun. Darum: < Zurück und sie sind wieder im Schritt 2 des Assistenten. Geben Sie hier zusätzlich das Leerzeichen als Trenner ein und wegen der Überschrift der 1. Spalte „Date/Time“ auch noch ein Klick bei Andere und in das Textfeld tragen Sie den / ein. In der Vorschau können Sie nun schon ganz klar erkennen, dass Datum und Zeit in jeweils einer eigenen Spalte stehen werden. Per Weiter > wieder zum 3. Schritt, dort noch einmal kontrollieren, ob die eben vorgenommenen Einstellungen noch so sind und ein Klick auf Fertig stellen. Die restlichen Schritte kennen Sie gewiss.

Was positiv ist: das Datum wurde korrekt übernommen, die Zeiten auch. Dennoch ist etwas Nacharbeit erforderlich, selbst wenn Sie Datum und Zeit in getrennten Spalten stehen lassen wollen. Sie werden in jedem Falle die Überschriften anpassen müssen, weil diese wegen der Spaltentrennung bei den Leerzeichen auch an ungewollten Stellen gesplittet worden sind. Aus jetziger Sicht (Date und Time sind 2 Spalten) wird die Überschrift der 3. Spalte DP °C und die folgende Spalte DP °F sein; Status ist o. k., dann folgt Alarm A und die letzte Spalte Alarm B. Und denken Sie daran, eventuell das °-Zeichen zu ersetzen, falls Sie es bei der Japanisch-Codierun belassen hatten.

Das war’s, es sei denn sie wollen wieder Datum und Zeit in einer einzigen Spalte darstellen. In dem Fall würden sie in einer freien Spalte, beispielsweise Zelle H2 diese Formel eingeben:
=SUMME(A2:B2)
Sie werden zwar zu Anfang nur das Datum sehen, aber wenn sie die Zelle im benutzerdefinierten Zahlenformat TT.MM.JJJJ hh:mm:ss formatieren, steht dort das gewünschte Datum (Einzahl von Daten). Anschließend kopieren Sie -beispielsweise durch Doppelklick auf das Ausfülltkästchen- die Formel bis zur letzten Datenzeile nach unten. Die gesamten Daten der (beispielsweise) Spalte H in die Zwischenablage kopieren und an gleicher Stelle Als Wert wieder einfügen. Nun können Sie auf beliebige Art und Weise diese Daten in die jeweils gleiche Zeile der Spalte A verschieben und anschließend die Spalte B löschen. Danach bleibt nur noch, die Überschrift der Spalte A auf den Ursprungswert Date/Time zurück zu setzen.

Sie sehen, das ist doch etwas mehr an Umstand. Hier sind die Stärken des neuen Tools Power Query ganz klar zu sehen. Allerdings ist diese moderne Möglichkeit in älteren Excel-Versionen als auch den Versionen für Apple und natürlich auch den Tablet-Versionen nicht gegeben. Da ist der hier genannte Weg durchaus denkbar und aus meiner Sicht auch eine gute Alternative.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50  freuen …

Dieser Beitrag wurde unter Datentyp anpassen, Datum und Zeit, Downloads, File-Handling, Formatierung, Musterlösungen, Ohne Makro/VBA, Power Query abgelegt und mit , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.