Facing "out of memory" issue while importing data from a .csv file to create Pivot Cache

[SeriousDeveloper]

Hi All,

I need a help on this issue which we are facing.

We are trying to create pivot cache from an external .csv file.

This works fine when the csv file is small (<500 mb).
But it given "out of memory" error for bigger .csv files(>~500 mb).

Attaching sample code for reference -

Dim cConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset
Dim pvtCache As PivotCache
Dim pvtTable As PivotTable
 Dim SQL As String

''Creating ADODB connection object
 Set cConnection = New ADODB.Connection
 cConnection.Open sConnStrP1 & sFilePath & sConnStrP2

'' where
'''Const sConnStrP1 = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="
'''Const sConnStrP2 = ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
'''Const sFilter = "CSV File, *.csv"


  Set rsRecordset = Nothing
    Set rsRecordset = New ADODB.Recordset
    Set rsRecordset = cConnection.Execute(SQL)
            
''Creating pivot cache from csv file
    Set pvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal)
    Set pvtCache.Recordset = rsRecordset

''Using Pivot cache to create pivot table
 arrTmp = Array("NoRow")
 Set pvtTable = createPivotTable(arrTmp, wsTemp.Range("f15"), pvtCache)
 pvtTable.Name = "ptbl_1"

Is there a way we can solve this issue. If not what is the best way to create a pivot table for >4-5 millions records.

It will be great help if someone can put some helpful pointers.

 

[RoryA]

I suspect you probably need 64bit Excel, or perhaps Powerpivot.

 

[SeriousDeveloper]

I suspect you probably need 64bit Excel, or perhaps Powerpivot.

Thanks for the prompt response RoryA.

Unfortunately 64 bit excel does not supports ADODB connection object. Powerpivot will require us to force our users to download it on their machines.
There has to be some other way for this??

 

[jsotola]

excel worksheet has a limit of 1048576 rows

you would have to split your data

also, your code does not show the SQL string, have you checked if it is correct

 

[RoryA]

Unfortunately 64 bit excel does not supports ADODB connection object.

What makes you say that? (it's not true)

 

[RickG]

excel worksheet has a limit of 1048576 rows

you would have to split your data

also, your code does not show the SQL string, have you checked if it is correct

The worksheet has a limit of 1,048,576 rows, but this limit does not apply to a PivotCache. I've had PivotCaches with upwards of 14,000,000 records for some apps. If one points a PivotTable to an external data source, such as a MySQL or SQLServer query, the returned PivotCache becomes part of the workbook, even though it cannot be displayed in its entirety within any single worksheet.

I am having a problem converting one app from using MS SQLServer to MySQL as a data source. The 13 MM record dataset comes back from MS SQL without a problem. The same table sourced from MySQL causes and Excel out of memory error at 10.6 MM records.