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.
Zumindest 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 „Gewusst 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 einzelnen Spalten sind durch ein Semikolon getrennt.
So sieht die Original-Datei in einem Texteditor aus:
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
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
Nun startet Excel beim Öffnen nämlich den sogenannten Textkonvertierungs-Assistenten:
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)
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
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
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
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.
Pingback: Ausflug in die Biologie: Zellteilung in Excel | Der Tabellen-Experte
Vielen Dank für diesen Tipp,
es hat geklappt !!!
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?
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
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
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
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
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
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
„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.
Dein Editor setzt jedesmal das erste Anführungszeichen nach unten wie bei Word.
Entschuldige.
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
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
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
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
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?
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
Hallo Martin,
ich lese dein Blog mit großen Interessen. Aber wenn ich es ausdrücken möchte, dann bekommen ich ein Problem.
Könntest du dein Text so formatieren, dass es ohne weiteres ausgedruckt werden kann ?
Danke.
Hallo Irina,
das kann gut möglich sein, dass der Text beim Drucken nicht optimal formatiert ist. Aber die Artikel sind einfach zum Lesen im Web konzipiert, daher kann ich dir hier leider nicht weiterhelfen.
Schöne Grüße,
Martin
Hallo Martin,
Danke für deine tolle Seiten und die wirklich nützlichen Inhalte.
Ich habe ein Problem mit dem ich nicht weiterkomme und wollte um Rat fragen:
Ich habe eine csv. Datei in der Zahlen teilweise als Potenzen drinstehen z.b. 2.123E-12. Beim importieren werden Fantasiezahlen draus gemacht. Wenn ich das als Text importiere steht zwar das richtige drin, aber man kann damit nicht rechnen, weil das eben Text ist. Und ich habe keine Idee wie ich das in die gemeinte Zahl umwandeln soll.
Grüße
Alex
Hallo Alex,
das mit den Fantasiezahlen wundert mich ein wenig, denn normalerweise werden Zahlen in der beschriebenen Schreibweise beim Import auch wieder als korrekte Zahlen eingelesen. Ich habe das gerade selbst nochmal ausprobiert. Die betreffende Spalte darf dabei nicht als Text formatiert werden.
2,123E-12 wird auch nach dem Import in dieser Schreibweise dargestellt. In der Zelle selbst sieht man dann die vollständige Zahl als 0,000000000002123
Schöne Grüße,
Martin
Kann es sein, dass es diesen Textkonvertierungs Assistenten so in Excel 2019 nicht mehr gibt.? Bin heute umgestiegen von Excel 2010 auf Excel 2019. In Excel 2010 funktioniert der Import einer Text Datei (Tabstopp getrennt) wie bei Dir oben beschrieben und in Excel 2019 wird alles in eine Spalte gepackt. Excel 2019 erkennt keine Tabstopps mehr und den Textkonvertierungs Assistenen gibt so auch nicht mehr.
Hallo Ralf,
doch, den Assistenten gibt es nach wie vor unverändert in den aktuellen Excel-Versionen. Wichtig ist nur, dass die Datei die Endung *.TXT hat, bei *.CSV frägt Excel tatsächlich nicht nach.
Aber du kannst den Textassistenten auch nachträglich aufrufen über das Menü Daten | Text in Spalten.
Schöne Grüße,
Martin
Hallo Herr Weiß,
ich habe schon lange ein Problem mit dem Excel-Import seit Version 365 bzw. teilweise schon mit Version 2010. Vorher hat eigentlich alles prima funktioniert. Ich brauche und brauchte die Funktion hauptsächlich zur Darstellung von Kontaktlisten mit Adressen und Geburtsdaten, Mitgliedsbeiträgen etc. und häufig muss ich daraus auch noch Serienbriefe/Dokumente erstellen, in denen dann die Daten logischerweise auch wieder korrekt dargestellt sein müssen.
Seit der Version 365 funktioniert aber irgendwie alles nicht mehr korrekt, so dass ich mir schon damit behelfen musste, den Datenimport mit LibreOffice zu machen und dann in Excel zu kopieren bzw. als Excel-Datei abzuspeichern – sehr umständlich und zeitaufwändig.
Nun habe ich Ihre Variante mit dem umwandeln der CSV-Datei in eine TXT-Datei versucht und mich schon gefreut. Wurde aber leider enttäuscht. Excel erkennt hier nun gar nicht mehr die Spaltentrennung. Es ist einfach nur Fließtext und ich kann als Trennzeichen anklicken was ich will, es klappt nicht obwohl die Kommatrennung wie in der CSV-Datei auch ja vorhanden ist.
Wenn ich die CSV-Datei in Excel einfach öffne habe ich genau das Problem, dass Sie beschreiben. Geht also gar nicht.
Ich gehe demnach so vor, dass ich in Excel über „Daten“ und dann aus Text/CSV die CSV-Datei auswähle (die Text-Datei funktioniert hier nämlich auch nicht). Somit kann ich zwar gut den Dateiursprung auf Unicode UTF-8 umstellen, aber leider die Spaltenformatierung nicht anpassen. Weshalb ich wieder Geburtstage, Hausnummern, PLZ und angegebene Telefonnummern mit einem + vorne komplett falsch angezeigt bekomme. Ich habe noch keine Lösung gefunden – es nervt aber, dass Microsoft in Excel bzw. generell offenbar so viele Automatismen im Hintergrund angepasst hat, dass man selbst nichts mehr umgehen kann. Auch die Serienbriefe funktionieren mehr schlecht als recht – aktuell hatte ich das Problem, dass gewisse Haus-Nummern (z.B. 5a) zwar in Excel korrekt angezeigt werden, im Serienbrief mir aber eine 0 angezeigt wird und das Auswerfen der Geburtsdaten funktioniert eigentlich gar nicht mehr. KI lässt grüßen!
Sorry für den langen Text, aber vielleicht haben Sie ja zumindest für den Datenimport noch eine weitere Idee hier auf Ihrer Seite? Ich kann mir zwar mit dem Weg über LibreOffice behelfen, finde das aber nicht elegant.
Ich danke Ihnen schon mal fürs Lesen und Antworten.
Elli Henkel
Hallo Frau Henkel,
das hört sich wirklich sehr gruselig an, wenn Sie Daten erst mit LibreOffice importieren müssen, um sie dann nach Excel zu kopieren. Denn auch Excel 365 sollte damit grundsätzlich überhaupt kein Problem haben.
Könnte es sein, dass die Einträge in Ihrer Textdatei von Anführungszeichen eingeschlossen sind? Dann könnte nämlich das beschriebene Problem auftreten. Falls das so ist, wäre die Lösung relativ einfach:
Anstatt die Daten nach der Vorschau direkt in Excel zu laden, können Sie sie mit der Schaltfläche „Daten transformieren“ in den Power Query-Editor laden. Dann rufen Sie dort das Menü „Transformieren | Spalte teilen | Nach Trennzeichen“ auf und wählen dort nochmal das Komma als Trennzeichen.
Vielleicht hilft das ja weiter.
Schöne Grüße,
Martin
Guten Abend Martin,
ich habe ein Excelproblem und hoffe, daß Du mir helfen kannst. Ich habe mit Hilfe des schönen Programms CSVed eine einzelne Spalte mit neuem Namen exportiert, indem ich alle anderen mit Hide und Delete entfernt hatte, und wieder mit Excel eingelesen. Sie sind auch alle da (30 000 Werte) in der Darstellng 0.409e-02. Ich will alle Werte aufsummieren, aber Excel erkennt oder verarbeitet sie nicht, es kommt immer nur 0,00 heraus. Ersetze ich einen Wert durch eine Handeingabe, so ist die richtige Summe in Feld A1 sofort da, d.h. das Programm arbeitet richtig. Seit Stunden probiere ich herum und finde keine Lösung. Was ist falsch? Was muss ich tun?
Mit Dank und Gruß
Hallo,
ich vermute einmal, dass es am Zahlenformat liegt. Manchmal werden importierte Daten nicht als Zahl erkannt, sondern als Text importiert, der nur wie eine Zahl aussieht. Du kannst die importierten Daten mit der Funktion =WERT() hinterher in echte Zahlen umwandeln.
Schöne Grüße,
Martin
Kann man das im Excel nicht abstellen, das er beim Textimport immer mit japanisch anfängt, statt mit dem naheliegendsten MS-DOS ANSI ? Gibts da nicht n Registry Hack, das man das immer wieder angeben muss ? Das dauert bei unserer Firmensoftware immer ewigkeiten, bis der dann MS-DOS Aanzeigt.
Das nervt total. Diese vielen sinnlosen Schritte, die man jedesmal beim Import machen muss. Was machen die da in Cupertino den ganzen Tag ? Japanische Mangas importieren oder was ? Manman. Die Software gibt es nun schon Jahrzehnte, aber so einen Pillepalle kriegen sie einfach nicht ausgemerzt. Die Software soll für mich da sein nicht umgekehrt ? Brauchen sich nicht wundern, das keiner mehr mit so was arbeiten will.
So, genug gemeckert. Sorry, aber das raubt einem soviel Zeit …
Ich schalt jetzt ab und geh die Sonne genießen. Ich wünsch Euch einen schönen Tag. Und nicht ärgern. Das versaut nur Euern Tag.
Ach Sorry Martin. Danke für Deine Seite. Finde immer wieder gute Sachen bei Dir.
Bleib dran, auch wenn so Meckerer wie ich manchmal ihren Frust hier ablassen 🙂
Wünsche Dir schon mal frohe Ostern
VG
Stephan
Hallo Stephan,
ist schon in Ordnung, manchmal muss der Frust halt einfach raus 😉
Ich wünsche Dir auch frohe Ostern!
Schöne Grüße,
Martin
Hallo,
vielleicht finde ich hier Hilfe, ich verzweifle nämlich grade. Wir hatten Systemumstellung auf Excel 2019. Ich arbeite im Controlling und muss für meinen Monatsbericht Daten aus einer TXT-Datei in Excel importieren. Trennzeichen ist ein | Das funktioniert soweit auch, aber ich habe nun das Problem, dass Excel nach dem Import mit den Daten nichts anfangen kann. Das wirkt, als würden die Daten (mal Text, mal Zahlen) dastehen, aber Excel wüsste nicht, dass da was steht. Eine Änderung des Dateityps (Standard als Grundeinstellung) bringt nichts. Wenn ich aber eine ein Kästchen klicke und dann auf Eingabe drücke, erkennt Excel die Daten wieder. Es handelt sich aber um 8 Spalten und Minimum 400 Zeilen. Wenn ich da jedes Kästchen anklicken muss, dreh ich durch.
Vor der Umstellung ging das innerhalb einer Minute über den Zahlenfilter.
Ich bin für jeden Tipp dankbar!
Hallo Matthias,
ja, das Problem kenne ich. Und wie du schon festgestellt hast, bringt die vermeintlich naheliegende Änderung des Formats nachträglich nichts bzw. erst nach Bestätigung jeder Eingabe.
Du hattest nicht geschrieben, wie du den Import vornimmst. Ich würde Dir dazu Power Query ans Herz legen. Dort kannst du dann auch genau für jede einzelne Spalte das Datenformat festlegen, bevor die Daten in Excel landen.
Schöne Grüße,
Martin
Hallo,
ich habe den Textkonvertierungsassistenten genauso verwendet, um Hausnummern korrekt angezeigt zu bekommen.
Bedauerlicherweise werden aber trotzdem noch Hausnummern als „9:00 am“ angezeigt.
UTF-8 stimmt, Zeile als Text konvertiert
und trotzdem nicht korrekt
Letzte Woche hat das aber noch geklappt. Was könnte das sein?
Gruß Michi