Raw Data from IArrayOfArrayOfRCCell or IArrayOfFieldValue

[chrweber]

Hi I wish to get a "pointer" or a whole blob of data from the COM Interface to quickly populate an excel range.

Currently i have to copy item-wise.

Is there a way to get at least a whole field at once and "cast" it into a (i.e.) c# array of respective type?

best regards

[marcus_sommer]

A field with its values is already an array but you won't be able to transfer it directly to excel as array but you could loop through the field to create a new and interchangeable array. Here an extended example from the APIGuide.qvw:

set val=ActiveDocument.Fields("Month").GetPossibleValues

for i=0 to val.Count-1

    msgbox(val.Item(i).Text)

    arr(i) = val.Item(i).Text

next

- Marcus

[chrweber]

Hi Marcus, thank you for your reply.

I am well aware of that option, but copying item-wise is very slow.

Fortunately, the com service appears to work using parallel loops, increasing performance by an order of magnitude.

It would have been nice to get a blob of data for copying without any additional overhead/latency.

[marcus_sommer]

I would expect that a loop like mentioned above would just need a second even with hundreds/thousands of values - is this already too slow? How many field-values could occur?

What is the aim of your task - maybe there are other ways to reach your aim.

- Marcus

[chrweber]

I want to copy tables to excel and be as fast as possible.

Why be anything but?

A parallel copy is maybe 10% faster than copy paste and allows parallel processing, unlike the copy paste option.

Excel allows for simultaneous setting of entire ranges, with minimal latency. So why incur thousands of iterations if a single one will do.

[marcus_sommer]

In this case why not just copying the table with something like:

rem ** copy full table to clipboard **

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true

and than a simple paste-command within excel.

- Marcus

[chrweber]

Because then I could not process multiple tables at once, as the clipboard  is single thread only (As far as I know. An alternative would be immensely helpful).

Copying many small tables introduces a lot of latency.

For comparison. The simple sequential copy paste solution takes 28 secs.

parallel copy 24, and additional object parallelism 22 seconds.

These values are crude approxiates and depend on the current load of the system.

Add to that a beefy CPU and you can shave of even more

[marcus_sommer]

Ok. I think I understand - you want to transfer a bigger number of tables/objects from QlikView to Excel. And yes it will take some time if you want to do it directly by copying, reading or exporting the objects which indicates that maybe an indirect way is more appropriate.

Indirect meant to store the data from the script-side and/or to export larger rawdata into xls or csv and using them as external data-source within Excel again.

How many tables/objects and records should be transferred to Excel and how long takes the process (maybe a different time-window might be useful, too).

- Marcus

[chrweber]

We are talking some dozen small ones(1 row) and a few larger ones (200 rows).

The times are provided in the last post.

What do you mean by the time window?

And as I said, parallel copy of a single table is faster than copy paste (makes sense), which would make a range copy even faster.

[marcus_sommer]

I'm not absolutely sure but I doubt that a parallel copying is possible.I think your mentioned approach with the array's of fields/cells meant no copying else a reading of cells over the rows/columns of their objects.

With a different time-window was meant to start this task somewhen overnight and then let it work for a few seconds/minutes.

- Marcus