Opening CSV file with 69000 + rows in Excel using VBA

[Guest]

Hi all,

I have to open csv files with possibly more than 65000 rows of data in them,
and all the rows greater than 1 worksheet have to be put on the next
sheet...when that is full I have to add another sheet and so on...

Due to the limitations of Excel, I can't even tell the text Import
(Querytables) method to start at row 65000 as the textFileStartRow parameter
is supposed to be an integer (DOH !) - who came up with that one?

So, what is the best option, open the files using OLEDB Text Provider in ADO
and load from recordset to read them in chunks of 65000 odd records, or is
there a better way?

Or should I use the Textfile Import Method, and read it in in chunks of say
30000 by setting the textFileStartRow property to current Value + 30000 for
each iteration until completed?

thanks for any help or ideas or sympathy...

Philip

 

[Guest]

Any possibility to drop the CSV into MS Access and then query the data out
from there. If not then you are left with reading the text file one line at a
time and using the split function. Then pasting the array generated by the
split function into the sheet, incrementing the sheet as necessary... That is
kinda slow and ugly though... Access would be a much better option. You could
even hook a pivot table up to the Access database if you want. A pivot coming
out of Access is good for at least about 650,000 records with reasonable
performance.

HTH

 

[Tom Ogilvy]

Using ADO would probably be an excellent way.

 

[Guest]

Hi,

How could I create that Access db on the fly at runtime, use it to load and
handle the data, then destroy it?

I tried using the Access 9 Library in VBA, and there is no 'dim oDB as New
Database' option...

Is that possible?

 

[Guest]

Thanks, would that be using the OLE DB Text Provider, or Jet do you think?

thanks

Philip

 

[Guest]

I don't have any code handy but I did do something similar a long time ago.
Try hitting up the Access site and they should be able to give you a hand.
They have helped me in the past and they are great...

HTH

 

[Tom Ogilvy]

If your going to use ADO, there is no need to involve Access.

 

[Guest]

Knowing now that he is willing to toss the data out you are correct. My
original assumption was the he would want to store the data, and if it could
be stored in one place that is usually best (instead of getting multiple
sheet involved). I then got myself on a one track (Access) solution, similar
to what I did for a previous project. I guess what I am tryin to say is you
are absolutely correct. ADO will work great in this instance.

 

[Tom Ogilvy]

see this article for sample code:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting03092004.asp

 

[Thomas Ramel]

Grüezi

Philip schrieb am 22.03.2005

I have to open csv files with possibly more than 65000 rows of data in them,
and all the rows greater than 1 worksheet have to be put on the next
sheet...when that is full I have to add another sheet and so on...

Or should I use the Textfile Import Method, and read it in in chunks of say
30000 by setting the textFileStartRow property to current Value + 30000 for
each iteration until completed?

I recently wrote the following code to import large .txt-files and split
them to columns, depending on the separator-character.
It's the semicolon here, but you can switch it easily in the second part of
the code.
(Moste comments ar in German, unfortunately)

Option Explicit
Option Base 1

Sub LargeFileImport()

Dim FileName As String
Dim FileNum As Integer
Dim ResultStr As String

Dim wsSheet As Worksheet
Dim strValues() As String

Dim lngRows As Long
Dim lngRow As Long
Dim intSheet As Integer
Dim intCounter As Integer

FileName = Application.GetOpenFilename("Textdateien " & _
"(*.txt; *.csv;*.asc),*.txt; *.csv; *.asc")

If FileName = "" Or FileName = "False" Then Exit Sub
FileNum = FreeFile()

Open FileName For Input As #FileNum
Application.ScreenUpdating = False
Workbooks.Add template:=xlWorksheet

lngRows = ActiveSheet.Rows.Count
lngRow = 1
intSheet = 1
ReDim strValues(lngRows, 1)

Application.StatusBar = " Loading Sheet " & intSheet & " / 0 %"

Do While Seek(FileNum) <= LOF(FileNum)
Line Input #FileNum, ResultStr
If Left(ResultStr, 1) = "=" Then
strValues(lngRow, 1) = "'" & ResultStr
Else
strValues(lngRow, 1) = ResultStr
End If
If lngRow < lngRows Then
lngRow = lngRow + 1
If (lngRow * 100 / lngRows) Mod 10 = 0 Then
Application.StatusBar = " Loading Sheet " & intSheet & _
" / " & Int(lngRow * 100 / lngRows) & " %"
End If
Else
Application.StatusBar = " Writing Sheet " & intSheet
ActiveSheet.Range("A1:A" & lngRows) = strValues
ActiveWorkbook.Worksheets.Add after:=Worksheets(Worksheets.Count)

ReDim strValues(lngRows, 1)
lngRow = 1
intSheet = intSheet + 1
Application.StatusBar = " Loading Sheet " & intSheet
End If
Loop
Close
ActiveSheet.Range("A1:A" & lngRows) = strValues

' Beginn der Aufteilung in Spalten
' Im unteren Bereich kann das Trennzeichen festgelegt werden, indem der
' entsprechende Eintrag auf 'True' gesetzt wird
If MsgBox("Sollen die eingelesenen Daten auf Spalten verteilt werden?", _
vbYesNo, "Text in Spalten") = vbNo Then
Application.ScreenUpdating = True
Application.StatusBar = "Fertig"
Exit Sub
End If

intSheet = 0
For Each wsSheet In ActiveWorkbook.Worksheets
intSheet = intSheet + 1
Application.StatusBar = "Bearbeiten von Blatt " & intSheet
With wsSheet
.Range("A:A").TextToColumns Destination:=.Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=True, _
Comma:=False, _
Space:=False, _
Other:=False
End With
Next wsSheet
Application.ScreenUpdating = True
Application.StatusBar = "Fertig"
End Sub



Regards
Thomas Ramel

 

[Guest]

Thanks guys, ADO it is... using a Schema.ini file (created using ODBC) and
the MSDASQL.1 Provider...

works a treat in simple tests, I can load the file into a recordset in less
than 2 seconds, use Excels CopyFromRecordset method to copy 65536 records at
a time, and if I need to specify an exact number of records for the last
sheet, I can use Recordset.Count mod 65536 to work out how many records are
left and plug that into the last CopyFromrecordset call...

Perfect,

thanks all

Philip

 

[Jamie Collins]

Sub Test()
Dim Cat As Object

' Create Jet DB
Set Cat = CreateObject("ADOX.Catalog")
With Cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\New_Jet_DB.mdb"

' Use Jet DB
With .ActiveConnection
.Execute "CREATE TABLE Test(data_col VARCHAR(50) NOT NULL);"
End With

.ActiveConnection = Nothing
End With

' Destroy Jet DB
Kill "C:\New_Jet_DB.mdb"

End Sub

Jamie.

--