Messdaten (US-Format) nach DE konvertieren

  Wis­sens­stand: Lev­el 1 ⇒ Ein­steiger in PQ, keine/kaum Erfahrung   

Viele Mess­geräte kön­nen zwar Dat­en in einem spez­i­fizierten For­mat aus­geben, aber sehr oft basiert das Ergeb­nis auf der US-Norm. Das bedeutet, dass es einen Dez­i­malpunkt, ein Kom­ma als Tausendertren­ner gibt und das Datum im For­mat MM/DD/YYYY bzw. MM.DD.YYYY vor­liegt und die Zeit­en eventuell auch noch im am/pm-For­mat.

In einem Excel-Forum habe ich ger­ade eine heiße Diskus­sion mit erlebt, wie denn nun solche Dat­en am besten in das deutsch For­mat umzuwan­deln seien. Einige wichtige Eck­punk­te dabei sind, dass täglich neue Dat­en dazu kom­men, die Dat­en als *.csv vom Mess­gerät exportiert wer­den und dass es sich um sehr große Daten­men­gen han­delt.

Ich räume ein, dass ich da manch­es hinein inter­pretiert bzw. ergänzt habe, aber das Ganze kön­nte so der Real­ität entsprechen. Im Forum wurde eine Excel-Datei eingestellt, wo das Datum am 05.09.2016 begann und dann in 2‑Minuten-Abstän­den Mess­werte doku­men­tiert wur­den. Das eigentliche Prob­lem war, dass das US-Datum (Schreib­weise: 09.05.2016 oder 09/05/2016) automa­tisch als Datum erkan­nt wurde und ab dem 13.9. natür­lich die Prob­leme begin­nen, weil (im Orig­i­nal) 09.13.2016 ein­fach kein gültiges Datum ist. Und Excel merkt keineswegs von alleine, dass es sich um das US- und nicht das DE-For­mat han­delt.

▲ nach oben …

Ich habe die Muster-Datei ein­mal etwas verän­dert; damit das Ganze etwas über­sichtlich­er ist, sind es jet­zt Abstände von ca. 6 Stun­den. Das Prinzip aber ist ja gle­ich. Und am besten, Sie laden die Datei gle­ich hier von unserem Serv­er herunter.

Die auf den ersten Blick ein­fach­ste Möglichkeit, die Dat­en gle­ich beim Ein­le­sen so zu importieren, dass ein deutsches Datum in Excel ste­ht, wurde (Stand 12.09.16) man­gels genauer Angaben des Fragestellers hin und her disku­tiert und nicht abschließend bew­ertet. Den Weg (Text in Spal­ten) kön­nen Sie hier im Blog nach­le­sen. Ins­beson­dere unter dem Aspekt, dass die Daten­menge extrem groß ist und die Tabelle auch andauernd aktu­al­isiert wer­den muss halte ich einen anderen Weg für zielführen­der: Pow­er Query heißt das „Zauber­wort”. Unter Umstän­den ist auch Pow­er­Piv­ot wegen der unter Umstän­den extrem hohen Zahl an Daten­sätzen sin­nvoll.

Aktivieren Sie die Menüleiste für Pow­er Query (PQ) und rufen Sie die Dat­en aus der *.csv ab. Das geht über den Weg Aus Datei und natür­lich Aus csv. Es öffnet sich das Vorschaufen­ster:

Vorschau auf die zu importieren­den Dat­en mit Ein­stel­lungsmöglichkeit­en

Auch wenn es ver­führerisch ist, der Dateiur­sprung ist kor­rekt, auch wenn Sie wis­sen, dass die Zahlen im US-For­mat sind. In der Vorschau wer­den die Dez­i­malpunk­te zwar ein­fach unter­schla­gen, aber bei dieser Ein­stel­lung geht es um Ein­stel­lun­gen wie beispiel­sweise Umlaute. – Da Sie noch einiges an den Dat­en bear­beit­en müssen, Klick­en Sie auf die Schalt­fläche Bear­beit­en. Es öffnet sich der Abfrage-Edi­tor, wo Sie die ganze Arbeit ver­richt­en wer­den:

Der Query-Edi­tor ist die Werk­bank für die Dat­en

Wie schon in der Vorschau zu sehen war, die aus dem US-Mess­gerät stam­menden Dez­i­malpunk­te wur­den nicht durch ein Kom­ma erset­zt son­dern ein­fach ent­fer­nt und die Zahlen sind dadurch natür­lich zu hoch im Wert. Da hil­ft nur eine „Radikalkur”. 😉 Die erste Über­schrift ist ja schon markiert, Shift und ein Klick in die let­zte Über­schrift. Jet­zt sind alle Spal­ten aus­gewählt. In der Gruppe Trans­formieren ist der Daten­typ Beliebig eingestellt. Ein Klick auf den Drop­Down-Pfeil  und wählen Sie Text. Und schon sieht das alles wieder so aus, wie es in der *.csv ste­ht.

Hin­weis: Eine Spalte kön­nen Sie nur markieren, wenn Sie auf die Über­schrift Klick­en. Wählen Sie nun die zweite bis let­zte Spalte, also alle außer Date/Time. Jet­zt ein Recht­sklick in eine Über­schrift:

Tre­f­fen Sie genau diese Auswahl

Umge­hend wird sich dieses Fen­ster öff­nen. In der Abbil­dung sind schon die erforder­lichen Änderun­gen vorgenom­men:

Hier wird der Daten­typ geän­dert

Auch wen die Null-Werte Ganz­zahlen sind, Dez­i­malzahl ist die bessere Auswahl. Und das ursprüngliche Gebi­etss­chema ist Englisch (USA). Ein Klick auf OK und Sie erken­nen schon an der Aus­rich­tung, dass es nun numerische Werte sind. Und vor allen Din­gen mit Kom­ma und wirk­lich als Dez­i­malzahl.

Bevor Sie sich die erste Spalte vornehmen, scrollen Sie ein­mal nach ganz unten in der Liste. Markieren Sie die erste Spalte und da es sich ja um den Daten­typ Datum/Uhrzeit han­delt, ändern Sie doch probe­hal­ber ein­mal den Daten­typ dieser Spalte auf genau diesen Typ. Autsch, das ging schief, denn den 09.13.2016 gibt es nun ein­mal nicht.  💡 

Also wieder als Text for­matieren. Und dann den gle­ichen Weg wie bei den anderen Spal­ten ein­schla­gen, nur dass dieses Mal der Daten­typ Datum/Uhrzeit ist. Und das haut dann auch hin. – Was ich bis­lang ver­schwiegen habe: Datum und Uhrzeit sollen in zwei getren­nten Spal­ten ste­hen. Die erste Spalte ist ja immer noch markiert. Menü Spalte hinzufü­gen | Dop­pelte Spalte und schon wird ein Dup­likat der Spalte als let­zte Spalte einge­fügt. Recht­sklick in die Über­schrift, im Kon­textmenü Ver­schieben | An den Anfang. Die nun erste Spalte als Datum for­matieren und die zweite als Zeit. Dann bleibt nur noch, die bei­den Über­schriften sin­nvoll umzube­nen­nen. Das geht am besten, wenn Sie in die Über­schrift Klick­en, F2 und dann schreiben.

▲ nach oben …

Ergänzung vom 14.04.2017 Hin­weis: Dass Excel mit seinem „vorau­seilen­den Gehor­sam” mitunter ganz schön ner­ven kann das merken Sie vor allen Din­gen dann, wenn beim Import von Text- oder csv-Dateien Inhalte oder eingegebene Texte wie beispiel­sweise 11–4 eigen­willig in ein Datum umge­wan­delt wer­den. Das wird dann ein­fach als 11. April oder 4. Novem­ber inter­pretiert. Und sie haben nach dem Import auch nicht die ger­ing­ste Chance, das ein­fach in das eigentliche gewün­schte For­mat umzuwan­deln. Das gin­ge nur per Hand. Hier zeigen sich aber auch ganz klar die Stärken von Pow­er Query, wo sie mit weni­gen Mausklicks diese Prob­leme umge­hen kön­nen. Wenn Sie diese hier ver­wen­de­ten Dat­en nicht per Pow­er Query son­dern über den klas­sis­chen Weg importieren, wer­den sie die eine oder andere Über­raschung erleben. 😕 

Da wird Ihnen beispiel­sweise die csv-Datei als Japanis­che Kodierung ange­boten. Und es ist schein­bar so, dass sie keine Chance haben, die kalen­darischen Dat­en der 1. Spalte kor­rekt zu importieren. Ich bin auch erst dank des Hin­weis­es eine aufmerk­samen Nutzerin des Blogs (danke Sabine) über diesen Umstand gefall­en. Und ich brauchte auch mehrere Anläufe, um diese Datei so in ein Excel-Sheet zu brin­gen, dass die kor­rek­ten Dat­en dort enthal­ten sind.

Ich nutze hier ein­fach mal die Gele­gen­heit Ihnen zu zeigen, wie ich das ganze (in Excel 2013) ohne VBA aber doch mit eini­gen kleinen Umwe­gen gelöst habe:

• In einem leeren Tabel­len­blatt Menü Dat­en | Externe Dat­en abrufen | Aus Text
• Im erschienen Dialogfen­ster wer­den Sie die entsprechende Datei auswählen und es zeigt sich kurz danach der Tex­tkon­vertierungs-Assis­tent mit diesem Dia­log:

Der erste Schritt des Tex­tkon­vertierungs-Assis­ten­ten

• Die Ein­stel­lung Getren­nt ist kor­rekt.
• Import begin­nt in Zeile 1 stimmt auch.
• Datei Ursprung 932 : Japanisch ist ja nicht unbe­d­ingt kor­rekt. Dazu gle­ich mehr.
• Das Häkchen bei Die Dat­en haben Über­schriften sollte geset­zt wer­den.

Zum The­ma „Japanisch”: ich kann nur ver­muten, dass entwed­er das °-Zeichen oder aber (was wahrschein­lich­er ist) die Kom­bi­na­tion von kalen­darischem Datum und Uhrzeit in ein­er Spalte ver­ant­wortlich sind. Unwahrschein­lich ist, dass beim Export der Dat­en etwas anders gelaufen ist als üblich. Denn csv ist eine reine Text-Datei ohne irgendwelche unsicht­baren Mark­er oder so. Und ich habe das File dahinge­hend auch auf ver­steck­te Inhalte unter­sucht… Wie dem auch sei, sie kön­nten ist dabei belassen, wenn keine Son­derze­ichen bzw. Umlaute im Text vorhan­den sind. Ich belasse es erst ein­mal aus Grün­den der Demon­stra­tion bei dieser Ein­stel­lung. Sie wer­den später beispiel­sweise Win­dows (ANSI) oder den let­zten Ein­trag der Liste 1252: Wes­teu­ropäisch (Win­dows) wählen und bei der Gele­gen­heit gle­ich fest­stellen, dass das °-Zeichen in der Über­schrift auch kor­rekt dargestellt wird.

• Im 2. Schritt des Assis­ten­ten wer­den sie in jedem Fall das Semi­kolon als Trennze­ichen für die Spal­ten fes­tle­gen. Anschließend Weit­er >.
• Im 3. Schritt des Assis­ten­ten wird die 1. Spalte der Dat­en markiert:

3. Schritt des Import-Assis­ten­ten

• Hier wer­den sie bei der Option Datum den Ein­trag MTJ für die Rei­hen­folge Monat Tag Jahr wählen. Anschließend ein Klick auf die Schalt­fläche Weit­ere… und dort leg­en Sie fest, dass das Dez­i­mal­trennze­ichen (in den Orig­i­nal­dat­en) ein Punkt . und das 1000^er-Trennze­ichen ein Kom­ma , ist.

So weit, so gut. Vielle­icht sind sie bei ihren eige­nen Ver­suchen auch genau diesen Weg gegan­gen und haben den­noch Schiff­bruch erlit­ten. Die kalen­darischen Dat­en der 1. Spalte wur­den großen­teils von Excel falsch inter­pretiert. Und ist ein­mal den Import abgeschlossen, lässt sich auch mit kein­er Tas­tenkom­bi­na­tion oder anderen Tricks etwas daran ändern.

Hier hil­ft nur ein etwas aufwendi­ger Umweg, der mit zusät­zlich­er Arbeit ver­bun­den ist. Die bei­den Alter­na­tiv­en: VBA bzw. Import der Dat­en über Oppen Office bzw. Libre Office mag ich Ihnen nicht antun. Darum: < Zurück und sie sind wieder im Schritt 2 des Assis­ten­ten. Geben Sie hier zusät­zlich das Leerze­ichen als Tren­ner ein und wegen der Über­schrift der 1. Spalte „Date/Time” auch noch ein Klick bei Andere und in das Textfeld tra­gen Sie den / ein. In der Vorschau kön­nen Sie nun schon ganz klar erken­nen, dass Datum und Zeit in jew­eils ein­er eige­nen Spalte ste­hen wer­den. Per Weit­er > wieder zum 3. Schritt, dort noch ein­mal kon­trol­lieren, ob die eben vorgenomme­nen Ein­stel­lun­gen noch so sind und ein Klick auf Fer­tig stellen. Die restlichen Schritte ken­nen Sie gewiss.

Was pos­i­tiv ist: das Datum wurde kor­rekt über­nom­men, die Zeit­en auch. Den­noch ist etwas Nachar­beit erforder­lich, selb­st wenn Sie Datum und Zeit in getren­nten Spal­ten ste­hen lassen wollen. Sie wer­den in jedem Falle die Über­schriften anpassen müssen, weil diese wegen der Spal­tentren­nung bei den Leerze­ichen auch an unge­woll­ten Stellen ges­plit­tet wor­den sind. Aus jet­ziger Sicht (Date und Time sind 2 Spal­ten) wird die Über­schrift der 3. Spalte DP °C und die fol­gende Spalte DP °F sein; Sta­tus ist o. k., dann fol­gt Alarm A und die let­zte Spalte Alarm B. Und denken Sie daran, eventuell das °-Zeichen zu erset­zen, falls Sie es bei der Japanisch-Codierun belassen hat­ten.

Das war’s, es sei denn sie wollen wieder Datum und Zeit in ein­er einzi­gen Spalte darstellen. In dem Fall wür­den sie in ein­er freien Spalte, beispiel­sweise Zelle H2 diese Formel eingeben:
=SUMME(A2:B2)
Sie wer­den zwar zu Anfang nur das Datum sehen, aber wenn sie die Zelle im benutzerdefinierten Zahlen­for­mat TT.MM.JJJJ hh:mm:ss for­matieren, ste­ht dort das gewün­schte Datum (Ein­zahl von Dat­en). Anschließend kopieren Sie ‑beispiel­sweise durch Dop­pelk­lick auf das Aus­fülltkästchen- die Formel bis zur let­zten Daten­zeile nach unten. Die gesamten Dat­en der (beispiel­sweise) Spalte H in die Zwis­chen­ablage kopieren und an gle­ich­er Stelle Als Wert wieder ein­fü­gen. Nun kön­nen Sie auf beliebige Art und Weise diese Dat­en in die jew­eils gle­iche Zeile der Spalte A ver­schieben und anschließend die Spalte B löschen. Danach bleibt nur noch, die Über­schrift der Spalte A auf den Ursprungswert Date/Time zurück zu set­zen.

Sie sehen, das ist doch etwas mehr an Umstand. Hier sind die Stärken des neuen Tools Pow­er Query ganz klar zu sehen. Allerd­ings ist diese mod­erne Möglichkeit in älteren Excel-Ver­sio­nen als auch den Ver­sio­nen für Apple und natür­lich auch den Tablet-Ver­sio­nen nicht gegeben. Da ist der hier genan­nte Weg dur­chaus denkbar und aus mein­er Sicht auch eine gute Alter­na­tive.

▲ nach oben …

Rück­mel­dun­gen / Feed­back gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

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

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 2,50 € freuen … (← Klick mich!)