Pivot Table based on CSV file.

 In my line of business we work a lot with data from numerous suppliers.
Some of the data is provided in csv files which can be imported in excel to base a pivot table on.
But what if the number of records / lines exceeds 65536 in excel 2003 or 1048576 in excel 2007.
You can cut the csv file in parts and base the pivot table on multiple consolidation ranges.
This was too much of a hassle for me and I started thinking about a more structured way to
create a pivot table on a csv file, with this result…
What is does is in fact simple but beautiful.

Select CSV File, create a recordset from it and attach it to the pivot cache to base a new
pivot table on this cache.


Option Explicit

'Set Reference to Microsoft ActiveX Data Objects 2.7 Library

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"

Sub CreatePivotTableFromCSV()

Dim sFileName As String
  Dim sFilePath As String

sFileName = Application.GetOpenFilename(sFilter, 1, "Select File", , False)

sFilePath = Left(sFileName, InStrRev(sFileName, "\"))
  sFileName = Replace(sFileName, sFilePath, "")

TestCSV sFilePath, sFileName

End Sub

Sub TestCSV(ByVal sFilePath As String, ByVal sFileName As String)

Dim cConnection As ADODB.Connection
  Dim rsRecordset As ADODB.Recordset

Dim pcPivotCache As PivotCache
  Dim ptPivotTable As PivotTable

Dim SQL As String

Set cConnection = New ADODB.Connection
  cConnection.Open sConnStrP1 & sFilePath & sConnStrP2

SQL = "SELECT * FROM " & sFileName

Set rsRecordset = New ADODB.Recordset
  Set rsRecordset = cConnection.Execute(SQL)

'For Excel 2003 Use
  'Set pcPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
  'For Excel 2007 Use
  Set pcPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
  Set pcPivotCache.Recordset = rsRecordset

Set ptPivotTable = pcPivotCache.CreatePivotTable(TableDestination:=Range("B5"))

cConnection.Close

Set rsRecordset = Nothing
  Set cConnection = Nothing

End Sub

Cheers

Comments are more than welkom

 Very Nice!!

I just tried it out with a 262,000 record csv file in XL2003 and it worked like a dream.
Many thanks for sharing this elegant solution.

kaak,

Interesting approach.  I'll have to try it out. 

My answer has always been to use the Pivot Table Wizard and External Data Source.  I can then use the Query wizard to get the data.  Works great on my 80K record csv.

David B.

Thanks for the comments

I never tried the Query wizzard approach, I will look in to that.

But is it a alternatieve for giving the user a pivot table with data for the day in a easy way.

p.s. I wrote the select file part to demo the code