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

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.

Moderator Note:

Pls use code tags around your code next time as per forum rules.