Stolperfallen beim Textimport 17

Die "Intelligenz" von Excel beim Textimport austricksen
 

Oft muss man in Excel Listen weiterverarbeiten, die mit anderen Programmen erzeugt wurden und nur im Textformat vorliegen. Das ist im Normalfall auch kein großes Problem, da Excel z.B. Textdateien im CSV-Format (CSV = comma separated value; durch Komma getrennte Werte) wunderbar einlesen und ohne großes Nachfragen auch gleich schön in die richtigen Spalten aufteilen kann.

Zumindestes meistens.

Manchmal ist Excel jedoch ein wenig zu schlau und wandelt Postleitzahlen einfach in Werte um, und schneidet dabei etwaige führenden Nullen einfach ab. Oder aber es wird die Zeichencodierung der Quelldatei nicht richtig erkannt, was zu unschönen Ergebnissen bei Umlauten führen kann.

Mit etwas „Gewußt wie“ lässt sich dieses Problem aber leicht umgehen.

Und so geht’s:

Standard-Import einer Textdatei

Ich habe für unser Beispiel eine Postleitzahlendatei vorbereitet. Die Datei enthält alle deutschen Städte mit ihren Postleitzahlen und den dazugehörigen Bundesländern. Sie liegt praktischerweise im CSV-Format vor, die einzelen Spalten sind durch ein Semikolon getrennt.

So sieht die Original-Datei in einem Texteditor aus:

Quelldatei im CSV-Format

Quelldatei im CSV-Format

Über den normalen „Datei-Öffnen“-Dialog importiert Excel die Textdatei ohne Rückfragen, was in unserem Beispiel zu folgendem Ergebnis führt:

Fehlerhaft importierte CSV-Datei

Fehlerhaft importierte CSV-Datei

Das Problem mit den abgeschnittenen Nullen habe ich eingangs schon erwähnt. Dazu kommt, dass die Quelldatei in einem Unicode-Format vorliegt (UTF-8), Excel aber einfach das ANSI-Format annimmt und daher die Umlaute nicht richtig darstellt.

Korrekter Import einer Textdatei

Beim importieren einer CSV-Datei hat man keine Möglichkeit, in irgendeiner Weise einzugreifen. Liegt die Quelldatei aber mit der Dateiendung TXT vor, sieht es gleich ganz anders aus. Es reicht dazu, die vorhandene Datei von PLZ-Liste.csv in PLZ-Liste.txt umzubenennen.

Datei umbenennen in TXT

Datei umbenennen in TXT

Nun startet Excel beim Öffnen nämlich den sogenannten Textkonvertierungs-Assistenten:

Textkonvertierung Schritt 1

Textkonvertierung Schritt 1

Wie man in diesem Bild gut sieht, wählt Excel standardmäßig das Windows-ANSI-Format, was zu den Darstellungsproblemen bei den Umlauten führt. Wir ändern also stattdessen dass Unicode-Format (UTF-8) aus. Tipp: Wenn man nicht genau weiß, was das richtige Format ist, dann probiert man einfach ein paar der angebotenen Einträge aus. In der Vorschau sieht man sofort, ob die Umlaute richtig angezeigt werden:

Textkonvertierung Schritt 1 (angepasst)

Textkonvertierung Schritt 1 (angepasst)

Im zweiten Schritt wird nun das richtige Trennzeichen ausgewählt, in unserem Beispiel also das Semikolon. Damit wird in der Vorschau sofort die richtige Spalteneinteilung angezeigt.
Tipp: Wenn mal eine Datei mit einem exotischen Trennzeichen vorliegt, dann kann man im Feld „Andere:“ einfach dieses Zeichen eintragen.

Textkonvertierung Schritt 2

Textkonvertierung Schritt 2

Und im dritten und letzten Schritt müssen wir noch das Format für die PLZ-Spalte ändern, und zwar von „Standard“ auf „Text“:

Textkonvertierung Schritt 3

Textkonvertierung Schritt 3

Fertig ist der Import! Da wir die PLZ-Spalte als Textformat definiert haben, werden die Postleitzahlen nun auch mit der führenden Null angezeigt. Und die Umlaute stimmen jetzt ebenfalls.

Hier nochmal die beiden Versionen im Vergleich:

Vorher-Nachher-Vergleich

Vorher-Nachher-Vergleich

Das könnte dich auch interessieren:

No related posts found

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

17 Gedanken zu “Stolperfallen beim Textimport

  • Kroetchen73

    Hallo,
    wir haben das Problem, dass eine Tab-Stopp getrennte txt Datei in Excel mit den Anführungszeichen angezeigt wird. Diese sollen nachher natürlich nicht mehr angezeigt werden, sondern jeder einzelne Inhalt in einer separaten Spalte. Wie bekommen wir die Anführungszeichen weg und die Inhalte dann in den richtigen Spalten (nicht in jeder Zeile ist auch Inhalt für jede Spalte enthalten). Wir haben in dem Assistenten alle möglichen Variationen angeklickt, aber letztendlich ohne Erfolg. Die Anführungszeichen (insbesondere bei der Artikel-Nummer) gehen nicht weg…. Kann mir hier jemand weiter helfen?

    • Martin Weiß Autor des Beitrags

      Hallo,

      wenn der Datenkonvertierungsassistent wirklich keine vernünftige Einstellung ermöglicht, dann bleibt zum Schluss noch die ganz banale Funktion mit Suchen und Ersetzen.
      Das heißt, suchen nach Anführungszeichen, ersetzen durch (leeres Feld).

      Grüße,
      Martin

  • Holger Klees

    Hallo Martin,

    Deine Erläuterungen sind super.

    Ich habe in meinem Beruf ständig mit der Umwandlung von txt.- oder csv.-Dateien in xlsx.-Dateien zu tun.

    Was mich am meisten stört, ist, dass ich immer wieder unter der Rubrik Dateiursprung im Textkonertierungsassistenten
    die Voreinstellung 932: Japanisch (Shift-JIS) habe. Ich möchte, dass die Angabe Windows (ANSI) vorgeschlagen wird.

    Weißt Du, ob es eine Möglichkeit Voreinstellungen Konvertierungsparameter zu ändern?

    Liebe Grüße Holger

    • Martin Weiß Autor des Beitrags

      Hallo Holger,

      ein ähnlicher Fall wurde in der Microsoft-Community schon mal beschrieben. Hier ist der Link dazu. Vielleicht hilft das weiter.

      Schöne Grüße,
      Martin

  • William Bausler

    Die Tipps finde ich super, aber ich habe hier ein Problem, dass ich nicht finden kann.
    Eine Datei wird von einem Server in Excel-Format importiert. Hier müssen nun Bezüge
    hergestellt werden.
    Beispiel: ich habe einen Reiter „Stammdaten“ worin alle Daten vorhanden sind und ich
    möchte nun anhand einer übernommenen Textzeile einen SVERWEIS herstellen um die
    in den Stammdaten hinterlegten Angaben in einer eingefügten Spalte in die eingespielte
    Datei einzufügen. Aber Excel verweigert hier den Dienst und gibt mir keine Werte zurück,
    sondern schreibt die Formel wie ich sie eingegeben habe, z.Bsp:
    =SVERWEIS(D3;’Stammdaten‘!$d$2:$e$48;2;WAHR) und gibt mir nicht den Wert zurück.
    Woran kann das liegen ? Danke für eine eventuelle Reaktion. M f G

    • Martin Weiß Autor des Beitrags

      Hallo William,

      das liegt am Zellenformat. Oftmals werden externe Daten im Textformat importiert, was dann genau zu dem von Dir beschriebenen Problem führt. Stelle das Zellenformat in der betreffenden Zelle auf „Standard“ um und gib dann die Formel nochmal ein (oder gehe in der vorhandenen Formel mit F2 den Bearbeitungsmodus und drücke einfach nochmal die Eingabetaste).

      Schöne Grüße,
      Martin

  • Dietmar

    Hallo Martin,
    ich bin schier am Verzweifeln.
    Sitze seit 7 Stunden am Rechner und komme nicht weiter.
    Ich öffne eine CSV-Datei in Excel mit folgenden Merkmalen:

    „Name“,“Ereignis“,“Datum“
    „Müller“,“Termine“,“17.04.2019″
    „Müller, Peter“,“Termine“,“17.04.2019″

    Excel erkennt diese auch als feldgetrennte Tabelle und listet mir diese auch perfekt in 3 Spalten auf.
    Wenn jetzt versuche die bearbeitete Tabelle zurück ins CSV-Format zu exportieren (speichern),
    dann trennt Excel die einzelnen Felder durch ein Semikolon und nicht durch ein Komma.
    Die Anführungsstriche sind auch alle verschwunden.
    Das Datenprogramm, in welches die nun erstellte CSV-Datei, eingelesen werden soll,
    kann aber mit dieser neuen Formatierung nichts anfangen.
    Kann Excel diese Formatierung („Feld01″,“Feld02″,“Feld03“) überhaupt oder muss ich ganz andere Wege gehen.

    Mit freundlichen Grüssen Dietmar

    • Dietmar

      „Name“,“Ereignis“,“Datum“
      „Müller“,“Termine“,“17.04.2019″
      „Müller, Peter“,“Termine“,“17.04.2019″

      „Feld01″,“Feld02″,“Feld03“

      So sind die Anführungszeichen richtig.

        • Martin Weiß Autor des Beitrags

          Hallo Dietmar,

          ja, die Anführungszeichen werden hier in der Kommentarfunktion leider immer wieder ersetzt. Dieses Ärgernis ist mir bekannt, leider habe ich noch keine Lösung gefunden.

          Was Dein Excel/CSV-Problem angeht, sieht es leider ebenfalls schlecht aus: Excel bietet hierfür keine Einstellungen, um beim Export die Anführungszeichen für die einzelnen Felder zu setzen. Import klappt, Export nicht. Im Netz kursieren einige Makro-/VBA-Lösungen dafür, aber mit normalen Bordmitteln geht es leider nicht.

          Schöne Grüße,
          Martin

          • Dietmar

            Danke Dir Martin für Deine Antwort,

            ich habe diese Möglichkeiten mit VBA und co. aus dem Internet ausprobiert, ist mir dann aber doch zu umständlich mich jedesmal in die Materie wieder einzulesen bis ich zu einem funktionierenden Ergebnis komme. Deshalb habe ich jetzt mein Terminprogramm ad acta gelegt und mache jetzt alles über den Google-Kalender. Sind ja doch nicht die Menge an Daten die man persönlich hat. GK kommt auch besser mit dem Einlesen klar. Verlangt eben als Trennungen keine Anführungsstriche und das Komma, sondern eben nur das Komma und das bekommt man ja durch die Ersetzenfunktion zbsp.in Wordpad ohne Probleme hin. Zum Anderen habe ich iim zugehörigen Gadget für den PC auch eine permanente Erinnerungsfunktion, so dass ich auch bildlich meine anstehenden Termine sehe.

            Mit freundlichen Grüssen Dietmar

  • Viktor Schillinger

    Hallo Martin,
    ich kämpfe schon länger mit folgendem Problem:
    Für einen Anfänger-Cup der Bogenschützen habe ich eine Auswertung in Access 365 (2016) erstellt.
    Es werden 4 verschiedene Turniere durchgeführt: Indoor, Feld, 3D, Outdoor und das an verschiedenen Orten.
    Ich erhalte von dem jeweiligen Veranstalter (Verein) eine Excel-Datei .xlsx) mit den Daten der Schützen (Name, Verein, Bogenklasse…..)
    Bisher für Indoor und Feld.
    Es sind in beiden Excel-Dateien großteils die selben Schützen.
    Ich habe zuerst die Indoor-Datei importiert und dann das Turnier ausgewertet. Als ich dann die Daten für das Feld-Turnier erhalten habe, waren zwar zumeist die selben Namen vorhanden. Diese liessen sich aber nicht!!! mit den Namen aus der Indoor-Tabelle verknüpfen, obwohl sie gleich lauteten und auch im Hexadezimal-Code vollkommen übereinstimmten.
    Erst nachdem ich die Namen einzeln manuell aus der Indoor-Tabelle in die Feld-Tabelle kopiert habe – mit einer Abfrage war es ja nicht möglich – war jede Art von Verknüpfung möglich. Ich habe auch versucht, die Daten in eine neue Access-Datenbank zu importieren, leider mit dem gleichen Ergebnis.
    Da ich ja noch zwei weitere Turniere auszuwerten habe, wäre mir sehr geholfen, wenn ich wenigstens die Ursache für dieses Problem wüsste.
    Besten Dank im Voraus und liebe Grüße, Viktor

    • Martin Weiß Autor des Beitrags

      Hallo Viktor,

      eine Access-Hilfestellung kann ich hier natürlich nicht geben. Aber wenn der Abgleich in Excel nicht funktioniert, muss es irgendeinen Unterschied in den Daten geben. „Beliebt“ sind in diesem Zusammenhang Leerzeichen am Ende des Namens.

      Schöne Grüße,
      Martin

      • Viktor Schillinger

        Hallo Martin,

        vielen Dank für Deine Information.
        Es geht mir ja auch wirklich um die Basis der Daten und die wird jeweils in Excel erfasst.
        In Access erfolgt dann ja nur die Weiterverarbeitung.
        Das mit den Leerzeichen habe ich kontrolliert, daran kann es nicht liegen.
        Da die Daten aber von verschiedenen Absendern (jeweils ein Turnierveranstalter) kommen, vermute ich, dass es an unterschiedlichen Excel- bzw. Windows-Versionen liegen könnte.
        Wäre das möglich, daß trotz gleichem Hex-Code die Daten jener Herkunft durch meine Excel- bzw Windows-Version (Microsoft Excel für Office 365 MSO (16.0.11708.20000) 32 Bit und Windows 10 Pro) jene angelieferten Excel-Files unterschiedlich interpretiert werden?

        • Martin Weiß Autor des Beitrags

          Hallo Viktor,

          bisher habe ich noch nichts davon gehört, dass unterschiedliche Excel-Versionen hier unterschiedliche Daten liefern würden. Aber komplett ausschließen kann man so etwas natürlich nie. Daher bin ich leider selbst etwas ratlos…

          Tut mir leid, wenn ich hier nicht weiterhelfen kann. Vielleicht hat ja ein anderer Leser schon ähnliche Erfahrungen gemacht und kann mit einem Tipp helfen.

          Schöne Grüße,
          Martin