Attached is small utility to create a Pivot table from a CSV file. There is a button provided on the click of which you are asked to choose the csv file and then the target cell/range where you want to place your pivot.

This is the macro code working behind the scene...

Sub CreatePivotTableFromCSV()

    Dim strFileName     As String

    Dim strFilePath     As String

    Dim rngTarget       As Range

    strFileName = Application.GetOpenFilename("CSV File, *.csv", 1, "Select CSV File", , False)

    If Not strFileName = "False" Then

        On Error Resume Next

            Set rngTarget = Application.InputBox(prompt:="Target Range", Type:=8)

        On Error GoTo 0

        If Not rngTarget Is Nothing Then

            Call TestCSV(strFileName, rngTarget)

        End If

    End If

    Set rngTarget = Nothing

End Sub

Sub TestCSV(ByVal strFullFilePath As String, rngPvtTarget As Range)

    Dim conADO        As Object

    Dim rstADO        As Object

    Dim pvtCache      As PivotCache

    Dim pvtTable      As PivotTable

    Dim strSQL        As String

    Dim strFileName   As String

    Dim strFilePath   As String

    strFilePath = Left(strFullFilePath, InStrRev(strFullFilePath, "\"))

    strFileName = Replace(strFullFilePath, strFilePath, "")

    Set conADO = CreateObject("ADODB.Connection")

    conADO.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _

                    & strFilePath _

                    & ";Extensions=asc,csv,tab,txt;Persist Security Info=False"

    strSQL = "SELECT * FROM " & strFileName

    Set rstADO = CreateObject("ADODB.Connection")

    Set rstADO = conADO.Execute(strSQL)

    'Excel 2003

    If Application.Version < 12 Then

        Set pvtCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)

    Else

    '>= Excel 2007 Use

        Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)

    End If

    Set pvtCache.Recordset = rstADO

    Set pvtTable = pvtCache.CreatePivotTable(TableDestination:=rngPvtTarget)

    conADO.Close

    Set rstADO = Nothing

    Set conADO = Nothing

    Set pvtCache = Nothing

    Set pvtTable = Nothing

End Sub