Ver­mut­lich wer­den sie bei dieser Über­schrift als erstes an Dateien mit der Endung *.txt denken. Diese Files sind aber nur ein Teil dessen, was aus fach­lich­er Sicht zu den Text­dateien gehört. Es gibt (min­destens) 2 weit­ere Date­itypen, wovon eine weitaus öfter als die eben beschriebene Text­datei ver­wen­det wird. Aber sehen Sie selb­st …

Comma separated values

Vielle­icht wer­den sich bei dieser Über­schrift fra­gen, welche Date­ityp hier gemeint ist. Wenn Sie jew­eils das 1. Zeichen der 3 Worte nehmen, kommt dabei eine in den gewiss bekan­nte Datei Änderung her­aus: *.csv. Es ist dur­chaus denkbar, dass sie jet­zt inner­lich „protestieren” und denken oder sagen, dass dieses doch eine Excel-Datei sei. Ganz klar und deut­lich: Nein, csv ist eine reine Text­datei, die gewis­sen und klar definierten Regeln unter­liegt. Sie kön­nen jede csv mit einem beliebi­gen Texte­d­i­tor öff­nen und sich den Inhalt betra­cht­en. Dass sich diese Files bei einem Dop­pelk­lick darauf automa­tisch in Excel öff­nen, das ist eine andere Baustelle. 😎 

Grund­sät­zlich gilt, dass die ver­schiede­nen Spal­ten durch einen definiertes Zeichen getren­nt sind. Im US-For­mat ist dieses typ­is­cher­weise das Kom­ma , (daher auch die Abkürzung Csv), im deutschen Sprach­bere­ich ist dieses meis­tens das Semi­kolon ;. Prinzip­iell hängt das ver­wen­dete Default-Trennze­ichen  aber von den Län­dere­in­stel­lun­gen des Betrieb­ssys­tems ab. Als Beson­der­heit ist zu ver­merken, dass Texte ein­er Spalte, die selb­st das Trennze­ichen enthal­ten (also beispiel­sweise das Kom­ma bzw. das Semi­kolon) in dop­pel­ter Anführungsstriche geset­zt wer­den; damit wird ver­mieden, dass zusät­zliche (und damit auch falsche) Spal­ten beim Import der Dat­en gener­iert wer­den. Reine Zahlen (ohne jegliche Zusätze ein­schließlich Währungs­beze­ich­nun­gen) ste­hen ohne Gänse­füßchen in der Zeile.

▲ nach oben …

Semikolon als Trennzeichen

Für die Übung laden Sie erst ein­mal diese gepack­te zip-Datei und ent­pack­en sie. Es wird ein eigenes Verze­ich­nis PQ-Import erzeugt, damit Sie einen guten Überblick behal­ten. Neben ver­schiede­nen anderen Files find­en Sie dort 2 prinzip­iell iden­tis­che csv-Dateien: Mitglieder;des;18.Dt.Bundestages.csv und Mitglieder_des_18.Dt.Bundestages.csv. Die erst­ge­nan­nte Datei entspricht der deutschen Norm und die Spal­ten sind mit einem Semi­kolon getren­nt; die zweit­ge­nan­nte enthält das Kom­ma als Trennze­ichen für die einzel­nen Spal­ten. Eine weit­ere Beson­der­heit der zweit­en Datei wer­den Sie später noch ken­nen­ler­nen. Sie wer­den anschließend bei­de Files per Pow­er Query ein­le­sen und in ein­er Tabelle spe­ich­ern. Leg­en sie nun eine neue, leere Arbeitsmappe an. – Das Vorge­hen ist dann für bei­de Dateien grund­sät­zlich sehr ähn­lich. Und bedenken Sie, dass Sie die Files nicht per Dop­pelk­lick son­dern mith­il­fe des Pow­er Query öff­nen!

• Aktivieren Sie die Pow­er Query Funk­tion­al­ität entwed­er über das Menü gle­ichen Namens (Excel ²⁰¹⁰⁄₂₀₁₃) bzw. Dat­en.
• Neue Abfrage | Aus Datei | Aus csv
• Im Datei-Dia­log wählen Sie zu Beginn die Datei Mitglieder;des;18.Dt.Bundestages.csv. Importieren
• Es zeigt sich die Vorschau der zu importieren den Dat­en in einem Dia­log:

Pre­view der Semi­kolon-getren­nten csv-Datei

Das sieht ja schon erst ein­mal ganz gut aus. Das Kom­bi­na­tions­feld Dateiur­sprung ist für Sie immer dann von Bedeu­tung, wenn Son­derze­ichen wie beispiel­sweise die deutschen Umlaute oder das ß nicht kor­rekt dargestellt wer­den. In den meis­ten Fällen wird auf­grund der Dat­en die richtige Codierung erkan­nt, in Einzelfällen ist hier eine Änderung an dieser Stelle erfol­gver­sprechend. – Als Trennze­ichen wurde das Semi­kolon hier kor­rekt erkan­nt. Hier haben sie aber auch die Möglichkeit, „exo­tis­che” Trennze­ichen wie beispiel­sweise das Pipe-Sym­bol (|) einzugeben. So weit, so gut. Aber beim genauen hin­se­hen wer­den Sie vielle­icht erken­nen, dass dort ein Punkt nicht so ist wie erwartet: Die 1. Zeile der csv-Dat­en wurde nicht als Über­schrift ver­wen­det. Und Sie hat­ten auch keine Möglichkeit, das im Vor­wege auszuwählen. Aber gut, ein Klick auf die Schalt­fläche Bear­beit­en und die Dat­en wer­den in den Pow­er Query Edi­tor geladen. Hier stellt sich das ganze nun so dar:

Ansicht direkt nach dem Import der Dat­en

Wie bere­its in der Vorschau zu sehen war, sind die einzel­nen Spal­ten kor­rekt getren­nt; die Umlaute wer­den auch richtig dargestellt; und die Über­schriften der einzel­nen Spal­ten wur­den eigen­mächtig durch Pow­er Query erstellt. Auf den ersten Blick bietet sich diese Möglichkeit an, das zu kor­rigieren: Die Über­schriften von Hand ändern und dann die 1. Daten­zeile löschen. – Pow­er Query bietet Ihnen aber eine weitaus ele­gan­tere Möglichkeit: Im Reg­is­ter Start, Gruppe Trans­formieren ein­fach ein Klick auf den Menüpunkt Erste Zeile als Über­schriften ver­wen­den und die Liste ist per­fekt.

Jet­zt bleibt nur noch ein Klick auf Schließen & laden, um die Dat­en in ein neues Arbeits­blatt zu spe­ich­ern. Wollen Sie die Abfrage in das leere Blatt Tabelle1 schreiben, dann Klick­en Sie im Menü Start in der Gruppe Schließen nicht auf das Sym­bol (Icon) son­dern auf den Text darunter und wählen dort im Unter­menü bewusst den Punkt Schließen und laden in… und dort dann natür­lich Beste­hen­des Arbeits­blatt. Dann kon­trol­lieren Sie den Ziel-Ort, Laden und die Dat­en der Abfrage wer­den am gewün­scht­en Ort abgelegt.

Hin­weis: Sehen Sie sich ein­mal mit einem Edi­tor (Notepad, Notepad++, …) die csv-Datei an und acht­en Sie auf die Leerze­ichen nach den Semi­ko­la. – Betra­cht­en Sie sich das Endergeb­nis in der durch PQ erstell­ten Tabelle. Laden Sie nun noch ein­mal das gle­iche File auf bekan­ntem Wege (beispiel­sweise per Dop­pelk­lick) in Excel. Fällt Ihnen etwas auf? Not­falls wird Ihnen die Funk­tion LÄNGE() ein Licht aufge­hen lassen … 😛 

▲ nach oben …

Komma – getrennt

Bei den ersten Schrit­ten ist das Vorge­hen abso­lut iden­tisch wie bei der mit Semi­ko­la getren­nten csv-Datei. Nur dass sie hier die Datei Mitglieder_des_18.Dt.Bundestages.csv aus dem ent­pack­ten Ord­ner wählen. Sie kön­nen für diese Übung entwed­er ein neues Work­book anle­gen oder aber (sin­nvoller­weise) in der aktuellen Arbeitsmappe bleiben. Wie bere­its zuvor angedeutet, gibt es hier einen weit­eren aber entschei­den­den Unter­schied: Die 1. Zeile der csv-Dat­en enthält nicht die Spal­tenüber­schriften son­dern einen erk­lären­den Text, der nicht zum Datenbe­stand gehört.

Ver­schiedene Waren­wirtschaftssys­teme erzeu­gen beim Export als soge­nan­nte Excel Datei eine *.csv mit ein­er oder mehreren Zeilen eines Kopf­bere­ichs, der mit den zu ver­ar­beit­eten Dat­en rel­a­tiv wenig zu tun hat und in keinem Fall in die auszuw­er­tenden Dat­en mit über­nom­men wer­den soll. Hier stellt sich das nun so dar:

Die 1. Zeile enthält eine Kopfzeile, aber nicht die Über­schrift

Klick­en Sie hier auf das Sym­bol Zeilen ver­ringern | Zeilen ent­fer­nen | Erste Zeilen ent­fer­nen und tra­gen Sie dann bei Anzahl von Zeilen eine 1 ein, denn es soll ja nur (begin­nend mit der 1. Zeile) 1 Zeile ent­fer­nt wer­den. – Anschließend wer­den sie wieder den Menüpunkt Erste Zeile als Über­schriften ver­wen­den auswählen. Danach reicht ein Klick auf das Sym­bol Schließen & laden, denn die Abfrage soll ja in einem neuen Arbeits­blatt gespe­ichert wer­den.

▲ nach oben …

Nicht-Standard Trenner und Anführungsstriche

Den Pro­gram­mier­ern von ein­schlägi­gen Waren­wirtschaftssys­te­men, etc. ist zwis­chen­zeitlich dur­chaus bewusst, dass die Werte der Spal­ten in Gänse­füßchen einge­fasst wer­den müssen, wenn diese in irgen­dein­er Form das Trennze­ichen enthal­ten. Und darum spielt dieses For­mat auch mitunter (eigentlich: inzwis­chen wieder) eine Rolle. Ein klas­sis­ches Beispiel wäre, dass US-Dat­en numerische Werte mit Tausendertren­nern enthal­ten und diese ‑aus welchen Grün­den auch immer- als Text in der csv ste­hen. Vielfach ist es das Währungs-Sym­bol wie beispiel­sweise das $-Zeichen. Und da dieses ja keine Zahlen son­dern Text sind, wird der jew­eilige Wert in Anführungsze­ichen einge­fasst.  Das bedeutet: Der Spal­tentren­ner ist wegen der US-Dat­en ein Kom­ma, genau­so wie der Tausendertren­ner in den als Text gespe­icherten Zahlen. 💡 – Um sie nicht allzu sehr zu ver­wirren, habe ich die nun schon mehrfach genutzten Dat­en der Abge­ord­neten etwas abgeän­dert: Statt des Semi­kolons oder eines Kom­mas als Spal­tentren­ner habe ich den Binde­strich ver­wen­det, und da dieses Zeichen auch Bestandteil unter­schiedlich­er Dat­en ist (z.B. Zeile 21) sind die Inhalte jed­er Spalte (natür­lich) in Anführungsze­ichen einge­fasst:

Die Orig­i­nal-csv-Dat­en im Edi­tor

Es ist dur­chaus denkbar, dass sie Dateien in einem solchen For­mat bekom­men und in Pow­er Query auf­bere­it­en sollen. Es muss nicht der Binde­strich sein, meis­tens ist das (wie schon erwäh­nt) ein Kom­ma oder das Semi­kolon, welch­es für die Ver­wen­dung der Anführungsze­ichen ver­ant­wortlich zeich­net. Aber hier zählt das Prinzip und Sie erken­nen auch gle­ich, wie Sie mit unge­wohn­ten Trennze­ichen umge­hen kön­nen. Der Ablauf eines solchen Imports ist aber an entschei­den­den Stellen um einiges anders, als wie sie es bis hier­her ken­nen­gel­ernt haben…

Begin­nen Sie wie bish­er mit dem Import der csv-Datei. Für diese Übung ver­wen­den Sie dieses File: Mitglieder-des-18.Dt.Bundestages-’-’.csv als Objekt der Bear­beitung. Bere­its das erste Dialogfen­ster sieht ganz anders aus als gewohnt; nur eine Spalte und offen­sichtlich keine Möglichkeit, in Sachen Trennze­ichen irgend eine Änderung vorzunehmen:

Die Vorschau ist etwas gewöh­nungs­bedürftig

• Fol­glich bleibt nur die Möglichkeit, Bear­beit­en anzuk­lick­en.
• Es ist nicht wirk­lich über­raschend, dass sich nach dem Import in den Edi­tor prak­tisch nichts gegenüber dem Pre­view geän­dert hat:

Eine einzige Spalte ohne direk­tes Ange­bot zum split­ten …

• Auch wenn es „in den Fin­gern juckt“, die Über­schrift Column1 bleibt erst ein­mal beste­hen.
• Im Reg­is­ter Start Gruppe Trans­formieren Klick­en Sie auf das Sym­bol Spalte teilen.
• Selb­stver­ständlich wer­den Sie Nach Trennze­ichen teilen.
• Da der Binde­strich nicht als Trennze­ichen ange­boten wird, wählen Sie im Menüband den Punkt –Benutzerdefiniert– aus.
• Tra­gen Sie in das ger­ade einge­blendete leere Textfeld den Binde­strich - ein.
• Alle weit­eren Ein­stel­lun­gen belassen Sie so, wie sie sind; OK.

Die Spal­ten sind ja offen­sichtlich kor­rekt getren­nt wor­den. Was allerd­ings etwas irri­tieren kann: Statt 3 sind es 5 Spal­ten. Ursachen­forschung ist hier (aus­nahm­sweise) ein­mal nicht wichtig, aber sie soll­ten in jedem Fall kon­trol­lieren, ob dort nicht doch irgen­det­was sin­nvolles dort drin­nen ste­ht. Dazu Klick­en Sie ein­fach auf den Drop­down-Pfeil der Spal­ten Column1.4 und Column1.5 und sie wer­den erken­nen, dass dort auss­chließlich die Werte (NULL) und (LEER) enthal­ten sind. Als Fol­gerung kön­nen Sie diese bei­den Spal­ten einzeln nacheinan­der oder gemein­sam löschen.

Bleibt noch als let­zter Schritt, die Über­schriften durch den Inhalt der 1. Zeile zu erset­zen. Das Vorge­hen ken­nen Sie ja bere­its: In der Gruppe Trans­formieren auf Erste Zeile als Über­schrift ver­wen­den Klick­en und der Import der csv ist kom­plett. Was noch bleibt: Schließen & laden.

▲ nach oben …

Text-Dateien (*.txt)

Die Def­i­n­i­tion ein­er Datei mit der Endung *.txt ist in Bezug auf Tabellen und Tabel­lenkalku­la­tion durchgängig so, dass ein Tab­u­la­tor-Zeichen Tab die Spal­ten voneinan­der separi­ert. Man spricht all­ge­mein von Tab-getren­nten Dat­en. Und das unter­schei­det nun solch ein File (Mitglieder_des_18.Dt.Bundestages_Tab.txt) von ein­er „ganz nor­malen” csv-Datei? Nun ja, wenn sie die Dateien­dung und das Trennze­ichen außer acht lassen: nichts. Das Vorge­hen ist abso­lut iden­tisch mit dem bei ein­er Stan­dard-csv Datei. Und Pow­er Query erken­nt natür­lich auch das Trennze­ichen von alleine. Mehr gibt es dazu nicht auszuführen.

▲ nach oben …

Text-Dateien (*.prn)

Fast schon anti­quar­isch mutet diese Form der Text­dateien an. Aber es gibt ver­schiedene Sys­teme, welche diese Sys­tem­atik noch anwen­den. Sehen Sie sich ein Beispiel in der Datei Mit­glieder des 18.Dt.Bundestages Text.prn an:

Diese Text-Dateien haben immer eine feste Bre­ite je Spalte

Der Screen­shot hierüber wurde von einem Text-Edi­tor erstellt. Sie erken­nen, dass jede Spalte die gle­iche Bre­ite hat und der Ein­trag erforder­lichen­falls mit Leerze­ichen aufge­füllt wurde. Für sie bedeutet das, dass sie auf jeden Fall die Def­i­n­i­tion, die Struk­tur, die Länge der einzel­nen Spal­ten ken­nen müssen.

Wenn Sie diese Zahlen haben, brauchen Sie sich um nichts weit­eres zu küm­mern. Wenn das nicht der Fall ist, dann gibt es ver­schiedene Wege, um die Bre­ite der einzel­nen Spal­ten festzustellen. – Wenn sie solch eine Datei nur ein­ma­lig in Pow­er Query importieren wollen, dann fra­gen Sie sich, ob es den Aufwand wert ist. Ob nun ein- oder mehrma­lig, ich schlage ihnen fol­gende Vorge­hensweise vor:

• Laden Sie die *.prn in ein leeres Arbeits­blatt des Excel (über Datei öff­nen, Typ: Text­dateien)
• Es öffnet sich der Tex­tkon­vertierungs-Assis­tent und der stellt automa­tisch fest, dass die Spal­ten eine Feste Bre­ite haben
• Markieren Sie das Kästchen Die Dat­en haben Über­schriften, Weit­er >
• Die Spal­tenbre­it­en wer­den fast kor­rekt vorgegeben. Nur auf Posi­tion 84 soll­ten Sie den Spal­tenteil­er beispiel­sweise per Dop­pelk­lick darauf ent­fer­nen. Sie kön­nen sich jet­zt die jew­eili­gen Posi­tio­nen (42 und 75) notieren.
• Da alle Spal­ten dem Daten­for­mat Stan­dard entsprechen, kön­nen Sie jet­zt schon auf Fer­tig stellen Klick­en.

Für eine ein­ma­lige Aktion kön­nen Sie die nun erstellte Liste ein­fach über Aus Tabelle in Pow­er Query ein­le­sen. Wenn beispiel­sweise ihr Waren­wirtschaftssys­tem diese Form der tabel­lar­ischen Text-Aus­gabe stets ver­wen­det und nicht die erforder­lichen Spal­tenbre­it­en mit angibt, kön­nen Sie diese (ein­ma­lig) beispiel­sweise so ermit­teln:

• Schreiben Sie in (beispiel­sweise) Zelle F1 diese Formel: =MAX(LÄNGE(A:A)) und schließen Sie diese Formel mit StrgShiftEingabe ab.

In der Edi­tierzeile wird die Formel nun mit geschweiften Klam­mern { … } dargestellt, welche sie nicht mit eingeben dür­fen. Sie sind ein Zeichen dafür, dass es sich um eine soge­nan­nte Array- Formel han­delt. – Ziehen Sie nun per Aus­fül­lkästchen diese Formel noch 2 Spal­ten nach rechts und sie erhal­ten das Ergeb­nis der jew­eili­gen Spal­tenbre­ite. In diesem Fall sind das 42, 23 und noch ein­mal 23 Zeichen.

Jet­zt, wo sie nun die erforder­lichen Werte haben, kön­nen Sie mit dem Pow­er Query-Import begin­nen. Wie üblich gehen Sie in Excel über das Reg­is­ter Dat­en | Neue Abfrage | Aus Datei | Aus Text. Wech­seln Sie erforder­lichen­falls zu dem Ord­ner, wo die prn-Datei gespe­ichert ist, markieren Sie das File und Importieren.Das darauf­fol­gende Pre­view finde ich immer wieder faszinierend:

Gemäß dem ural­ten Satz „Augen zu und durch” ein­fach nur auf Bear­beit­en Klick­en. Und vom Grund­prinzip her scheint da ja zu min­destens einiges richtig zu sein. Wahrschein­lich sieht es bei Ihnen nun sehr ähn­lich aus wie bei mir:

Direkt nach dem Importieren sind es nur 2 Spal­ten …

Alle Vor­na­men haben am Ende das Aus­las­sungsze­ichen …, bei der Frak­tion ist das immer bei Bünd­nis 90/Die Grü­nen der Fall. – Klar ist, dass in Col­umn 1 2 Spal­ten zusam­menge­fasst sind und diese getren­nt wer­den müssen.

Erforder­lichen­falls markieren Sie diese Spalte und dann Spalte teilen. Hier wählen Sie dann Nach Anzahl von Zeichen und geben im Dialogfen­ster die Länge der 1. Spalte als Zahl ein: 42. Da die (kün­ftige) Spalte mit den Vor­na­men nicht auch so bre­it wer­den soll, markieren Sie bei Teilen den Options-But­ton Ein­mal, so weit links wie möglich. Nach einem OK stim­men zumin­destens die Spal­ten an sich. Jet­zt ste­ht in der 1. Zeile jed­er Spalte die kor­rek­ten Über­schrift. Darum ein Klick auf Erste Zeile als Über­schriften ver­wen­den und das ganze hat schon richtig Stil. Ich ermuntere Sie, trotz der angezeigten Aus­las­sungsze­ichen ein­fach auf Schließen & laden zu Klick­en. Und Voila, das Ergeb­nis überzeugt.

Damit sind jet­zt die wichti­gen Import­möglichkeit­en von textbasierten Dateien besprochen. Auch wenn streng genom­men Importe aus dem Web auch dazuge­hören wür­den, weil hier auch nur Text über­tra­gen wer­den, sind diese an anderen Stellen hier im Blog beschrieben wor­den.

▲ nach oben …

Bun­desweite  ✉ Schu­lun­gen ✉  durch unseren Spon­sor GMG Com­put­er-Con­sult­ing

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 …