CVS Import mit VBA, Zeilenumbrüche entfernen

[Kolja L. (kolja82)]

Moin

Ich habe jetzt doch noch ein wenig mit VBA rumgespeilt, bzw ein Skript 
gefunden, mit welchem ich CSV Dateien importieren in Excel importieren 
kann.

Sub Datei_Importieren()
  Dim strFileName As String, ArrDaten, arrTmp, lngR As Long, lngLast As Long
  Const cstrDelim As String = ";" 'Trennzeichen
  
  With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Datei wählen"
    .InitialFileName = "C:\Users\Kolja\ownCloud\H26\Finanzen\Unsätze\*.csv"  'Pfad anpassen
    If .Show = -1 Then
      strFileName = .SelectedItems(1)
    End If
  End With
  
  If strFileName <> "" Then
    Application.ScreenUpdating = False
    Open strFileName For Input As #1
    ArrDaten = Split(Input(LOF(1), 1), vbCrLf)
    Close #1
    For lngR = 1 To UBound(ArrDaten)
      arrTmp = Split(ArrDaten(lngR), cstrDelim)
      If UBound(arrTmp) > -1 Then
        With ActiveSheet
          lngLast = .Cells(Rows.Count, 1).End(xlUp).Row + 1
          lngLast = Application.Max(lngLast, 10)
          .Cells(lngLast, 1).Resize(, UBound(arrTmp) + 1) _
            = Application.Transpose(Application.Transpose(arrTmp))
        End With
      End If
    Next lngR
  End If
End Sub

Schon mal ganz cool :-)

Aber das Problem mit den Zeilenumbrüchen bleibt.
Wie kann man die Entfernen?

Danke und Gruß

[Peter M. (r2d3)]

Hallo Kolja,

Kolja L. schrieb:
> lngLast = .Cells(Rows.Count, 1).End(xlUp).Row + 1
> lngLast = Application.Max(lngLast, 10)

ich verstehe den Sinn dieses Codes nicht.

Ansonsten finde ich es einfacher, die Datei byte- bzw. zeichenweise 
einzulesen und je nach Ergebnis Spalten, bzw. Zeilen weiterzuspringen 
bzw. den Einlesestring zu verlängern.

[Kolja L. (kolja82)]

Hi Peter

Den Sinn verstehe ich leider auch nicht.
Den ganze Code habe ich so gefunden und bis auf die Zeilenumbrüche 
funktioniert er auch, wie ich es mir wünsche.

Und auch wenn ich prinzipiell verstehe, was du mit dem zeichenweise 
einlesen meinst,
programmieren kann ich es leider nicht.

Meine Hoffnung war / ist, dass irgendwo in den Code noch ein Zeile mit: 
ersetze "LF" durch "nix" hinzugefügt werden kann.

[Peter M. (r2d3)]

Wie äüßert sich das mit den Vorschüben im Ergebnis?

Ein Blick auf den Code lässt mich nicht erkennen, was der mit der 
Tabelle bewirkt.

[Peter M. (r2d3)]

Kolja L. schrieb:
> Meine Hoffnung war / ist, dass irgendwo in den Code noch ein Zeile mit:
> ersetze "LF" durch "nix" hinzugefügt werden kann.

Ja, prinzipiell geht so etwas.

[Peter D. (Gast)]

Funktioniert ein zusätzliches Trim()?

ändere

arrTmp = Split(ArrDaten(lngR), cstrDelim)

zu

arrTmp = Split(Trim(ArrDaten(lngR)), cstrDelim)

[Kolja L. (kolja82)]

Peter D. schrieb:
> Funktioniert ein zusätzliches Trim()?

Gerade getest, leider nicht :-(

[Weingut P. (weinbauer)]

Ich versteh genau nicht wo das Problem mit CR LF liegt,
Du hast doch Bankdaten, Buchungsdaten und jeder Datensatz wird
mit CR LF abgeschlossen, damit die Buchungsdaten eben als Tabelle
untereinander kommen.
Machst Du die raus, dann kommt alles in eine Reihe.

Zur Not einfach die ASCII Codes 10 und 13 durch leeren String ersetzen

[Peter M. (r2d3)]

Hallo Weingut P.,

Weingut P. schrieb:
> Ich versteh genau nicht wo das Problem mit CR LF liegt,

Kolja sucht die fertige Lösung von uns.
Er kann/will nicht selbst programmieren.

[c-heater (Gast)]

Für was braucht man das? Excel kann doch CSV Dateien direkt laden.

[Kolja L. (kolja82)]

Die Zeilenumbrüche sind innerhalb des Verwendungszwecks, siehe Bild.

[Kolja L. (kolja82)]

Weingut P. schrieb:
> Zur Not einfach die ASCII Codes 10 und 13 durch leeren String ersetzen

Ja, gerne.
Aber das:

myStr = Replace(myStr, vbCr, " ")
myStr = Replace(myStr, vbLf, " ")
myStr = Replace(myStr, vbCrLf, " ") '// or vbNewLine

funktioniert leider auch nicht.

c-heater schrieb:
> Für was braucht man das? Excel kann doch CSV Dateien direkt laden.

Ich habe ein bestehendes Dokument und möchte immer wieder neue Daten in 
dieses laden.

[Peter M. (r2d3)]

Hallo Kolja,

Du kannst offensichtlich doch programmieren?
Mit Deinem Bildschirmausschnitt ist das Problem viel klarer beschrieben.
Da muss man erst einmal die Zeichenkette analysieren.

Mit dieser Excelformel kannst Du Dir die ASCII-Codes der Zeichenkette in 
F7 an der Position E8 angucken.
Bitte für Deine Bedürfnisse anpassen und einfach mal die umgebrochene 
Zelle analysieren.

=ASC(TEIL($F$7;E8;1))

Ich habe hier nur ein Excel2000.
Bei den späteren Varianten von Excel gibt es die Formeln einmal für 
byteweises Gucken und einmal für Unicode.

Du kannst Dir die CSV-Datei auch in einem Hex-Editor angucken, um dem 
Verhalten auf die Spur zu kommen.

[Kolja L. (kolja82)]

Peter M. schrieb:
> Du kannst offensichtlich doch programmieren?

Nein, das würde ich nicht so nennen.
Ein Programmierer wohl auch nicht :-)

Ein wenig Arduino und gaanz früher mal PHP.
Daher verstehe ich schon, was die replace() Funktion machen soll,
aber ich habe keine Ahnung, warum sie dies nicht macht.

Außerdem finde ich VBA irgendwie unintuitiv, oder zumindest ganz anders 
als die Sprachen, die ich zumindest ein bisschen verstehe.

Peter M. schrieb:
> =ASC(TEIL($F$7;E8;1))

Versuche ich mal, danke

[Kolja L. (kolja82)]

Peter M. schrieb:
> Du kannst Dir die CSV-Datei auch in einem Hex-Editor angucken, um dem
> Verhalten auf die Spur zu kommen.

Das ist zumindest das, was ein Texteditor dazu sagt.

[Micha (Gast)]

Ich vermute, dass nur die Zellausrichtung „unten“ ist.

[Kolja L. (kolja82)]

Micha schrieb:
> Ich vermute, dass nur die Zellausrichtung „unten“ ist.

Aber die Zeilenumbrüche sind real :-(

Peter M. schrieb:
> =ASC(TEIL($F$7;E8;1))

Habe ein bisschen gebraucht, um zu verstehen was das soll.
Im Bild meine Interpretation.

[Peter M. (r2d3)]

Kolja L. schrieb:
> Micha schrieb:
>> Ich vermute, dass nur die Zellausrichtung „unten“ ist.
>
> Aber die Zeilenumbrüche sind real :-(
>
> Peter M. schrieb:
>> =ASC(TEIL($F$7;E8;1))
>
> Habe ein bisschen gebraucht, um zu verstehen was das soll.
> Im Bild meine Interpretation.

Was ist das für ein Zeichen zwischen "strass" und "e"?
Welcher Code? Dafür musst Du eine Spalte breiter machen.
Da spielt die Musik. :)

Das müsstest Du mal gegen "nichts" austauschen.

[Kolja L. (kolja82)]

In Excel ist dort nichts zu sehen, C&P in den Texeditor ergibt, CR LF.
Aber das wussten wir doch schon:

Aus der Wikipedia: CR LF 13 10   \r\n

Peter M. schrieb:
> Das müsstest Du mal gegen "nichts" austauschen.

Habe ich ja versucht:

Kolja L. schrieb:
> myStr = Replace(myStr, vbCr, " ")
> myStr = Replace(myStr, vbLf, " ")
> myStr = Replace(myStr, vbCrLf, " ") '// or vbNewLine

Gab n Laufzeitfehler 13, was  aber (scheinbar) sehr unspezifisch ist.
An dem versuchten Zeichen (vb**) lag es nicht, ich konnte auch nicht "5" 
durch "1" ersetzen.

[Peter M. (r2d3)]

Kolja L. schrieb:
> In Excel ist dort nichts zu sehen,

Ja, weil die Spalten so schmal sind, dass die ASCII-Codes nicht 
angezeigt werden. :)


C&P in den Texeditor ergibt, CR LF.

Copy und Paste in den Texteditor ist irrelevant.
"CR" und "LF" sind Bezeichner und keine ASCII-Codes.
Die will ich sehen - die Zahlenwerte!


Korrektur!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Fehler bei mir!

Wir brauche unter den Buchstaben die ASCII-Codes.

=CODE(TEIL($F$7;E8;1))

[Peter M. (r2d3)]

Kolja, gucken, Korrektur!

[Kolja L. (kolja82)]

Peter M. schrieb:
> =CODE(TEIL($F$7;E8;1))

10

[Peter M. (r2d3)]

Schön!

Variante 1:

Mal mit dem Hexeditor alle Line Feed löschen, also ersetzen durch 
"Nichts" und dann in Excel laden.

Ergebnis?

Variante 2:

In Deinem VBA-Code auch mal die restlichen Parameter nutzen und die 
beiden untenstehenden Compare-Parameter austesten.

Aus der VBA-Hilfe:

Replace(expression, find, replace[, start[, count[, compare]]])

Für find "chr(10)" benutzen.

vbBinaryCompare 0 Führt einen binären Vergleich durch.
vbTextCompare 1 Führt einen Textvergleich durch.

[Kolja L. (kolja82)]

Peter M. schrieb:
> Mal mit dem Hexeditor alle Line Feed löschen, also ersetzen durch
> "Nichts" und dann in Excel laden.

Na dann sollte es funktionieren, aber es soll ja ohne einen 
vorgeschalteteten Editer machbar sein.

Peter M. schrieb:
> Replace(expression, find, replace[, start[, count[, compare]]])

Habe jetzt einiges durchprobiert, so wie diese Zeile:
arrTmp = Replace(arrTmp, vbLf, "", , vbBinaryCompare)

Es gibt aber immer den Laufzeitfehler.
Da es den auch gibt wenn ich "a" durch "x" ersetzten will, scheint das 
Problem wohl noch vor dem "LF" zu liegen.

Danke für dein Engagement !

[Peter M. (r2d3)]

Kolja L. schrieb:
> Peter M. schrieb:
>> Mal mit dem Hexeditor alle Line Feed löschen, also ersetzen durch
>> "Nichts" und dann in Excel laden.
>
> Na dann sollte es funktionieren, aber es soll ja ohne einen
> vorgeschalteteten Editer machbar sein.

Deswegen sagte ich, byteweise einlesen, wegschreiben und den Müll 
einfach filtern.

>
> Peter M. schrieb:
>> Replace(expression, find, replace[, start[, count[, compare]]])
>
> Habe jetzt einiges durchprobiert, so wie diese Zeile:
> arrTmp = Replace(arrTmp, vbLf, "", , vbBinaryCompare)

Statt VBLF bitte auch Chr(10) probieren.
Ich weiss nicht ob Replace mit leeren Argumenten zurecht kommt.
arrTmp einfach mal Buchstabe für Buchstabe mit einer Schleife in VBA 
ausgeben, z.B. mit Debug.print auf den Ausgabebereich.
Alles angucken wo der ASCII-Code <31 und größer 127 ist.

>
> Es gibt aber immer den Laufzeitfehler.
> Da es den auch gibt wenn ich "a" durch "x" ersetzten will, scheint das
> Problem wohl noch vor dem "LF" zu liegen.
>
> Danke für dein Engagement !

Ich freue mich über Deinen Dank! Viel Erfolg!

[Kolja L. (kolja82)]

Du hast nicht zufällig ein Konto bei einer Volksbank?
Daher kommt nämlich meine Ursprungs-CSV.

Peter M. schrieb:
> Statt VBLF bitte auch Chr(10) probieren.
> Ich weiss nicht ob Replace mit leeren Argumenten zurecht kommt.

Beides probiert, immer Laufzeit13...

[Peter M. (r2d3)]

Kolja L. schrieb:
> Du hast nicht zufällig ein Konto bei einer Volksbank?
> Daher kommt nämlich meine Ursprungs-CSV.
>
> Peter M. schrieb:
>> Statt VBLF bitte auch Chr(10) probieren.
>> Ich weiss nicht ob Replace mit leeren Argumenten zurecht kommt.
>
> Beides probiert, immer Laufzeit13...

Das ist echt doof.

Dann muss man leider die Zeichenkette z.B. im Rahmen einer VBA-Funktion 
reparieren, aber die VBA-Funktionen werden interpretiert und bremsen die 
Tabellenkalkulation fürchterlich aus.

Alternativ über VBA einlesen, filtern und gleich in die Zellen 
wegschreiben oder über den Umweg in eine neue, saubere CSV-Datei und die 
dann regulär einlesen.

[Kolja L. (kolja82)]

Peter M. schrieb:
> Das ist echt doof.

Stimmt :-(


Für heute gebe ich auf.
Nochmal vielen Dank, immerhin habe ich mich jetzt endlich mal mit VBA 
beschäftigt.

[c-hater (Gast)]

Kolja L. schrieb:

> Die Zeilenumbrüche sind innerhalb des Verwendungszwecks, siehe Bild.

Jetzt wird endlich das Problem klar. Und das ist: hier werden innerhalb 
eines Feldes genau die Zeichen verwendet, die als Delimeter für 
Datensätze dienen sollen. Ein ähnliches Problem würde natürlich auch 
auftreten, wenn der Datenfeld-Delimeter ";" innerhalb von Datenfeldern 
verwendet wird.

Das hat nix mit Windows oder VBA zu schaffen, das ist ein ganz 
grundsätzliches Problem. Und die übliche Lösung ist "Quoting". Im 
vorliegenden Fall höchstwahrscheinlich so gelöst, dass die betreffenden 
Felder (oder möglicherweise auch alle Felder oder zumindest alle 
Nicht-Zahl-Felder) in der CSV-Datei in '"'-Zeichen eingeschlossen sind. 
Innerhalb so eines Paares von Anführungszeichen verlieren die Delimeter 
ihre besondere Funktion.

Das Problem ist nun nur, dass der sehr primitive VBA-Code nicht darauf 
vorbereitet ist, mit Quoting umzugehen und eben nicht berücksichtigt, 
dass nicht jedes Vorkommen eines Delimeter-Zeichens auch tatsächlich ein 
Delimeter ist. Dazu müsste man einen Code schreiben, der vollkommen 
anders funktioniert, nämlich zeichenweise.

[Thomas S. (doschi_)]

Evtl. hat stackoverflow einen Tipp,
z.B. 
https://stackoverflow.com/questions/2668678/importing-csv-with-line-breaks-in-excel-2007
Vielleicht ist die csv-Datei ja UTF-8 oder Unicode-formatiert.

Oder Tipps von der Libreoffice-Fraktion,
https://ask.libreoffice.org/en/question/135603/how-to-remove-multiple-carriage-returns-line-breaks-from-cells-in-calc/

[Peter M. (r2d3)]

Hallo c-hater,

c-hater schrieb:
> Jetzt wird endlich das Problem klar. Und das ist: hier werden innerhalb
> eines Feldes genau die Zeichen verwendet, die als Delimeter für
> Datensätze dienen sollen.

Nein, denn dann würde eine Excel-Zeile einfach mehr befüllte Zellen 
umfassen, bzw. es gäbe kein LF-Zeichen/Zeilenumbrüche mehr in einer 
Zelle.

Das Problem hat nichts mit dem Import zu tun.
Inhaltlich will Kolja eigentlich nur wissen, wie man die LF innerhalb 
einer Zelle entfernt.

[Kolja L. (kolja82)]

Guten Morgen und erstmal vielen Dank für die Antworten.

Peter M. schrieb:
> Inhaltlich will Kolja eigentlich nur wissen, wie man die LF innerhalb
> einer Zelle entfernt.

Mir ist es ja eigentlich sogar egal, ob die LF aus den Zellen entfernt 
wird, oder erst gar nicht eingefügt.

[Kolja L. (kolja82)]

GESCHAFFT!!!!!!!!!

Sub Zeichen_Entfernen()
For i = 1 To [a65536].End(xlUp).Row
Cells(i, 9).Value = WorksheetFunction.Substitute(Cells(i, 1), Chr(10), 
"") ' Entfernt (Linke Klammern in Spalte 1
Next
End Sub




edit:
zu früh gefreut, irgendwie wird nur die Spalte gelöscht...

[Kolja L. (kolja82)]

Jetzt aber:

Sub Replace()
Dim Bereich As Range
Set Bereich = ActiveSheet.UsedRange

    Bereich.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Bereich.Replace What:=Chr(34), Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Die 10 ersetzt die Zeilenumbrüche, die 34 die Anführungszeichen, wenn 
man schonmal dabei ist ;-)

[georg (Gast)]

Peter M. schrieb:
> Inhaltlich will Kolja eigentlich nur wissen, wie man die LF innerhalb
> einer Zelle entfernt.

Allerdings ist "Verwendungszweck" ein mehrzeiliges Feld, nicht nur in 
Excel, sondern auch im Online-Formular und auf dem Überweisungsvordruck. 
Löscht man da die Zeilenvorschübe raus so ergeben sich u.U. endlos 
breite Textfelder, die man auf keinem Drucker mehr drucken kann.

Die LFs in einem Feld wie Verwendungszweck zu löschen ist daher von 
vornherein Unsinn. Siehe Screenshot.

Georg

[Kolja L. (kolja82)]

Moin Georg

Danke für die Erklärung!
Das erklärt auch, warum wir gestern, beim Drucken so viele Seiten mit 
wenig Inhalt bekommen haben.
Aber dass soll ja in Zukunft nicht mehr vorkommen, da die Zelle mit dem 
Verwendungszweck nur auf dem ersten Tabellenblatt erscheint, danach wird 
sie durch eine manuelle Zuordnung ersetzt.

Also LF löschen ist nur Unsinn, wenn gedruckt werden soll.

Mein Makro habe ich jetzt noch um eine Formatierung der Zellen 
erweitert.
Also einfach die Aufnahme gestartet, Zellenbreite und Höhe angepasst und 
die  Textausrichtung eingestellt.
Klappt super :-)

[Peter M. (r2d3)]

Hallo georg,

georg schrieb:
> Die LFs in einem Feld wie Verwendungszweck zu löschen ist daher von
> vornherein Unsinn. Siehe Screenshot.

Harte Zeilenumbrüche sind eher Murks!
Das Löschen dieser Umbrüche ist kein Unsinn, sondern sinnvoll, denn wenn 
Du für eine Zelle in Excel den Zeilenumbruch aktivierst, bricht Excel 
automatisch in Abhängigkeit von der Spaltenbreite um.

Die Bloomberg-Nachrichten, die noch aus Zeiten von Terminals ohne 
Grafikdarstellung zu stammen scheinen, verursachen die harten 
Zeilensprünge auch nur Ärger.