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

SeriousDeveloper

New Member
Joined
Dec 20, 2013
Messages
2
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 -

Code:
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.
 
Last edited by a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,351
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
I suspect you probably need 64bit Excel, or perhaps Powerpivot.
 

SeriousDeveloper

New Member
Joined
Dec 20, 2013
Messages
2
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

Well-known Member
Joined
Nov 15, 2013
Messages
524
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
 

RickG

New Member
Joined
Apr 25, 2014
Messages
1
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.
 

Forum statistics

Threads
1,082,258
Messages
5,364,090
Members
400,779
Latest member
lumers

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top