Hallo allerseits,
gegeben ist eine Zeichenfolge mit Semikolon-separierten Werten, die auf verschiedene Spalten einer Zeile verteilt werden sollen. Im Beispiel sind's nur 5 Werte und eine Zeile, in der Praxis bis zu 20 Millionen Zeilen und 100 Spalten, von denen etwa 20.000 schließlich in der Exceltabelle landen. Daher muss die Performance optimiert werden.
Ich schreibe die Datenfelder erstmal in ein Array und gebe dieses dann in die Exceltabelle aus. Dafür gibt es 2 Varianten. Variante 2 macht alles richtig. Bei der viel schnelleren Variante 1 existiert das Problem, das die Spalten 2 und 4 als Zeichenfolge in der Zelle landen, nicht als Zahl.
Frage: Wie kann dieses Problem vermieden werden?
Achja: ".TextToColumns" ist mir auch bekannt, das möchte ich aber vermeiden. Welche Möglichkeiten gibt es noch?
Code:
Sub test()
Dim a() As String, s As String, n As Long
s = "AAA;2;BBB;123.56;CCC"
a = Split(s, ";")
'Variante 1
Range(Cells(1, 1), Cells(1, UBound(a) + 1)) = a
'Variante 2
For n = 1 To UBound(a) + 1: Cells(2, n) = a(n - 1): Next
End Sub
vg, MM
Hoi,
warum Dim a() as string und nicht variant (ausgenommen der Einwand Speicherbedarf) ?
edit:
I.S.v:
a = Quellrange.Value2
Zielrange.Value2 = a
Hallo Mase,
danke für die Antwort.
ob a Variant oder String ist, macht für das Problem keinen Unterschied. In beiden Fällen werden Zahlen nicht als solche erkannt und sauber in die Zellen geschrieben.
Die Datenquelle ist eine Textdatei (*.csv), kein Excel.
vg, MM
Hallo,
wenn man dein Beispiel nicht in einer Zeile schreibt sondern in eine Spalte funktioniert es.
Code:
Sub test()
Dim a() As String, s As String, n As Long
s = "AAA;2;BBB;123.56;CCC"
a = Split(s, ";")
'Variante 1
Range(Cells(1, 1), Cells(UBound(a) + 1, 1)) = WorksheetFunction.Transpose(a)
'Variante 2
For n = 1 To UBound(a) + 1: Cells(n, 2) = a(n - 1): Next
End Sub
Hi
und so dann auch wieder für eine Zeile. Geschwindigkeit???
Code:
Application.Transpose(Application.Transpose(a))
(18.07.2019, 15:33)mmat schrieb: [ -> ]Hallo Mase,
danke für die Antwort.
ob a Variant oder String ist, macht für das Problem keinen Unterschied. In beiden Fällen werden Zahlen nicht als solche erkannt und sauber in die Zellen geschrieben.
Die Datenquelle ist eine Textdatei (*.csv), kein Excel.
vg, MM
Stimmt, wenn aus *.csv bringt das nichts; und doppelt gemoppelt (erst einlesen, dann variant-array) beantwortet die eigentliche (gute Frage wie ich finde :70: ) letztlich auch nicht ;).
Interessanterweise findet bei Cells() im Vergleich zu Range() keine implizite Typumwandlung statt.
By the way:
Ein interessanter Performancetest zwischen .Text, .Value, .Value2 lässt sich für Interessierte folgender Link finden:
https://fastexcel.wordpress.com/2011/11/...-avoid-it/ :68:
Warum dim a() as string?
Dann müssen alle Elemente von a Texte sein. Einfacher wäre ohne den Dim-Befehl zu arbeiten, dann ergibt "Split()" ein Variant-Array.
Ungeprüft: cells(1,1).resize(,ubound(a)+1) = a
Wenn es auf Performance ankommt, sollte zuerst alles in ein Array geschrieben werden und erst dann alles auf einmal in die Excel-Zellen.
(18.07.2019, 16:22)Steffl schrieb: [ -> ]Hallo,
wenn man dein Beispiel nicht in einer Zeile schreibt sondern in eine Spalte funktioniert es.
Code:
Sub test()
Dim a() As String, s As String, n As Long
s = "AAA;2;BBB;123.56;CCC"
a = Split(s, ";")
'Variante 1
Range(Cells(1, 1), Cells(UBound(a) + 1, 1)) = WorksheetFunction.Transpose(a)
'Variante 2
For n = 1 To UBound(a) + 1: Cells(n, 2) = a(n - 1): Next
End Sub
Hallo Steffl,
bei mir leider nicht nicht (Office 2010). Mit transpose hab ich das selbe Phänomen in der 1. Spalte (der fehlende implizite Typecast) wie sonst in der 1. Zeile.
Trotzdem: Eine interessante Idee !
vg, MM
Hallo
ich weiss jetzt nicht ob ich mich blamiere, bei 20.000 Zeilen waere es einen Versuch wert!
Was ist wenn man Variante 1 verwendet, und in die Spalte daneben per VBA diese Formel setzt und runterzieht =A1*1
Diese Formel kann man danach wieder per VBA in echte Zahlen umwandeln. Vielleicht mal testen??
mfg Gast 123
(19.07.2019, 13:06)Gast 123 schrieb: [ -> ]Hallo
ich weiss jetzt nicht ob ich mich blamiere, bei 20.000 Zeilen waere es einen Versuch wert!
Was ist wenn man Variante 1 verwendet, und in die Spalte daneben per VBA diese Formel setzt und runterzieht =A1*1
Diese Formel kann man danach wieder per VBA in echte Zahlen umwandeln. Vielleicht mal testen??
mfg Gast 123
Hallo Gast,
Nee, das ist eigentlich eine gute Idee, auf die ich jedenfalls auch nicht gekommen bin
Danke
vg, MM
Hallo,
ich danke allen für ihre Mitwirkung.
Inzwischen hab ich mich damit abgefunden, dass der Typecast bei Variante 1 einfach nicht funktioniert und folgenden Workaround entwickelt. Wie bereits erwähnt, macht es keinen Unterschied (für das Problem), ob a als variant oder string deklariert ist.
Der Workaround besteht aus einer kleinen Schleife, in der die ganze Zeile in ein weiteres Array kopiert wird. Das ist allemal schneller, als jede Zelle einzeln in die Tabelle zu schreiben.
Code:
Sub test()
Dim a() As String, s As String, n As Long, b
Cells.ClearContents
s = "AAA;2;BBB;123.56;CCC"
a = Split(s, ";")
ReDim b(UBound(a))
For n = 0 To UBound(a)
If IsNumeric(a(n)) Then b(n) = Val(a(n)) Else b(n) = a(n)
Next
'Variante 1: Alle Zellen der Zeile auf einmal schreiben
Range(Cells(1, 1), Cells(1, UBound(a) + 1)) = b
''Variante 2: jede Zelle einzeln schreiben (mit implizitem Typecast). Laaaaaaahm ...
' For n = 1 To UBound(a) + 1: Cells(2, n) = a(n - 1): Next
End Sub
vg, MM