Office: TXT-Import brauchbar machen

Hallo zusammen,

mein Problem sieht wie folgt aus. In regelmäßigen Abständen erhalte ich Daten als txt, die in Excel importiert werden müssen.

Das importierte Format kann nur mit sehr viel Handarbeit nutzbar gemacht werden. Die Größe liegt manchmal bei 20000 Zeilen, was sehr zeitintensiv ist. Mein Kollege meinte nun, ich sollte das am besten mit VBA lösen, von dem ich nur Null bis ganz wenig Ahnung habe.

Die immer wiederkehrenden Dateien sind im Original recht groß, ich habe nun einfach eine Test-Datei erstellt, die alle meine Schwierigkeiten darstellt. Der obere Teil zeigt das vorher, der untere, so wie es am Besten aussehen sollte.

Es wäre ganz wunderbar, wenn mir dabei jemand helfen würde.

Aufgaben:

1. Zeitstempel B1:C1 entfernen (in ROT), D1:L1 nach C1:K1 kopieren. Alle weiteren Überschriften entfernen
2. In A2 befindet sich der übergeordnete Pfad, in B3 und Folgende, die Dateien. Hieraus sollte aus jeder Datei Pfad und Datei entstehen (z.B. $AAAAA.P1766PRM / I1766ACK --> $AAAAA.P1766PRM.I1766PRM) - zwischen Pfad und Datei kommt noch immer ein Punkt. Der zusammengefügte Pfad sollte nun in Spalte A stehen.
3. Manchmal kann es vorkommen, daß ein großes "O" in Spalte C erscheint. Dieses muss gelöscht werden, denn es verschiebt alles rechts daneben.

Hoffentlich habe ich meine Probleme verständlich ausgedrückt?

Ich wäre unendlich dankbar, wenn ich von diesen immer wiederkehrenden Kopfschmerzen erlöst würde

LG,
Lena

 

Sub importbearb()

Dim rng As Range
Dim rngFund As Range
Dim i&, col&
Dim sNr$

Set rng = ActiveSheet.UsedRange
'O's löschen
Set rngFund = rng.Find(What:="O", LookIn:=xlValues, lookat:=xlWhole)
If Not rngFund Is Nothing Then
col = rngFund.Column
For i = rngFund.Row To rng.Rows.Count + rng.Row - 1
If Cells(i, col) = "O" Then Cells(i, col).Delete shift:=xlToLeft
Next
End If

'Spale 1 und 2 verbinden
For i = rng.Row To rng.Rows.Count + rng.Row - 1
If rng.Cells(i, 1).Value <> "" Then
sNr = Cells(i, 1).Value
Else
If sNr <> "" Then
rng.Cells(i, 1).Value = sNr & "." & rng.Cells(i, 2).Value
End If
End If
Next

'Spalte 2 Löschen
rng.Columns(2).Delete shift:=xlToLeft
'kopfzeilen nach links verschieben
rng.Cells(1).Delete shift:=xlToLeft

'Zeilen löschen mit "CODE" oder "" in Spalte 3
Set rngFund = Nothing
For i = rng.Rows.Count + rng.Row - 1 to rng.Row Step -1

If rng.Rows(i).Cells(3).Value = "" Or rng.Rows(i).Cells(3).Value = "CODE" Then
If rngFund Is Nothing Then 'alle Zellreferenzen sammeln
Set rngFund = rng.Cells(i, 1)
Else
Set rngFund = Union(rngFund, rng.Cells(i, 1))
End If
End If
Next
'unnötige Zeilen löschen
If Not rngFund Is Nothing Then rngFund.EntireRow.Delete shift:=xlUp

rng.Cells(1).Clear 'erste Zelle leer machen
End Sub

 

Vielen Dank, für deine Antwort.

Leider funktioniert das noch nicht so, wie gewünscht, aber ich konnte einen Block deines Codes
ganz wunderbar verwenden. Vielen Dank, dafür. Werde meinen vollendeten Code später präsentieren
um mich der Lächerlichkeit preiszugeben :-)

Momentan stehe ich vor dem Problem einen String zu suchen und alle, die dem NICHT entsprechen,
zu löschen.

Ich suche nach allen Reihen, die folgendes Format enthalten:

Beispiel: $AAAAA.BBBBB.CCCCC (jeder der Blöcke zw. den Punkten kann unterschiedlich lang sein)
Bei allen anderen Funden, sollte die Reihe gelöscht werden.

Hier ein Teil des Codes:
==================
If Not Cells(n, 1).Value = "$*.*." Then
'Code Zeile löschen
Rows(n).Delete Shift:=xlUp

Frage: Kann man einen Platzhalter wie '*' nur einmal verwenden?

Gruß,
Lena

 

wenn du zwei Werte vergleichen willst, dann formatiere den Vergleichswert auch so
also z.b. If Cells(i, 1).Value = Format(Cells(i, 1).Value, "$*.*.") then

 

Vielen Dank, für deinen Hinweis. So sieht der Code jetzt aus, allerdings tut er nicht, was ich mir so erhofft habe.

Sub test()

Dim rng As Range
Dim n As Long
Dim letzteZeile As Long

Set rng = ActiveSheet.UsedRange

letzteZeile = Range("A4000").End(xlUp).Row
For n = letzteZeile To 2 Step -1

If Not Cells(n, 1).Value Like Format(Cells(n, 1).Value, "$*.*.") Then

Rows(n).Delete Shift:=xlUp

End If
Next n

End Sub

 

Hier, mein kleiner Test

 

Hi,

immerhin hast du ja mittlerweile raus gefunden, dass das Gleichheitszeichen keine Platzhalter erlaubt und dass man statt dessen den Like-Operator verwenden muss.
Jetzt musst du nur noch den Quatsch mit Format weg lassen. Und .Value ist auch überflüssig.

Code:

If Not Cells(n, 1) Like "$*.*.*" Then

 

Hi,

ich habe Stunden gebraucht und ewig herumprobiert und nun kommst Du daher und vereinfachst das Problem komplett.
Davon abgesehen, daß es tatsächlich Spaß macht, kann es dennoch frustrierend sein, wenn man den Wald vor lauter Bäumen nicht sieht.

Vielen Dank für deinen Input, auf zum nächsten Problem!

 

Hallo Hellmut, viele Wege führen nach Rom. Das mit dem Quatsch hättest du dir ruhig schenken können.

 

Hi Ralf,

sorry, aber in diesem Fall ist Format schlicht Quatsch. Die Ausgangslage:

Hier soll also ein Text geprüft werden, ob zuerst ein $-Zeichen kommt, dann irgendwas, dann ein Punkt, wieder irgendwas und noch ein Punkt.

Deine "Hilfe" dazu:

Hast du dir mal angeschaut, was raus kommt, wenn man eine Zahl mit "$*.*." formatiert? Ein einfaches Dollarzeichen!
Und bei einem Text wie $AAAAA.BBBBB.CCCCC? Der Text $AAAAA.BBBBB.CCCCC - es passiert also nichts!
Folglich ist dein Vergleich bei einem Text immer Wahr und bei einer Zahl immer Falsch.

Daher ist das mit dem Format Quatsch!

 

hmm verdammt, stimmt.
da hatte ich zu wenig getestet.

 

Mit Eurer Hilfe konnte ich die meisten Probleme lösen, hänge aber noch an Folgendem.
Manche Zellen "verrutschen" innerhalb des Datenblocks. Hier benutze ich 2 If-Schleifen,
die bestimmte Werte abfragen. Diese gesuchten Werte gibt es in 8 verschiedenen Kombinationen.

Sollte man das besser mit mit Select Case lösen oder ist das ok so? Die Schleifen brauchen
recht lange, was wahrscheinlich an meiner Art des Programmierens liegt. Bitte seit etwas nachsichtig.

letzteZeile = Range("A4000").End(xlUp).Row
For x = letzteZeile To 3 Step -1

If Cells(x, 7) Like "XPK" Then
String1 = Cells(x, 7).Value
If Cells(x, 8).Value = "In" Then
String2 = Cells(x, 8).Value
Cells(x, 7).ClearContents
Cells(x, 8).ClearContents
String3 = String1 & " " & String2
Cells(x, 7).Value = String3
Range("I" & x, "J" & x).Cut Destination:=Range("H" & x)
End If
End If

' Spalten checken und hin- und herschieben!!!

Next x


Es funktioniert eigentlich so, frage mich aber, ob diese die beste Lösung ist?
Wie sähe so ein Select Case Fall aus? Ich habe etwas damit herumgespielt
und das Internet durchforstet, bekomme aber meine Versuche leider nicht ans
rennen.

LG,
Lena

 

Hi,

jeglicher Zugriff auf eine Zelle kostet richtig Zeit. Im Vergleich dazu kostet der Zugriff auf eine Variabel gar keine Zeit. Außerdem ist es fast egal, ob man auf eine Zelle oder 100.000 Zellen gleichzeitig zugreift. Daher ist es besser benachbarte Zellen in ein Array einzulesen und dieses zu bearbeiten.

Den Like-Operator braucht man nur, wenn man Wildcards verwenden will - oder wenn man Groß-/Kleinschreibung nicht unterscheiden will. Ansonsten reicht auch das einfache Gleichheitszeichen.

Da .Value die Default-Eigenschaft von Range und Cell ist, kann man darauf idR verzichten.

Wieso ermittelst du die letzte belegte Zeile oberhalb von Zeile 4000 statt oberhalb der letzten Zeile?

Code:

letzteZeile = Range("A" & Rows.Count).End(xlUp).Row
For x = letzteZeile To 3 Step -1
    arrString = Cells(x, 7).Resize(, 2)
    If arrString(1, 1) = "XPK" And arrString(1, 2) = "In" Then
        Cells(x, 7) = "XPK In"
        Cells(x, 8).ClearContents
        Range("I" & x, "J" & x).Cut Destination:=Range("H" & x)
    End If
    ' Spalten checken und hin- und herschieben!!!
Next x

Das dürfte schon merkbar schneller laufen. Richtig schnell wird es, wenn du alles in einem Array abhandelst. Sprich am Anfang alles in ein Array lesen, dieses durchlaufen und die notwendigen Werte in ein zweites Array kopieren. Am Ende zweites Array auf einen Schlag rausschreiben. Wenn du von vorne nach hinten arbeitest, kannst du auch mit einem Array auskommen.

Hast du dir eigentlich mal überlegt dies alles mit PowerQuery zu machen? Da wärst du sicherlich längst fertig.

 

Guten Morgen, Helmut.

Vielen Dank, für deinen Input, werde es gleich mal einbauen. Die Zeile 4000 ist ein Überbleibsel vom Testen.
Die regelmäßige Größe der Datensätze kann schon mal 20000 überschreiten.

Zu PowerQuery fehlt mir leider völlig der Bezug. Es ist für mich nicht so intuitiv. Werde Amazon mal fragen, was
es da an Einsteiger-Lektüre gibt.

Gruß,
Lena