Home > CSV, Excel, Pivot table > Pivot Table based on CSV file.

Pivot Table based on CSV file.

May 17th, 2011 admin

July 31st, 2008

It’s been a while since my last post.
Due to my new job and rebuilding my house free time was scarce.
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"))


  Set rsRecordset = Nothing
  Set cConnection = Nothing

End Sub


Comments are welkom

Categories: CSV, Excel, Pivot table Tags: , ,
Comments are closed.