Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Pivot Table based on CSV file.

Latest post Mon, Jul 12 2010 10:12 AM by Juracy Americo. 8 replies.
  • Thu, Jul 31 2008 9:39 AM

    • kaak
    • Top 25 Contributor
    • Joined on Fri, Jan 18 2008
    • Edam, Netherlands
    • Posts 73
    • Points 1,272

    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

    • Post Points: 37
  • Thu, Jul 31 2008 11:35 AM In reply to

    • Roger Govier
    • Top 10 Contributor
    • Joined on Wed, Jan 9 2008
    • Abergavenny, South Wales, UK
    • Posts 273
    • Points 4,330
    • MVP

    Re: Pivot Table based on CSV file.

     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.

    Regards
    Roger Govier
    Microsoft Excel MVP

    • Post Points: 5
  • Fri, Aug 1 2008 8:32 AM In reply to

    Re: Pivot Table based on CSV file.

    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.

    • Post Points: 21
  • Mon, Aug 4 2008 1:58 AM In reply to

    • kaak
    • Top 25 Contributor
    • Joined on Fri, Jan 18 2008
    • Edam, Netherlands
    • Posts 73
    • Points 1,272

    Re: Pivot Table based on CSV file.

    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 

    • Post Points: 21
  • Sat, Apr 17 2010 7:43 PM In reply to

    Re: Pivot Table based on CSV file.

    Hey Kaak thank you for share your code.

     

    Work realy well.

    Kaak do you think is possible to use the same code with text file delimited by a " | " ?

    I tried here but all column sitck together...

     

    Can you help me?

     

     

    Thank you in advance

     

    Juracy Americo

    from Brazil

    • Post Points: 21
  • Mon, Apr 19 2010 2:00 PM In reply to

    • Roger Govier
    • Top 10 Contributor
    • Joined on Wed, Jan 9 2008
    • Abergavenny, South Wales, UK
    • Posts 273
    • Points 4,330
    • MVP

    Re: Pivot Table based on CSV file.

    Hi Juracy

    You could open your file in Notepad first, then do a Find & Replace of " | " with " , "
    Save the file as a txt filem then proceed as per Kaak's instructions.

    Regards
    Roger Govier
    Microsoft Excel MVP

    • Post Points: 21
  • Mon, Apr 19 2010 6:03 PM In reply to

    Re: Pivot Table based on CSV file.

    Hi Roger, thank you for your reply.

     

    But I tried that, but is not a good idea because my file have 300 G, it is so big.

     

    Any more ideas?

     

    thank you

    • Post Points: 21
  • Mon, Jul 12 2010 7:14 AM In reply to

    • Jesper
    • Top 500 Contributor
    • Joined on Wed, Jun 16 2010
    • Posts 4
    • Points 52

    Re: Pivot Table based on CSV file.

    Hi there!

    I'm pretty new in this forum - and I realize it's a rather old thread - but still I'd like to add a few comments to this issue.

    First, to import a file where the delimiter is | you could try to go controlpanel, regional settings see if you can alter the 'list separator' to | that should do the trick for you. I am in a danish version of XP, sorry I can't be more specific about where to alter the settings.

    Second, if you import data from csv or other text-based format, you don't have to open the file and you don't have to worry about the number of observations. If you want to put the data in a pivottable all that matters is, can the number of rows in the pivittable be fitted into the sheet. Whether 1,000 or 1,000,000 it fits perfectly in excel2003 (limited to app. 65,000 rows) if only the number of distinct obs, i.e. rows in the pivottable, does not exceed the 65,000. This should be done through 'import external data' use the MSquery.

    • Post Points: 21
  • Mon, Jul 12 2010 10:12 AM In reply to

    Re: Pivot Table based on CSV file.

    Hi Jesper!You are right.

     

    Thank you for your reply, very helpful .

     

    Juracy Americo

    • Post Points: 5
Page 1 of 1 (9 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.