none
Generate Pivot Table from CSV file

    Question

  • Hi all,

     

    I am writing a plugin using C# which will generate the pivot table from data stored in an CSV file. I try using the PivotTableWizard object but don't know how to specified the value for the parameter SourceData in this case.

    Please help me out on this problem.

     

    Thank you very much,

    Anh Bui

    Tuesday, March 11, 2008 7:55 AM

Answers

  • Hi Anh,

     

    As far as I test, we cannot create a PivotTable directly from .csv file, even manually. To use .csv file create PivotTable, we need find a bridge for us.

    An approach is, we can configure the ODBC(Data Source) in control panel to add a User DSN with “Driver da Microsoft para arquivos texto”. And set the folder which includes your csv file to be the data base directory.

    After the configuration, we can add a connection in Excel. With the new connection to csv file added, you can easily create a pivot table using that connection as SourceData parameter.

    I record a Macro as follows. Hope it helps!

    Code Snippet

    Sub Macro2()

    '

    ' Macro2 Macro

    '

        Workbooks("Book1").Connections.Add "DESKTOP test.csv", "", _

            "ODBC;DSN=Test;DefaultDir=C:\USERS\V-JZHO\DESKTOP;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;" _

            , Array("SELECT * FROM `C:\USERS\V-JZHO\DESKTOP`\`test.csv`"), 2

        ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _

            ActiveWorkbook.Connections("DESKTOP test.csv"), Version:= _

            xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R1C1", _

            TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion12

        Cells(1, 1).Select

    End Sub

     

     

     

    Thanks,

    Ji

     

    Monday, March 17, 2008 7:14 AM