Fehlermeldung CSV Datei, würde auch XLS betreffen

Sebastian_Moser · 10. November 2019 um 15:58

Hallo zusammen.

Ich habe mir eine CSV aus dem Web heruntergeladen und zwar die:
http://ip-to-country.webhosting.info/downloads/ip-to…

Diese enthält IP Ranges um User, die auf einer Homepage surfen zu lokalisieren.

Jetzt kommt beim Öffnen der CSV-Datei folgende Fehlermeldung:

Dieser Fehler tritt normalerweise auf, wenn Sie versuchen eine Datei
mit mehr als 65536 Zeilen oder 256 Spalten zu öffnen. Excel hat eine
Limitation von 65536 Datenzeilen und 256 Spalten pro Arbeitsblatt. 
Sie können mehrere Arbeitsblätter mit dieser Anzahl von Zeilen und 
Spalten benutzen, jedoch genügt meistens eine Arbeitsmappe (Datei). 
&nbsp; Die Anzahl der Arbeitsblätter, die Sie pro Arbeitsmappe benutzen 
können, findet ihre Limitation lediglich in der verfügbaren 
Speicherkapazität Ihres Betriebssystems. Standardmäßig, Excel kann 3
 Arbeitsblätter unterstützen, und mehr sofern die vorhandene 
Speicherkapazität die Anzahl der Daten unterstützt.

Das Abschneiden von Zeilen oder Spalten außerhalb der Limits erfolgt 
automatisch und kann nicht konfiguriert werden. Dieses Problem kann 
meistens damit behoben werden, indem Sie die Quelldatei mit einem 
Text-Editor, wie zum Beispiel Microsoft Office Word öffnen, und die 
Datei anschließend unter Aufteilung in mehrere Dateien mit Zeilen- 
oder Spaltenanzahlen innerhalb der Limits von Excel speichern. Diese 
Dateien können dann mit Excel geöffnet oder in ein Excel-Arbeitsblatt
 importiert werden.
Falls Sie ein Dateiformat verwenden, das die Anwendung eines 
Texteditors nicht unterstützt, kann es einfacher sein die Daten nach
 Microsoft Office Access zu importieren und anschließend die 
"Exportieren" Feature in Access anzuwenden, um die Daten in einem 
Excel-Format zu importieren. Andere Methoden zum Importieren größerer
 Dateien in mehrere Arbeitsblätter sind verfügbar, können jedoch 
wesentlich aufwendiger sein als die Anwendung eines Texteditors oder 
Access.

Die Fehlermeldung warum sie auftritt ist klar, jedoch, heisst das jetzt für mich die CSV-Datei beinhaltet nur die 65536 Datensätze oder würden eigentlich mehr darin stecken??

Besten Dank.
Gruss Sebastian

Felix_W_9c131d · 10. November 2019 um 15:58

Hallo,

Die Fehlermeldung warum sie auftritt ist klar, jedoch, heisst
das jetzt für mich die CSV-Datei beinhaltet nur die 65536
Datensätze oder würden eigentlich mehr darin stecken??

Es würden mehr darin stecken, das ist aber für Excel zuviel. Eine CSV-Datei ist ja reiner Text, das kann beliebig lang sein.

Cheers, Felix

plaicy · 10. November 2019 um 15:58

Hallo,

Ich habe mir eine CSV aus dem Web heruntergeladen und zwar
die:
http://ip-to-country.webhosting.info/downloads/ip-to…

die Datei hat wirklich mehr als 65535 Datensätze. Genaugenommen sind es 87810. Das Openoffice hier macht auch nur den ersten Teil der Daten auf. In deiner Fehlermeldung wurde ja schon vorgeschlagen Access (sofern vorhanden) zu benutzen.

Aber du hast nicht geschrieben, was du genau machen möchtest. Wenn es dir nur darum geht die Datei anzuschauen, sollte ja ein beliebiger Texteditor reichen. Wenn es darum geht einen bestimmten Bereich zu finden kommst du wohl an einer Datenbank-Anwendung oder etwas selbst programmierten nicht herum. Wenn ich darin eine bestimmte IP suchen wollte, würde ich erst einmal herausfinden, die wie IP-Adressen in die entsprechenden Zahlen umgewandelt werden, da es da mehrere Möglichkeiten gibt. Ich würde es erst einmal versuchen, ob die IP-Adresse sozusagen im Bigendian-Format umgewandelt wird (erster Teil höherwertigste), aber es kann auch anders sein. Wenn das klar ist, müsste man beispielsweise mit AWK das sehr leicht (wenn man Erfahrung mit AWK hat) auswählen können.

Viele Grüße
Sebastian Waschik

Sebastian_Moser · 10. November 2019 um 15:58

Hallo,

Ich habe mir eine CSV aus dem Web heruntergeladen und zwar
die:
http://ip-to-country.webhosting.info/downloads/ip-to…

die Datei hat wirklich mehr als 65535 Datensätze.
Genaugenommen sind es 87810. Das Openoffice hier macht auch
nur den ersten Teil der Daten auf. In deiner Fehlermeldung
wurde ja schon vorgeschlagen Access (sofern vorhanden) zu
benutzen.

Leider habe ich hier kein Access installiert.

Aber du hast nicht geschrieben, was du genau machen möchtest.
Wenn es dir nur darum geht die Datei anzuschauen, sollte ja
ein beliebiger Texteditor reichen. Wenn es darum geht einen
bestimmten Bereich zu finden kommst du wohl an einer
Datenbank-Anwendung oder etwas selbst programmierten nicht
herum. Wenn ich darin eine bestimmte IP suchen wollte, würde
ich erst einmal herausfinden, die wie IP-Adressen in die
entsprechenden Zahlen umgewandelt werden, da es da mehrere
Möglichkeiten gibt. Ich würde es erst einmal versuchen, ob
die IP-Adresse sozusagen im Bigendian-Format umgewandelt wird
(erster Teil höherwertigste), aber es kann auch anders sein.
Wenn das klar ist, müsste man beispielsweise mit AWK das sehr
leicht (wenn man Erfahrung mit AWK hat) auswählen können.

Eigentlich wollte ich die Datei öffnen und einige Formeln einfügen, jedoch kann ja Excel keine CSV-Dateien darstellen, die > 65536 Zeilen haben.

Ich habs jetzt über PHP geöffnet und komme auch auf dein Ergebnis mit den 87810 Zeilen.

Stimmt das mit dem Text-Editor hätte ich auch drauf kommen können, sorry für meine blöde Frage.

Dachte es gibt vielleicht die Möglichkeit über Excel die entsprechenden 65537 - 87810 Zeilen anzuzeigen.

Gruss und besten Dank.
Sebastian

Sebastian_Moser · 10. November 2019 um 15:58

Hallo Felix.

Es würden mehr darin stecken, das ist aber für Excel zuviel.
Eine CSV-Datei ist ja reiner Text, das kann beliebig lang
sein.

Stimmt, danke dir.

Gruss Sebastian

Felix_W_9c131d · 10. November 2019 um 15:58

Hallo,

Dachte es gibt vielleicht die Möglichkeit über Excel die
entsprechenden 65537 - 87810 Zeilen anzuzeigen.

Dazu schreibt ja die Fehlermeldung, dass du die restlichen Zeilen in eine zweite Tabelle kopieren könntest.

Cheers, Felix

Sebastian_Moser · 10. November 2019 um 15:58

Hallo,

Dachte es gibt vielleicht die Möglichkeit über Excel die
entsprechenden 65537 - 87810 Zeilen anzuzeigen.

Dazu schreibt ja die Fehlermeldung, dass du die restlichen
Zeilen in eine zweite Tabelle kopieren könntest.

und wie, wenn ich nur auf OK klicken kann?

Gruss
Sebastian

Reinhard_fb8cb7 · 10. November 2019 um 15:59

Vba: Übergroße csv - Datei in Excel einlesen

Dachte es gibt vielleicht die Möglichkeit über Excel die
entsprechenden 65537 - 87810 Zeilen anzuzeigen.

Dazu schreibt ja die Fehlermeldung, dass du die restlichen
Zeilen in eine zweite Tabelle kopieren könntest.

und wie, wenn ich nur auf OK klicken kann?

Hi Sebastian,

nimm aus der Symbolleiste eine Schaltfläche und weise dieser das nachstehende Makro zu.
Dann klicke auf die Schaltfläche.

Sicher, der Umweg über Access wäre vielleicht eleganter, aber da kenne ich mich so schon nicht aus, geschweige dann da Vba zu basteln.
Nicht mal nen Makrorekorder hat Access

Gruß
Reinhard

Sub TeileCsv()
Dim S, Satz, Anz As Long, Zei As Long, Teil As Long, FF As Integer, N As Long
Const Pfad As String = "H:\"
Const Dat As String = "ip-to-country.csv"
Application.ScreenUpdating = False
On Error GoTo Ende
FF = FreeFile
Application.StatusBar = "Lese csv-Datei ein"
Open Pfad & Dat For Binary As #FF
S = Input(FileLen(Pfad & Dat), #FF)
Close #FF
Satz = Split(S, Chr(10))
For N = 0 To UBound(Satz)
 If N Mod 65536 = 0 Then
 Close
 FF = FreeFile
 Teil = Teil + 1
 Open Pfad & "Teil" & Teil & ".csv" For Output As #FF
 End If
 Print #FF, Satz(N)
 Application.StatusBar = "Schreibe in neue csv-Datei Satz " & N & " von " & UBound(Satz)
Next N
Close
For N = 1 To Teil
 Application.StatusBar = "Kopiere in diese Mappe Blatt " & N & " von " & Teil
 Workbooks.Open Pfad & "Teil" & N & ".csv"
 ActiveSheet.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
 Workbooks("Teil" & N & ".csv").Close savechanges:=False
 Application.StatusBar = "TextinSpalten für Blatt " & N & " von " & Teil
 Columns(1).TextToColumns Destination:=Range("A1"), Comma:=True
Next N
Ende:
Application.StatusBar = ""
Application.ScreenUpdating = True
End Sub

Felix_W_9c131d · 10. November 2019 um 15:59

Hallo,

Dachte es gibt vielleicht die Möglichkeit über Excel die
entsprechenden 65537 - 87810 Zeilen anzuzeigen.

Dazu schreibt ja die Fehlermeldung, dass du die restlichen
Zeilen in eine zweite Tabelle kopieren könntest.

und wie, wenn ich nur auf OK klicken kann?

Genau wie es in der Fehlermeldung steht: Öffne die CSV-Datei mit Word, kopiere einen Teil der Zeilen auf eine Tabelle und den Rest in die Zweite.

Cheers, Felix

Sebastian_Moser · 10. November 2019 um 16:01

Hallo.

Genau wie es in der Fehlermeldung steht: Öffne die CSV-Datei
mit Word, kopiere einen Teil der Zeilen auf eine Tabelle und
den Rest in die Zweite.

Nervig das die Excel-Zeilen auf 2 Byte beschränkt sind.
Zum Glück ist das ab Excel 2007 Geschichte.
Gruß Sebastian

Sebastian_Moser · 10. November 2019 um 16:02

Hallo Reinhard.

Danke dir für deine Antwort und für deine Sub.

> Sub TeileCsv()  
> End Sub

Die Sub funktioniert super.
Es sollte noch erwähnt werden, dass die Teile auf pfad = „H:“ abgelegt werden und zwar Teil 1 - X!!

Gruss Sebastian

Reinhard_fb8cb7 · 10. November 2019 um 16:02

Danke dir für deine Antwort und für deine Sub.
Die Sub funktioniert super.
Es sollte noch erwähnt werden, dass die Teile auf pfad = „H:“
abgelegt werden und zwar Teil 1 - X!!

Hallo Sebastian,

nachfolgend neuer Code, der einen Tick schneller laufen sollte.
Oben im Code bei den Zeilen die mit Const… beginnen kannst du den Code anpassen.
Pfad und Dateiname sind klar.
Die 3.000.000 ist die Teilgröße.
Jede Zeile in deiner csv hat ca 50 Bytes Länge, wenn ich also deine csv aufteile in Teile a 3.000.000 Bytes, so habe ich ca. 3.000.000 / 50 = 60.000 Zeilen pro Teil-csv.

Die im Code erzeugten Teile (Teil1.csv, Teil2.csv,usw.) kannst du entfernen lassen wenn du sie nicht weiter benötigst.
Dazu nur das Hochkomma vor dem Kill-Befehl entfernen.

Und, ich habe mal manuell deine csv in Access einlesen lassen, das dauert auch recht lange.
Das Aufteilen in verschiedene Teil-Csv geht fix, was da dauert ist das Einlesen jeder Teil-csv durch Excel und dann das darauf folgende TextinSpalten durch Excel.
Excelintern kann ich nix beschleunigen.

Sub TeileCsv()
Dim S, Satz, Anz As Long, Zei As Long, Teil As Long, FF As Integer, N As Long
Dim Pos As Long
Const Laenge As Long = 3000000
Const Pfad As String = "H:\"
Const Dat As String = "ip-to-country.csv"
Application.ScreenUpdating = False
'On Error GoTo Ende
FF = FreeFile
Open Pfad & Dat For Binary As #FF
S = Input(FileLen(Pfad & Dat), #FF)
Close #FF
Do
 Pos = InStr(Laenge, S, Chr(10))
 If Pos \> 0 Then
 Satz = Left(S, Pos - 1)
 Else
 Satz = S
 End If
 S = Mid(S, Len(Satz) + 2)
 Teil = Teil + 1
 FF = FreeFile
 Open Pfad & "Teil" & Teil & ".csv" For Output As #FF
 Print #FF, Satz
 Close #FF
Loop While Len(S) \> 0
For N = 1 To Teil
 Application.StatusBar = "Kopiere in diese Mappe Blatt " & N & " von " & Teil
 Workbooks.Open Pfad & "Teil" & N & ".csv"
 ActiveSheet.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
 Workbooks("Teil" & N & ".csv").Close savechanges:=False
 Application.StatusBar = "TextinSpalten für Blatt " & N & " von " & Teil
 Columns(1).TextToColumns Destination:=Range("A1"), Comma:=True
 'Kill Pfad & "Teil" & N & ".csv"
Next N
Ende:
Close
Application.StatusBar = ""
Application.ScreenUpdating = True
End Sub

Gruß
Reinhard