Excel VBA - CopyFromRecordset (ADO) Problems with 107000 records

[Guest]

Hi,

I have an ADO recordset with 107000+ records (loaded from text file using
schema.ini file and ADBC Text Driver).

I need to get this data into Excel sheets, first, copy 65535 (1st row=column
heads), then keep copying 65535 onto new worksheets (1st row=column heads)
until there are no records left.

There could be many hundreds of thousands of records, perhaps up to 700000
.... so I can't just hard code it to do 2 or 3 Copies

Unfortunately on the second time I use the CopyFromRecordset method it
fails, and for some reason the recordset 'AbsolutePosition' property is set
to -3, even though I can get the recordcount...

Here is my codeim oConn As New ADODB.Connection
Dim oRec As New ADODB.Recordset

Dim iNoRec As Long
Dim iCount As Long
Dim iCurrRec As Long

Dim iNoSheets As Integer, iSheet As Integer, iFlds As Integer

Dim sQry As String, sColumnHeads As String

oConn.Open "DBQ=G:\EVERYONE\Ad\IPS Group\ddi\;DefaultDir=G:\EVERYONE\Ad\IPS
Group\ddi\;Driver={Microsoft Text Driver (*.txt;
*.csv)};DriverId=27;Extensions=asc,csv,tab,txt;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"

sQry = "select FUNDCODE, CLIENT_SPARE, FIN_STMT_CURRCY, ACCOUNT, SUBACCOUNT,
LOCAL_CURRENCY, BASIS," _
& "CD_ACTIVITY_SIGN + CD_ACTIVITY as CD_ACTIVITY1, CY_ACTIVITY_SIGN +
CY_ACTIVITY as CY_ACTIVITY1, PROC_RATIO, DATE_ADDED," _
& "TIME_ADDED, ADD_PROGRAM_ID, DATE_UPDATED, TIME_UPDATED,
UPD_PROGRAM_ID from l902glm#txt"

With oRec
.CursorLocation = adUseClient
.Open sQry, oConn

Sheets("Sheet1").Name = "l902glm_1"

For iFlds = 0 To .Fields.Count - 1
Sheets("l902glm_1").Cells(1, iFlds + 1).Value = .Fields(iFlds).Name
Next
End With

iCurrRec = 1
iSheet = 1
iNoRec = oRec.RecordCount
oRec.MoveFirst
Sheets("l902glm_" & iSheet).Select
Range("A2").Select

Do While Not iCurrRec > iNoRec
oRec.Move iCurrRec - 1
ActiveCell.CopyFromRecordset oRec, 65536
If oRec.RecordCount >= iCurrRec Then
Sheets.Add After:=Sheets("l902glm_" & iSheet)
iSheet = iSheet + 1
Sheets(Sheets.Count).Name = "l902glm_" & iSheet
iCurrRec = iCurrRec + 65535
Sheets("l902glm_" & IIf(iSheet > 1, iSheet - 1, 1)).Activate

' copy column/field names ... to next sheet
Sheets("l902glm_" & IIf(iSheet > 1, iSheet - 1, 1)).Range(Cells(1,
1), Cells(1, oRec.Fields.Count)).Copy Destination:=Sheets("l902glm_" &
iSheet).Range("A1")
Sheets("l902glm_" & iSheet).Select
Range("A2").Select
End If
Loop
'MsgBox oRec.RecordCount
'oRec.Move 65537

oRec.Close
oConn.Close
I'd be grtateful for any help, ideas, improvements, solutions, or even some
sympathy...

thanks

Philip

 

[Fredrik Wahlgren]

Hi,

I have an ADO recordset with 107000+ records (loaded from text file using
schema.ini file and ADBC Text Driver).

I need to get this data into Excel sheets, first, copy 65535 (1st row=column
heads), then keep copying 65535 onto new worksheets (1st row=column heads)
until there are no records left.

There could be many hundreds of thousands of records, perhaps up to 700000
... so I can't just hard code it to do 2 or 3 Copies

Unfortunately on the second time I use the CopyFromRecordset method it
fails, and for some reason the recordset 'AbsolutePosition' property is set
to -3, even though I can get the recordcount...

Here is my code:
Dim oConn As New ADODB.Connection
Dim oRec As New ADODB.Recordset

Dim iNoRec As Long
Dim iCount As Long
Dim iCurrRec As Long

Dim iNoSheets As Integer, iSheet As Integer, iFlds As Integer

Dim sQry As String, sColumnHeads As String

oConn.Open "DBQ=G:\EVERYONE\Ad\IPS Group\ddi\;DefaultDir=G:\EVERYONE\Ad\IPS
Group\ddi\;Driver={Microsoft Text Driver (*.txt;
*.csv)};DriverId=27;Extensions=asc,csv,tab,txt;FIL=text;MaxBufferSize=2048;M
axScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommi
tSync=Yes;"

sQry = "select FUNDCODE, CLIENT_SPARE, FIN_STMT_CURRCY, ACCOUNT, SUBACCOUNT,
LOCAL_CURRENCY, BASIS," _
& "CD_ACTIVITY_SIGN + CD_ACTIVITY as CD_ACTIVITY1, CY_ACTIVITY_SIGN +
CY_ACTIVITY as CY_ACTIVITY1, PROC_RATIO, DATE_ADDED," _
& "TIME_ADDED, ADD_PROGRAM_ID, DATE_UPDATED, TIME_UPDATED,
UPD_PROGRAM_ID from l902glm#txt"

With oRec
.CursorLocation = adUseClient
.Open sQry, oConn

Sheets("Sheet1").Name = "l902glm_1"

For iFlds = 0 To .Fields.Count - 1
Sheets("l902glm_1").Cells(1, iFlds + 1).Value = ..Fields(iFlds).Name
Next
End With

iCurrRec = 1
iSheet = 1
iNoRec = oRec.RecordCount
oRec.MoveFirst
Sheets("l902glm_" & iSheet).Select
Range("A2").Select

Do While Not iCurrRec > iNoRec
oRec.Move iCurrRec - 1
ActiveCell.CopyFromRecordset oRec, 65536
If oRec.RecordCount >= iCurrRec Then
Sheets.Add After:=Sheets("l902glm_" & iSheet)
iSheet = iSheet + 1
Sheets(Sheets.Count).Name = "l902glm_" & iSheet
iCurrRec = iCurrRec + 65535
Sheets("l902glm_" & IIf(iSheet > 1, iSheet - 1, 1)).Activate

' copy column/field names ... to next sheet
Sheets("l902glm_" & IIf(iSheet > 1, iSheet - 1, 1)).Range(Cells(1,
1), Cells(1, oRec.Fields.Count)).Copy Destination:=Sheets("l902glm_" &
iSheet).Range("A1")
Sheets("l902glm_" & iSheet).Select
Range("A2").Select
End If
Loop
'MsgBox oRec.RecordCount
'oRec.Move 65537

oRec.Close
oConn.Close

I'd be grtateful for any help, ideas, improvements, solutions, or even some
sympathy...

thanks

Philip

Are you sure you need every darn record? Wouldn't it make sense to use the
WHERE clause to limit the records you get?

/Fredrik

 

[RB Smissaert]

Might be worth it to describe first what exactly has to be done as there may
be other methods to achieve the same.
Maybe you even don't need a recordset for example if you could do the job
with low level file IO functions.
Another option is to transfer the recordset to an array with GetRows and
then handle the array.
A third option is to use Access as this would bypass the 65536 row
limitation.

RBS

 

[Guest]

Hi,

Well, files containing 65536+ records need to be imported into MS Excel for
checking.

I agree that Access would be a nice option, but it isn't an option as the
person checking the data won't have MS Access.

So I decided to use ADO as File I/O is unbelievably slow - for example, why
read a file line by line when you can read the entire file using ADO / ODBC
in 3 seconds then use Excels 'CopyFromrecordset' method to dump it into MS
Excel...where the data has to be.

Anyway, I solved it... it turns out that the behaviour (seems to be
undocumented!) of the CopyFromRecordset method is to take the first 65536
records, and move the recordset pointer to the first unread record...

so in fact all I have to do is loop unti EOF, and in the loop add a new
sheet, rename it, and call CopyFromrecordst again...

nice!

Thanks anyway

Philip

 

[Fredrik Wahlgren]

Hi,

Well, files containing 65536+ records need to be imported into MS Excel for
checking.

I agree that Access would be a nice option, but it isn't an option as the
person checking the data won't have MS Access.

So I decided to use ADO as File I/O is unbelievably slow - for example, why
read a file line by line when you can read the entire file using ADO / ODBC
in 3 seconds then use Excels 'CopyFromrecordset' method to dump it into MS
Excel...where the data has to be.

Anyway, I solved it... it turns out that the behaviour (seems to be
undocumented!) of the CopyFromRecordset method is to take the first 65536
records, and move the recordset pointer to the first unread record...

so in fact all I have to do is loop unti EOF, and in the loop add a new
sheet, rename it, and call CopyFromrecordst again...

nice!

Thanks anyway

Maybe this solves th problem in a way. Maybe there's an easier way. I have
never used SQL to get data from a txt file,anyway depending on what kind of
checking you are doing, maybe it's possible to make a smarter SQL statement
that selects the records that have some kind of error in them.

So.. What kind of checking do you do?

/Fredrik

 

[RB Smissaert]

OK, thanks for the feedback.
I am not sure file IO will always be slow.
You can read the whole file to a string variable without
any looping, but then of course you haven't got your data
in Excel yet.
Will keep that behaviour of CopyFromRecordset in mind.

RBS

 

[RB Smissaert]

Even when you use File I/O with loops it seems pretty quick.
Just tried it with this on a 30000 row 6 column text file and it did it in
0.3 secs


Function OpenTextFileToArray(ByVal txtFile As String, _
ByRef arr As Variant, _
ByVal LBRow As Long, _
ByVal UBRow As Long, _
ByVal LBCol As Long, _
ByVal UBCol As Long, _
Optional ByVal bSkipFields As Boolean = False)
As Variant

Dim hFile As Long
Dim R As Long
Dim c As Long
Dim varWaste

hFile = FreeFile

Open txtFile For Input As #hFile

On Error Resume Next

If bSkipFields = False Then
For R = LBRow To UBRow
For c = LBCol To UBCol
Input #hFile, arr(R, c)
Next
Next
Else
For c = LBCol To UBCol
Input #hFile, varWaste
Next
For R = LBRow To UBRow
For c = LBCol To UBCol
Input #hFile, arr(R, c)
Next
Next
End If

Close #hFile

OpenTextFileToArray = arr

End Function


RBS

 

[Jamie Collins]

I have an ADO recordset with 107000+ records (loaded from text file using
schema.ini file and ADBC Text Driver).

I need to get this data into Excel sheets, first, copy 65535 (1st row=column
heads), then keep copying 65535 onto new worksheets (1st row=column heads)
until there are no records left.

Here's a suggestion (thanks TK). Using Excel's CopyFromRecordset on a
recordset with more records than the 65536 maximum worksheet rows does
not cause the method to fail. Rather, the cursor is merely moved e.g.
to record 65537. Therefore, you could do something like this:

Sub test()
Dim rs As Object
Set rs = CreateObject("ADOR.Recordset")
rs.Open _
"SELECT * FROM 100K_row_table;", _
"Provider=Microsoft.Jet.OLEDB.­4.0;" & _
"Data Source=C:\Tempo\New_Jet_DB.mdb­"

Dim Counter As Long
With Workbooks("MyWorkbook.xls")
Do While Not rs.EOF
Counter = Counter + 1
.Worksheets(Counter).Range("A1­") _
.CopyFromRecordset rs
Loop
End With
End Sub


Jamie.

--

 

[Guest]

Hi,

Yes I need every darn record....

The users need to be able research discrepancies, so they need to see all of
the source records...

Nice to know that straight file i/o is pretty fast too ... that's good for
when ADO / ODBC is not available for whatever reason...

But the CopyFromRecordset method just seems to me to be more elegant...and
this undocumented behaviour is very helpful too - definitely something to
remember I think!

So thanks all, it's looking good now, just tested with 710000 records and
it's very fast...

regards

Philip

: