Dateien aus Excel können vielfach direkt in andere Anwendungen importiert werden. Manche Systeme/Anwendungen benötigen jedoch speziell aufbereitete Daten in bestimmten Speicherformaten um diese weiterverarbeiten oder nutzen zu können. An einem einfachen Beispiel zeige ich Ihnen in drei Teilen, wie Sie mit einem Excel-Makro Daten aus einer Exceltabelle in unterschiedlichen Speicherformaten erzeugen.
Teil 1: CSV/TXT-Format
Teil 2: HTML-Format
Teil 3: XML-Format
Für den Import/Export von Dateien werden grundsätzlich verschiedene Dateiformate genutzt. Die gängigsten hierfür genutzten Formate sind Textdateien, bei denen die Datensätze ohne die Informationen zur Formatierung der Zellwerte gespeichert werden und mit einem fest definierten Trennzeichen (z.B. Semikolon) oder einer festgelegten Breite die Abgrenzung der Spalten sicherstellen. Der Vorteil von Textdateien liegt insbesondere darin, dass diese Formate sehr leicht zu verarbeiten sind und zahlenmäßig von den meisten Anwendungen unterstützt werden. Das Risiko bei diesen Speichertypen: Die festgelegte Breite ist bei langen Inhalten nicht breit genug gewählt oder das Trennzeichen kommt auch innerhalb einer Zeichenkette im Zellwert vor. Zudem kann es zu Problemen kommen, wenn beim Import die Zellwerte aufgrund der fehlenden Angaben zur Formatierung von der Anwendung falsch interpretiert werden. Gängige Probleme in Excel sind Zahlenwerte mit mehr als 11 Ziffern und Zahlen, die mit einer Null beginnen. Diese Zellwerte müssen grundsätzlich als Text formatiert werden um Fehlinterpretationen in Excel zu verhindern. Beim Export in einer Textdatei geht die Information zur Formatierung jedoch verloren.
Für die Erstellung einer CSV oder TXT Datei steht am Ende des Beitrag eine Mustermappe zum Download bereit. Hier wird der erforderliche VBA-Code exemplarisch für eine Tabelle mit drei Spalten genutzt.
Der folgende VBA-Code erstellt eine Datei im Format „.txt“. Grundsätzlich ist dieser Programmcode identisch mit der Erstellung einer Datei im CSV-Format. Allerdings muss hier die Dateiendung angepasst werden. Beim Export im CSV-Format muss allerdings noch beachtet werden, dass die Spaltenüberschriften mit Bedacht gewählt werden. Steht zum Beispiel in Zelle A1 die Überschrift „ID“ meldet Excel beim Öffnen der Datei den Fehler:
Das Dateiformat und die Dateierweiterung von ‚dateiname.csv‘ passen nicht zueinander. Möglicherweise ist die Datei beschädigt oder nicht sicher. Sie sllten sie nicht öffnen, wenn Sie ihrer Quelle nicht vertrauen. Möchten Sie die Datei trotzdem öffnen?
Um den VBA-Code zu testen sind die Werte für die Variablen strPath und strDateiname ggf. zu ändern. Die Variable strPath beinhaltet den Speicherpfad in der die Textdatei erstellt werden soll. Der Ordner muss bereits existieren und mit einem abschließenden „\“ enden. In der Variable strDateiname wird der Dateiname und die Dateiendung der zu erstellenden Textdatei festgelegt. Achtung: Existiert die Datei im Ordner bereits, wird die Datei ohne Warnung überschrieben!
Sub TXT_erzeugen()
Dim strDateiname As String, strPath As String
Dim i As Long, lngZeile As Long
strPath = "C:\Dateien_erstellen\TXT\" 'Speicherpfad eintragen
strDateiname = "txt_dateiname.txt" 'Dateinamen mit Dateiendung eintragen
lngZeile = Range("A" & Rows.Count).End(xlUp).Row
Open strPath & strDateiname For Output As #1
For i = 1 To lngZeile
Print #1, Cells(i, 1).Value & ";" & Cells(i, 2).Value & ";" & Cells(i, 3).Value
Next i
Close #1
End Sub
Die Anzahl der Datensätze wird über die letzte genutzte Zelle in Spalte A ermittelt. Überschriften werden in Zeile 1 erwartet. Hat die Tabelle keine Überschriften, oder sollen anders beschriftete Überschriften erstellt werden, kann das Makro wie folgt angepasst werden.
For i = 1 To lngZeile
if i = 1 then
Print #1, "SpalteA;SpalteB;SpalteC"
else
Print #1, Cells(i, 1).Value & ";" & Cells(i, 2).Value & ";" & Cells(i, 3).Value
end if
Next i
Zur Vollständigkeit noch der VBA-Code für die Erstellung einer Datei im CSV-Format:
Sub CSV_erzeugen()
Dim strDateiname As String, strPath As String
Dim i As Long, lngZeile As Long
strPath = "C:\Dateien_erstellen\CSV\" 'Speicherpfad eintragen
strDateiname = "txt_dateiname.csv" 'Dateinamen mit Dateiendung eintragen
lngZeile = Range("A" & Rows.Count).End(xlUp).Row
Open strPath & strDateiname For Output As #1
For i = 1 To lngZeile
Print #1, Cells(i, 1).Value & ";" & Cells(i, 2).Value & ";" & Cells(i, 3).Value
Next i
Close #1
End Sub
Die vorherigen Beispiele haben zur Demonstation jeweils die ersten drei Spalten in die CSV- oder TXT-Datei übertragen. Sollen mehr Spalten übertragen werden, können die obigen Beispiele entweder manuell angepasst werden, oder über eine weitere Schleife neben der variablen Zeilenzahl auch eine variable Spaltenzahl definiert werden.
Am Beispiel der CSV-Erstellung sieht das dann folgendermaßen aus:
Sub CSV_erzeugen_Spaltenzahl_variabel()
Dim strDateiname As String, strPath As String, strZeile As String
Dim i As Long, lngZeile As Long, j As Long, lngSpalte As Long
strPath = "C:\Dateien_erstellen\CSV\" 'Speicherpfad eintragen
strDateiname = "csv_dateiname_variable_spaltenzahl.csv" 'Dateinamen mit Dateiendung eintragen
lngZeile = Range("A" & Rows.Count).End(xlUp).Row
lngSpalte = Cells(1, Columns.Count).End(xlToLeft).Column
Open strPath & strDateiname For Output As #1
For i = 1 To lngZeile
For j = 1 To lngSpalte
If j < lngSpalte Then
strZeile = strZeile & Cells(i, j).Value & ";"
Else
strZeile = strZeile & Cells(i, j).Value
End If
Next j
Print #1, strZeile
strZeile = ""
Next i
Close #1
End Sub
Die Mustermappe enthält beide Varianten, jeweils für das TXT- und CSV-Format.
Excel Mustermappe:
Weiterlesen…
Teil 2: HTML-Format
Teil 3: XML-Format
Guten Abend Herr Schade
Ich habe Ihren Code im Netz entdeckt und habe eine Frage, ich habe eine Arbeitsmappe mit diversen Tabellenblätter und möchte ein bestimmtes Blatt in meinem fall „Protokoll“ auslesen die Titel sind dabei fest in Zeile 1″Tabelle, Zelle, neuer Wert, Datum, Uhrzeit, Benutzer“, das heisst wenn etwas in der Mappe geändert wird wird das im Protokoll festgehalten, jetzt möchte ich das die *.txt erstellt wird dabei aus meiner cboKW an die Textdatei angehängt wird (Protokoll_2018_01) und anschliessen ab der Zeil2 und folgend der inhalt aus dem „Portokoll_Tabellenblatt“ entfernt wird.
Für ihre Idee wäre ich Ihnen sehr Dankbar, falls Sie meine Tabelle brauchen kann ich Ihnen diese per Mail übermitteln.
Freundliche Grüsse Thomas Testa
@Thomas Testa: In diesem Fall darf die Textdatei nicht neu erstellt bzw. überschrieben werden, sondern die vorhandene Datei muss zum Bearbeiten geöffnet werden. Ungefähr so:
Open strPath & strDateiname For Append As #1
Darüber hinaus sind größere Anpassungen am Makro erforderlich um die weiteren individuellen Vorgaben zu erfüllen. Wenn Sie hierfür Unterstützung benötigen kann ich Ihnen gerne ein individuelles Angebot für die Programmierung erstellen.
Guten Tag Herr Schade,
ich habe mehrere xml Dateien von denen ich die Daten in Excel einlesen muss. Pro Datei muss ich ein Tabellenblatt erstellen. Es wurde mir gesagt, dass es mit VBA einfacher gehen würde ich weiß nicht wie ich voran gehen muss.
@Sarina: Einfacher als einzeln von Hand? Also generell kann natürlich jede Aufgabe auch mit VBA gelöst werden. Ob das im Einzelfall zielführend ist, kommt darauf an, ob man die Lösung immer wieder benötigt oder nur einmalig. Ein Makro zu schreiben ist keine Kleinigkeit und rechnet sich regelmäßig erst dann, wenn das Makro mehrfach genutzt werden soll.
XML-Dateien mit VBA einlesen ist nicht gerade eine Aufgabe für Programmieranfänger. Ohne nähere Hintergründe zu Ihrem Vorhaben zu kennen, können Sie mit der Aufzeichnung von Makros den manuellen Einlesevorgang über die Standardfunktion in VBA umsetzen. Nachfolgend können Sie dann versuchen das aufgezeichnete Makro so anzupassen, dass Ihre weiteren Anforderungen untersützt werden. Weitere Unterstützung können Sie alternativ über die Beauftragung eines Auftragsmakros erhalten.
Hallo Marco,
super Makro, vielen Dank dafür. Eine kurze Frage, bei mehr als 38 Spalten (d.h & Cells(i, 39).Value) gibt dein Makro die Daten in der nachfolgenden Zeile und nicht mehr in einer Reihe aus. Gibt es hier eine Möglichkeit die Abfrage auf mehr als 38 Zeilen zu erweitern?
Danke.
@Micha: Eine Einschränkung auf 38 Spalten gibt es im VBA Makro nicht. Das Problem dürfte an den Zellinhalten liegen. Ich kenne jetzt zwar nicht deine Datei, aber ich würde mal tippen, dass in einer Zelle ein Zeilenumbruch oder sonstige Sonderzeichen enthalten sind, die den Zeilenumbruch auslösen.
Hallo Marco, das Schreiben der TXT funktioniert :-), vielen Dank. Die Datei wird in ANSI abgespeichert. Damit der Import ins andere Programm funktioniert, muss die TXT in der UTF-8-Codierung gespeichert sein. Wie kriegt man das hin? Hast Du da auch einen Codeschnipsel? Danke, Grüße, Karin
@Karin: Einfach mal bei Google suchen. Individuelle VBA-Anpassungen biete ich alternativ auch als kostenpflichtige Auftragsprogrammierung an.
Hallo,
wie funktioniert es, wenn in der Spalte/Zeile Uhrzeit steht. Welche Möglichkeit besteht darin, die Uhrzeit zu exportieren von Excel in txt.
Gruß
Hallo Marco,
vielen Dank erstmal für den Code. Ich habe das Problem, dass in folgendem Beispiel das Wort „Owner“ nebeneinander anstatt untereinander geschrieben wird.
Wenn meine letzte Zeile lngZeile=16 ist, heisst das, dass ich neun mal „Owner“ nebeneinander in der Zeile stehen habe.
Hast du eine Idee, was der Fehler sein könnte?
Und weiß du, wie ich das umsetzen kann, dass nur Zeilen, die in einer bestimmten Zelle „WAHR“ stehen haben, untereinander als .scv exportiert werden können?
Momentan gibt er mir ja alle ausgefüllten Zeilen aus.
For i = 7 To lngZeile
If i = 7 Then
Print #1, „Angemeldet_Von; Nummer; Kundennummer; Name“
Else
Print #1, „Owner“; „;“;
End If
Next i
Close #1
Gruß und vielen Dank
Hab den Code umgeschrieben, damit Tabellen jeder Größenordnung weggeschrieben werden:
Sub tab_in_txt()
Dim strDateiname As String, strPath As String
Dim i As Long, j As Long, lngZeile As Long, lngSpalte As Long, strText As String
strPath = „C:\Users\Roller\Downloads\cmt\datenaustausch\“ ‚Speicherpfad eintragen
strDateiname = „txt_dateiname.txt“ ‚Dateinamen mit Dateiendung eintragen
lngZeile = Application.CountA(Range(„a:a“))
lngSpalte = Application.CountA(Range(„1:1“))
Open strPath & strDateiname For Output As #1
For i = 1 To lngZeile
For j = 1 To lngSpalte
strText = strText & Cells(i, j).Value & „;“
Next
Print #1, strText
strText = „“
Next
Close #1
End Sub
Hallo,
gibt es eine Möglichkeit um bei einer erstellten CSV-Datei noch nachträglich an erster Stelle einen festen Text zu erfassen? Problem, ich erstelle eine CSV-Datei für einen Import. In der ersten Zeile muss die aktuelle Versionsnummer stehen, sonst nix. Der Import umfasst mehrere Spalten, wenn ich diese in Excel bearbeite und als CSV speichere, wird in der ersten Zeilen die Versionsnummer um so viele Semikolon ergänzt, wie Spalten vorhanden sind. Diese Semikolon müssten also weg.
Da die Versionsnummer aber fest ist, dachte ich, es ist am einfachsten, nach dem Erstellen der CSV-Datei die Versionsnummer als erste Zeile einzutragen.
Haben Sie evtl. Ideen dafür? Danke vorab
Viele Grüße
Norbert K.
@Norbert K.: Die Versionsnummer soll also nur in Zeile 1 stehen. Die Importdaten stehen dann ab Zeile 2? In diesem Fall würde ich die CSV nicht in Excel editieren, sondern mit einem Texteditor z.B. Notepad++.
Hallo Marco,
vielen Dank für das Makro. In der Version des CSV-Exports erscheinen bei mir leider nur die ersten drei Spalten. Wie können alle Spalten der Tabelle übernommen werden?
Eine angepasste Version des Codes „Sub CSV_erzeugen()“ würde mir sehr helfen.
Vielen Dank vorab.
@Torsten: Bei dem Makro handelt es sich um ein Beispiel, das demonstriert, wie eine CSV aus Excel erzeugt werden kann. Um mehr als drei Spalten in die CSV-Datei zu schreiben gibt es zwei Möglichkeiten. Entweder man erweitert die Programmzeile um die weiteren Spalten:
Je weiterer Spalte muss dann dieser Teil hinten angehängt werden und entsprechend die Spaltennummer hochgezählt werden
Gibt es eine Vielzahl von Spalten oder ist die Spaltenzahl wechselnd ist es dagegen sinnvoll die letzte genutzte Spalte zu ermitteln und dann eine Schleife einzubauen.
For i = 1 To lngZeile For j = 1 To lngSpalte If j < lngSpalte Then strZeile = strZeile & Cells(i, j).Value & ";" Else strZeile = strZeile & Cells(i, j).Value End If Next j Print #1, strZeile strZeile = "" Next iZusätzlich müssen dann noch die Variablen definiert werden und der Variable lngSpalte die letzte verwendete Spalte übergeben werden. Gehen wir davon aus, dass in Zeile 1 die Überschriften stehen und alle verwendeten Spalten eine Überschrift haben, dann kann das z.B. so aussehen:
Vielen Dank!
Das hilft mir so weiter.
EMPFEHLENSWERT!!!
Hi Marco,
Ich würde gerne aus einer Excel Datei in der viele verschiedene Informationen anliegen, eine txt Datei erstellen und aus dieser txt Datei soll eine json Datei erstellt werden.
Meine erste Frage wäre schreibe ich die von Ihnen hinterlegten Programmcode in einen Makro und der übernimmt meine Excel dann in ein txt Format ?
Die Datei enthält nämlich mehrere Zeilen und Spalten und bisher hat es nicht ganz funktioniert bei mir.
@Monte Ulrich: Ich verstehe den Umweg über eine TXT-Datei nicht. Wenn am Ende eine Datei im JSON-Format benötigt wird, sollte das Makro aus den Informationen in der Exceldatei direkt eine JSON-Datei erstellen.
In dem Beitrag ist am Ende des Beitrags eine Mustermappe verlinkt. Darin können Sie nachvollziehen, wie aus der Tabelle eine TXT-/CSV-Datei erstellt wird. Das Makro-Beispiel ist in der Datei bereits integriert. Aber wie gesagt, aus meiner Sicht macht es keinen Sinn die Daten erst in eine TXT-Datei zu schreiben um Sie dann nochmals weiterzuverarbeiten.
Hallo Herr Schade,
ab der 10. Spalte erhalte ich in dem Makro immer einen typenfehler und weiß nicht iwe ich den behben soll, da ja in Spalte 10 bis 12 tatsächlich daten stehen, die in die txt Datei sollen.
Mein Code sieht wie folgt aus:
‚Daten in txt schreiben
Dim strDateiname As String, strPath As String
Dim i As Long, lngZeile As Long
strPath = „J:\ITST\Tabellen\Auswertungen\_Listenaufbereitung\“ ‚Speicherpfad eintragen
strDateiname = „#_FATAB.txt“ ‚Ziel Dateinamen mit Dateiendung eintragen
lngZeile = Range(„A“ & Rows.Count).End(xlUp).Row
Open strPath & strDateiname For Output As #1
For i = 1 To lngZeile
Print #1, Cells(i, 1).Value & „;“ & Cells(i, 2).Value & „;“ & Cells(i, 3).Value & „;“ & Cells(i, 4).Value & „;“ & Cells(i, 5).Value & „;“ & Cells(i, 6).Value & „;“ & Cells(i, 7).Value & „;“ & Cells(i, 8).Value & „;“ & Cells(i, 9).Value & „;“ & Cells(i, 10).Value & „;“ & Cells(i, 11).Value & „;“ & Cells(i, 12).Value
Next i
Close #1
@Annika: Das klingt so, als wenn in Spalte 10 ein ungültiger Wert steht, der den Abbruch verursacht. Das Makro an sich funktioniert.