.CSV in UTF-8 ohne BOM aus Excel

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 BOMCodierung. 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(dSh.Range(SelCol & 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    '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:
    MsgBox Err.Number & "-" & Err.Description & vbCrLf & " when exporting row #" & myRow & "." & vbCrLf _
    & "Please correct data and try again.", vbOKOnly, "Error"
    Resume Next
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:
XTipp UTF-8 03.png
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 🙂
Veröffentlicht unter Access, Excel, VBA | Verschlagwortet mit , , , , , , , , , , , | Kommentar hinterlassen

.CSV-Datei im Format #UTF-8 speichern

Die aus #Excel im #csv-Format erzeugte Datei soll auch noch eine bestimmte Codierung haben? Puh, das wird ja von Tag zu Tag kniffliger! (Aber wenn die Applikation, die die Daten dann importieren soll, so heikel ist …).

Bitte sehr:

Variante 1: Sie arbeiten mit „Speichern unter“ (und akzeptieren ggf. den Beistrich als Trennzeichen, bzw. stellen halt in Windows die Region um). Dann können Sie die Codierung unter DATEI > Speichern unter > Tools > Weboptionen einstellen.xtipp-utf-8-01

Im Register Codierung wählen Sie unter Dokument speichern als Unicode (UTF-8) aus – fertig.

xtipp-utf-8-02

Natürlich geht auch jede andere Codierung.

Morgen geht’s in den Endspurt zu dem Thema.

Veröffentlicht unter Excel | Verschlagwortet mit , , , , | Kommentar hinterlassen

.csv mit flexiblem #Trennzeichen

Sie wollen mit Excel eine .csv-Datei erzeugen, in der die Felder durch Strichpunkte getrennt sind. Sie wollen NICHT vor jedem Export die Windows-Einstellungen verändern (oder Sie dürfen das gar nicht)? Dann müssen wir tricksen:

Legen Sie in Ihrer Arbeitsmappe ein zweites Blatt an. Hier werden wir die Daten aus Ihrer Liste zusammenhängen. In etwas älteren Excel-Versionen geht das wie folgt:

=Namensliste2!A2 & par_TZ & Namensliste2!B2 & par_TZ & Namensliste2!C2 & par_TZ & „“““ & Namensliste2!D2 & „“““

oder

=VERKETTEN(Namensliste2!A2;par_TZ;Namensliste2!B2;par_TZ;Namensliste2!C2;par_TZ;““““; Namensliste2!D2; „“““)

Damit hängen Sie die Feldinhalte aneinander und fügen dazwischen immer ein Trennzeichen ein – damit das schön flexibel ist, hab ich auf dem ersten Blatt eine Zelle par_TZ benannt, und da schreibe ich nun je nach gewünschtem Trennzeichen einen Beistrich hinein oder einen Strichpunkt oder meinetwegen auch ein anderes Zeichen. Schauen Sie sich bitte das Ende der beiden Formeln genau an: die letzten drei Partikel sind „“““, die Zelle mit den Kindernamen und wieder „“““. Dieses „“““ ergibt ein doppeltes Hochkomma – das muss so geschrieben werden, damit Excel sich auskennt. (Darauf, dass einem da die Augen wehtun, kann die Technik keine Rücksicht nehmen, leider.)

Haben Sie eine schöne neue Version von Excel, dann steht Ihnen die Funktion TEXTVERKETTEN(Trennzeichen;LeerIgnorieren;Text;Text2;…) zur Verfügung. Damit schreiben Sie einfach

=TEXTVERKETTEN(par_TZ;FALSCH;Namensliste2!A2:Namensliste2!C2;““““&Namensliste2!D2&““““)

Damit werden genauso alle Bestandteile aneinandergehängt. Probieren Sie die für Ihre Situation am besten passende Formel aus; überlegen Sie, wo und wann Sie am besten die doppelten Hochkommata einbringen.

Das Ergebnis ist jetzt jedenfalls eine Liste in einer einzigen Spalte (wenn nicht, dann müssen Sie noch etwas an Ihren Formeln verändern). Speichern Sie sich die Arbeitsmappe mit den Formeln schnell im normalen Format, damit nichts verlorengeht. Und wenn Sie nun von dem neuen Blatt ausgehend die Datei mit DATEI > Speichern unter als CSV (Trennzeichen-getrennt) speichern, dann passt alles. Schließen Sie sie und schauen Sie mit einem Texteditor hinein!

csv07.PNG

Veröffentlicht unter Uncategorized | Verschlagwortet mit , , , , | 1 Kommentar

#csv mit #Komma als #Trennzeichen

Wenn Sie eine #Datei in #Excel als .csv (Comma Separated Values) speichern, dann kann es passieren, dass als Trennzeichen keine Beistriche sondern Strichpunkte verwendet werden – siehe gestern. Das liegt am babylonischen Vielsprachigkeits-Fluch – die deutschen Windows- und Excel-Versionen sagen: auf Deutsch ist das Trennzeichen der Strichpunkt! Weil wir ja den Beistrich als Dezimaltrennzeichen verwenden. Ist gar nicht so blöd, allerdings gibt es Schnittstellen, die sagen, wenn sie die Daten nicht mit Beistrich getrennt bekommen, dann funktioniert der Import Ihrer Daten nicht, und basta.

Was tun?

Vor dem Speichern unter was Arges tun: Windows > Systemsteuerung > Zeit, Sprache und Region > Region und Sprache > Datum, Uhrzeit oder Zahlenformat ändern > Formate > Format auf z.B. Englisch (Großbritannien) umstellen. Unter Weitere Einstellungen sehen Sie das Resultat (vorher / nachher):

Sehen Sie den Unterschied bei Listentrennzeichen? Jetzt können Sie die Datei mit DATEI > Speichern unter im Format CSV (Trennzeichen-getrennt) speichern, und alles ist gut. Auch die doppelten Hochkommata, die uns gestern gefehlt haben, sind nun da.

csv06

Vergessen Sie nicht, Windows wieder zurückzustellen.

Das nervt? Na dann freuen Sie sich auf morgen – es gibt auch Wege (allerdings keine einfachen), mit denen Sie die Umstellerei nicht brauchen.

 

Veröffentlicht unter Excel, Windows | Verschlagwortet mit , , , , , , , , | Kommentar hinterlassen

Erzeugen Sie eine #csv Datei

Jemand verlangt von Ihnen Daten als #csv-Datei? Die können Sie ganz einfach mit #Excel erzeugen.

Aber zuerst die Theorie: was heißt .csv eigentlich und was ist das? Heißen tut es Comma Separated Values, also durch Kommata (Beistriche) getrennte Werte. Und das bedeutet, dass Sie Daten datensatzweise (Zeile) und feldweise (Spalte) so liefern, dass die Spalten durch Beistriche getrennt werden. Sonst tut sich nichts, die Daten sind einfach Text; in csv-Dateien gibt es keine Gitternetzlinien, sichtbaren Spaltengrenzen, … Wenn Sie so eine .csv-Datei mit einem Texteditor aufmachen, dann sieht sie z.B. so aus:

Nachname,Vorname,Alter
Schwarzer,Katharina,49
Niedlich,Cäcilie,35
Glein,Traude-Elisabeth,55
Martin,Franz,44

Hier ist die erste Zeile der Indikator für den Feldnamen. Und dann müssen Sie sich durchzählen: alles bis zum ersten Beistrich ist der Nachname, alles bis zum zweiten Beistrich ist der Vorname, dann kommt das Alter.

Können Beistriche in den Daten vorkommen, dann werden Texte mit doppelten Hochkommata zusammengehalten:

Nachname,Vorname,Alter, Kinder
Schwarzer,Katharina,49,“Leonie, Seppl, Moira, Pippa“
Niedlich,Cäcilie,35,““
Glein,Traude-Elisabeth,55,“Karina-Sophia,Friedensreich-Elmer“
Martin,Franz,44,““

So. Und wenn Sie eine ganz normale Liste in Excel haben, und jemand sagt, machen Sie mir doch daraus bitte eine .csv-Datei? Dann gehen Sie auf DATEI > Speichern unter und wählen beim Dateityp CSV (Trennzeichen-getrennt).

csv01

Natürlich kann nur das aktuelle Blatt gespeichert werden; Excel wird Ihnen zwar weiterhin noch Ihre Arbeitsmappe anzeigen, als wäre nichts geschehen, aber weitere Blätter, Formatierungen, … gehen nun verloren, weil die Datei keine Ahnung hat, wie sie das speichern soll, sie ist ja nur Text mit Trennzeichen. Excel weist Sie darauf hin:

csv02

Schon fertig. Mit einem Wermutstropfen: wenn Sie die Datei nun mit einem Texteditor aufmachen, so sieht sie gut aus, aber nicht perfekt. Die doppelten Hochkommata fehlen. Und da sind Strichpunkte statt der Beistriche! Weil ich mit einer deutschen Version von Excel gearbeitet habe … in vielen Fällen ist das kein Drama, und Sie sind fertig. Die anderen Situationen gehen wir morgen an.

csv03

Veröffentlicht unter Excel | Verschlagwortet mit , , , , , , | 1 Kommentar

#neu in #PowerPoint und #Excel für #MacBookPro: #TouchBar wird unterstützt

#neu in #PowerPoint und #Excel für #MacBookPro: #TouchBar wird unterstützt – mit dem neuesten Update kann’s losgehen!

Veröffentlicht unter Excel, Mac, PowerPoint | Verschlagwortet mit , , , | Kommentar hinterlassen

#Duplikate in #mehreren #Zellen #markieren

Die Lösungen der letzten beiden Tage zum #Markieren von #mehrfachen Einträgen in einer #Excelliste benötigten eine Zusatzspalte. Heute zeig ich Ihnen eine Variante, die ohne zusätzliche Spalte auskommt:

XTipp doppelte mehrere Zellen 4.png

Markieren Sie alle Daten (hier also H2:J7), und öffnen Sie mit START > Formatvorlagen > Bedingte Formatierung > Neue Regel… den Formatierungsregel-Dialog. Bei Regeltyp auswählen brauchen wir Formel zur Ermittlung der zu formatierenden Zellen verwenden. Und bei Werte formatieren, für die diese Formel wahr ist, geben wir nun folgende Formel ein:

=ZÄHLENWENNS($H$2:$H$7;$H2;$I$2:$I$7;$I2;$J$2:$J$7;$J2)>1

Da geschieht nun Folgendes: mit ZÄHLENWENNS(Bereich; Kriterium; Bereich2; Kriterium2; Bereich3; Kriterium3) wird herausgefunden, wie oft Kriterium in Bereich vorkommt und in derselben Zeile Kriterium2 in Bereich2 steht und in derselben Zeile Kriterium3 in Bereich3 steht. Wir zählen also für die Formatierung von H2 bis J2 die Anzahl der Duplikate von H2, I2 und J2 im gesamten Bereich. Und wenn das mehr als 1 ergibt, dann wird gefärbt (das definieren Sie im Dialog hinter dem Button Formatieren…).
Achtung: die Dollarzeichen sind wichtig! $H$2:$H$7 sorgt dafür, dass immer im gleichen Bereich verglichen wird, $H2 als Kriterium bedeutet: wir prüfen immer Spalte H, aber wandern Zeile für Zeile mit – beim Formatieren der Zeile 3 wird der Inhalt von H3 geprüft, ….

(Wenn Ihr Bereich nicht H2 bis J7 ist, müssen Sie das entsprechend anpassen. Und wenn es mehr als drei Spalten sind, dann müssen Sie noch mehr Bereich;Kriterium-Paare ergänzen – oder doch zu einer der Varianten mit Zusatzspalte aus den letzten Tagen greifen.)

 

Veröffentlicht unter Excel, Uncategorized | Verschlagwortet mit , , , , , , | Kommentar hinterlassen