C#-Excel: Cannot set PivotCache Recordset with adUseServer

[Vishakh]

Hello,

I am trying to create pivot tables using data from CSV files. Here is an example:

String conStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\\";
String query = "SELECT * FROM content0.csv";
Recordset content = new Recordset();
Connection cn = new Connection();
cn.Open(conStr, "", "", 0);
content.Open(query, cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic , 0);
 
Application xlApp = new Application();
Workbook xlWb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
xlApp.Visible = true;
Worksheet xlWs = (Worksheet) xlWb.Sheets.get_Item(1);
 
PivotCaches xlPivCaches = xlApp.ActiveWorkbook.PivotCaches();
PivotCache xlPc = xlPivCaches.Add(XlPivotTableSourceType.xlExternal, Missing.Value);
xlPc.Recordset = content;
PivotTable xlPt = xlPc.CreatePivotTable(xlWs.Cells[1, 1], "test", Missing.Value, Missing.Value);
 
content.Close();
cn.Close();

Running this code gives the following exeption:

Exception from HRESULT: 0x800A03EC
at System.RuntimeType.ForwardCallToInvokeMember(Strin g memberName, BindingFla
gs flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.PivotCache.set_Reco rdset(Object )
at Project1.Sandbox.Main() in C:\Documents and Settings\vvishakh\My Documents
\Visual Studio 2005\Projects\Project1\Project1\Sandbox.cs:line 43

The above is caused by the line

xlPc.Recordset = content;

If I add the line below right before opening the Recordset, the code works and a pivot table shows up:

cn.CursorLocation = CursorLocationEnum.adUseClient;

However, I want to use asUseServer since some of the files I read in are as large as 100 MB and can contain more than half a million rows. adUseClient uses up all my memory, I get an OutOfMemoryException and the pivot table doesn't load.

I have been grappling with the problem over the past few days and would be very grateful if someone could tell me how to get Excel pivot caches to accept Recordsets using adUseServer.

[TonyR9652]

I've just hit the same problem :)

The only workaround I can think of for the moment is to stay with adUseServer/adOpenForwardOnly and load the data initially into a normal SHEET (allowing up to 1 million rows in XL12, the same limit as an xlExternal pivotcache).

You can then build your PIVOT table on this table data, which works very well with these cursor settings, but initially leads to "double-storing" of the data.

It appears you can then DELETE the data sheet afterwards and SAVE the file, but without refreshing the Pivot. Result looks similar to the xlExternal method and appears to preserve all the pivot table functionaility except REFRESH (which personally I don't plan to use, as we are doing single-use/canned reports).

But I too would appreciate a better solution...

Tony