Create Excel pivot cache in pywin32 error

614 views Asked by At

I'm trying to create a pivot table in python but can't get it to work on my own data. I've identified a problem cell to be the final one in the example file. The same data works fine when the pivot table is created directly in excel.

Here's the code:

import win32com.client as win32
win32c = win32.constants

excel = win32.gencache.EnsureDispatch('Excel.Application')

wb = excel.Workbooks.Open("test_pivot_cache.xlsx")
excel.Visible = True

data_sheet = wb.Sheets(1)
data_sheet.Activate()
r = data_sheet.UsedRange
n_rows = r.Rows.Count

#first column only
PivotSourceRange1 = data_sheet.Range("A1:A{}".format(n_rows))
PivotCache = wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=PivotSourceRange1, Version=win32c.xlPivotTableVersion14)

#all but last row
PivotSourceRange2 = data_sheet.Range("A1:B{}".format(n_rows - 1))
PivotCache = wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=PivotSourceRange2, Version=win32c.xlPivotTableVersion14)

#all the data
PivotSourceRange3 = r
PivotCache = wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=PivotSourceRange3, Version=win32c.xlPivotTableVersion14)

And the data: https://drive.google.com/file/d/0B8fA-_mtoc2NRnR4ZDF1amNndEU/view?usp=sharing

The problematic string cell contains

* Buying Cycle - Consideration,Car insurance - Young Drivers,Channel - Car Insurance,Language - Phrase,Modifier - Price,Modifier - Price - Cheap,Niche - Car,Niche - Car Hire,Niche - Driver,Niche - Insurance,Persona - Age,Persona - Age - Young,Transport,Transport - Car

The final line gives an error:

pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352571), 3)

Can anyone interpret?

0

There are 0 answers