Jemand verlangt von Ihnen eine #.csv-Datei in #UTF-8 ohne #BOM? Spricht der Suaheli? Nein – das ist ein legitimes Anliegen … (Comma Separated Values in der Datei, in der Zeichencodierung UTF-8, und ohne Signatur). Und wie lösen wir das?
Nicht ohne #VBA – leider. Es gibt beim DATEI > Speichern Unter > Tools > Weboptionen keine UTF-8 ohne BOM –Codierung. Der Ansatz ist zunächst derselbe wie vorgestern beschrieben – Sie erzeugen ein Hilfstabellenblatt, das die Daten exportfertig in einer Spalte beinhaltet, die Trennzeichen und Hochkommata müssen Sie selbst hineinbasteln.
Den Export erledigt jetzt keine Excel-Funktionalität, sondern ein Codeschnipsel:
Sub Export2CSVUTF8ohneBOM()
Dim cSh As Worksheet
Dim myRow As Long
Dim UTFStream As Object
Dim BinaryStream As Object
Set cSh = ThisWorkbook.Worksheets("Export CSV")
On Error GoTo myFault
Set UTFStream = CreateObject("adodb.stream") 'Die Ausgabe erfolgt mit ADODb Stream
UTFStream.Type = adTypeText
UTFStream.Mode = adModeReadWrite
UTFStream.Charset = "UTF-8" 'hier wird die Codierung vorgegeben
UTFStream.LineSeparator = adLF
UTFStream.Open
For myRow = 1 To cSh.Range("A1").End(xlDown).Row
If Not IsEmpty(cSh.Range("A" & myRow)) Then
UTFStream.WriteText (cSh.Cells(myRow, 1)), adWriteLine
End If
Next myRow
'Ergebnis ist jetzt UTF-8 mit BOM - es gibt keine Einstellung für "ohne BOM", daher kommt jetzt der ultimative Trick
myRow = -1
'wir erstellen einen zweiten stream
Set BinaryStream = CreateObject("adodb.stream")
BinaryStream.Type = adTypeBinary
BinaryStream.Mode = adModeReadWrite
BinaryStream.Open
UTFStream.Position = 3 'überspringen BOM (die ersten 3 Zeichen)
UTFStream.CopyTo BinaryStream 'jetzt wird der ursprünglich erzeugte Stream kopiert, aber BOM fällt weg
'den ersten Stream braucht jetzt kein Mensch mehr
UTFStream.Flush
UTFStream.Close 'Ergebnis speichern
BinaryStream.SaveToFile ThisWorkbook.Path & "\Datei_" & Format(Now, "yyyymmdd_hhnnss") & ".csv", adSaveCreateOverWrite 'Speichern unter dem hier erzeugten Dateinamen
BinaryStream.Flush
BinaryStream.Close
Set UTFStream = Nothing
Set BinaryStream = Nothing
MsgBox "Export finished to file " & ThisWorkbook.Path & "\Events_" & Format(Now, "yyyymmdd_hhnnss") & ".csv", vbOKOnly, "Export"
Set cSh = Nothing
Exit Sub
myFault:
If myRow = -1 Then
MsgBox "Problems on saving the file - make sure that the ThisWorkbook.Path is a local path (not OneDrive or SharePoint).", vbOKOnly, "Save problem"
Else
MsgBox Err.Number & "-" & Err.Description & vbCrLf & " when exporting row #" & myRow & "." & vbCrLf _
& "Please correct data and try again.", vbOKOnly, "Error"
Resume Next
End If
End Sub
Die Microsoft ActiveX DataObjects Library muss in den Extras > Verweisen vorhanden sein, sonst wird das nichts.
Ob das wirklich richtig arbeitet sehen Sie, wenn Sie sich das Ergebnis z.B. mit Notepad++ anschauen:
Ich geb’s zu, das ist keine alltägliche Anforderung. Aber auch keine völlig abwegige – Tools, die Daten importieren, haben immer zwei Möglichkeiten: sie sind sehr komplex und können mit allen möglichen Formaten gut umgehen (dann sind sie teuer), oder sie geben genau vor, womit sie arbeiten wollen und können, und reagieren pikiert, wenn der Input auch nur ein bisschen anders ist als erwartet (dann sind sie weitaus kostengünstiger, und im Fall des Falles haben Sie ein bisschen Extraaufwand).
Woche beendet – angenehme Ruhetage 🙂
katharinakanns
Soprani Software 
Leider bekomme ich bei der Zeile
„Set cSh = ThisWorkbook.Worksheets(„Export CSV“)“
immer die Fehlermeldung „ThisWorkbook.Worksheets(„Export CSV“)“=
LikeLike
Wie heißt denn das Arbeitsblatt, das exportiert werden muss? Der String -Export CSV- muss mit dem Namen des Blattes ersetzt werden.
Lg
Katharina
LikeLike
Hallo katharinakanns,
Diese Seite gefunden zu haben, ist ein wahrer Segen 🙂
Ich war schon am verzweifelt, weil ich es zwar manuell geschafft habe, dass Excel 365 UTF-8mitBOM speichert, aber nicht per VBA.
Danke für diese Prozedur – funktioniert wunderbar.
/Willy
LikeLike
Ich bekomme eine Fehlermeldung hier:
If Not IsEmpty(dSh.Range(SelCol & myRow)) Then
Auch soll ich die nummer.Format „.“ statt „,“ specihern.
Wie kann ich das lösen?
LikeLike
Hallo,
da war noch ein Fehler drin, sorry. Ist jetzt korrigiert.
Es gibt einen Post von 2 Tagen davor; in dem werden die nötigen Vorbereitungen für diesen Schritt beschrieben – Zahlenformat, Trennzeichen, …
Herzliche Grüße
Katharina
LikeLike
Hallo Katharina,
leider funktioniert dein VBA-Code nicht. Ich habe zwei Dinge entdeckt und würde mich freuen, wenn du mir beim Fehler finden hilfst. „cSh“ und eine Zeile drunter „dSh“. Ich vermute, dass dir hier ein Schreibfehler unterlaufen ist.
Allerdings geht es dann weiter: SelCol ist leer und läuft damit in den Fehler 1004.
Kannst du eventuell dazu etwas berichten? Vielen Dank!
LikeLike
Hallo, Patrick,
danke für das Finden des Tippfehlers – ist bereits korrigiert.
Und das SelCol ist tatsächlich von einer konkreten Lösung „hängengeblieben“ – ist jetzt weg. Ich habe auch noch ein bisschen was bei der Fehlermeldung ergänzt, damit es keine Missverständnisse geben kann bezüglich etwaiger weiterer Fehlerquellen.
Also: Großes DANKE für das Feedback zu meinen Schlampereien; ich hoffe, jetzt funktioniert es auch bei dir wie erwartet, mein Test passt.
Herzliche Grüße
Katharina
LikeLike
Hallo Katharina,
es freut mich, dass du mir geantwortet und die Fehler berichtigt hast. Ich habe den Code nun erneut bei mir als Modul hinzugefügt und den Arbeitsblattnamen geändert. Wenn ich nun das Modul ausführe, so wird eine CSV generiert und abgespeichert, aber allerdings nur mit den Werten der ersten Spalte. Woran kann es liegen, dass bei mir nur die erste Spalte in der CSV landet? Was mache ich falsch?
Vielen Dank für deine Hilfe!
Viele Grüße
Patrick
LikeLike
Hallo, Patrick,
das liegt daran:
UTFStream.WriteText (cSh.Cells(myRow, 1)), adWriteLine
Die Vorbereitung der Datei beschreibe ich in einem Blogbeitrag davor („vorgestern“) – da wird schon eine Hilfsdatei mit den richtigen „Textwürsten“ in Spalte 1 vorbereitet.
Hilft das?
Herzliche Grüße
Katharina
LikeLike
Achso verstehe! Durch die Google-Suche habe ich bisher immer nur diesen einen Thread gesehen. Man muss also all seine Daten zunächst mit VERKETTEN oder TEXTVERKETTEN in der ersten Spalte zusammenfassen. Das macht dann natürlich Sinn. Ich habe die ganze Zeit die zweite for-Schleife zum Durchiterieren der Spalten gesucht. 😀
Ok dann halt so. Super, Danke!
Viele Grüße!
LikeGefällt 1 Person
Oder man speichert die Datei einfach mit LibreOffice 😉
Oder nutzt xlsx2csv.
Bin allerdings Linux-User, wo UTF-8 überall Standard ist. Warum auf Windows als de facto einzigem Betriebssystem noch immer nicht systemweit auf UTF-8 umgestiegen worden ist, verstehe ich nicht. Wer will locale-abhängig kodierte Dateien?
Die Auswahl der Codierung in den „Weboptionen“ (warum Web?) bewirkt übrigens gar nichts, getestet.
Als Systementwickler/Datenbank-Mensch ist das übrigens absolut üblich. Ganz im Gegenteil: Ich hatte noch nie den Bedarf nach Dateien mit BOM.
Sehr mühsam, dass Excel das nicht kann, somit muss man immer Daten von Enduser:inne:n aufbereiten, bevor man sie weiterverarbeiten kann.
LikeLike