mikrocontroller.net

Forum: PC Hard- und Software CVS Import mit VBA, Zeilenumbrüche entfernen


Autor: Kolja L. (kolja82)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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ß

Autor: Peter M. (r2d3)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

: Bearbeitet durch User
Autor: Kolja L. (kolja82)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

Autor: Peter M. (r2d3)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

Autor: Peter M. (r2d3)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

Autor: Peter D. (pedre)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Funktioniert ein zusätzliches Trim()?

ändere

arrTmp = Split(ArrDaten(lngR), cstrDelim)

zu

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

Autor: Kolja L. (kolja82)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Peter D. schrieb:
> Funktioniert ein zusätzliches Trim()?

Gerade getest, leider nicht :-(

Autor: Weingut P. (weinbauer)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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

Autor: Peter M. (r2d3)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

Autor: c-heater (Gast)
Datum:

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

Autor: Kolja L. (kolja82)
Datum:
Angehängte Dateien:

Bewertung
0 lesenswert
nicht lesenswert
Die Zeilenumbrüche sind innerhalb des Verwendungszwecks, siehe Bild.

Autor: Kolja L. (kolja82)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

Autor: Peter M. (r2d3)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

Autor: Kolja L. (kolja82)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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

Autor: Kolja L. (kolja82)
Datum:
Angehängte Dateien:

Bewertung
0 lesenswert
nicht lesenswert
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.

Autor: Micha (Gast)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Ich vermute, dass nur die Zellausrichtung „unten“ ist.

Autor: Kolja L. (kolja82)
Datum:
Angehängte Dateien:

Bewertung
0 lesenswert
nicht lesenswert
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.

Autor: Peter M. (r2d3)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

Autor: Kolja L. (kolja82)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

: Bearbeitet durch User
Autor: Peter M. (r2d3)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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))

: Bearbeitet durch User
Autor: Peter M. (r2d3)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Kolja, gucken, Korrektur!

Autor: Kolja L. (kolja82)
Datum:

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

10

Autor: Peter M. (r2d3)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

: Bearbeitet durch User
Autor: Kolja L. (kolja82)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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 !

Autor: Peter M. (r2d3)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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!

Autor: Kolja L. (kolja82)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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...

Autor: Peter M. (r2d3)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

Autor: Kolja L. (kolja82)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

Autor: c-hater (Gast)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

Autor: Thomas S. (doschi_)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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/

Autor: Peter M. (r2d3)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

Autor: Kolja L. (kolja82)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

Autor: Kolja L. (kolja82)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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...

: Bearbeitet durch User
Autor: Kolja L. (kolja82)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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 ;-)

Autor: georg (Gast)
Datum:
Angehängte Dateien:

Bewertung
0 lesenswert
nicht lesenswert
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

Autor: Kolja L. (kolja82)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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 :-)

Autor: Peter M. (r2d3)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
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.

: Bearbeitet durch User

Antwort schreiben

Die Angabe einer E-Mail-Adresse ist freiwillig. Wenn Sie automatisch per E-Mail über Antworten auf Ihren Beitrag informiert werden möchten, melden Sie sich bitte an.

Wichtige Regeln - erst lesen, dann posten!

  • Groß- und Kleinschreibung verwenden
  • Längeren Sourcecode nicht im Text einfügen, sondern als Dateianhang

Formatierung (mehr Informationen...)

  • [c]C-Code[/c]
  • [avrasm]AVR-Assembler-Code[/avrasm]
  • [code]Code in anderen Sprachen, ASCII-Zeichnungen[/code]
  • [math]Formel in LaTeX-Syntax[/math]
  • [[Titel]] - Link zu Artikel
  • Verweis auf anderen Beitrag einfügen: Rechtsklick auf Beitragstitel,
    "Adresse kopieren", und in den Text einfügen




Bild automatisch verkleinern, falls nötig
Bitte das JPG-Format nur für Fotos und Scans verwenden!
Zeichnungen und Screenshots im PNG- oder
GIF-Format hochladen. Siehe Bildformate.

Mit dem Abschicken bestätigst du, die Nutzungsbedingungen anzuerkennen.