QVW excel storage with added headers and fields

[michael_wernsing]

Hello,

I want to  load a file, process it and store it again in Excel to make it connectable with another program. To be able to load it into the other program, I need to add 9 rows and and add some text in A2-A4 and B2-B4. B2 is the only field which changes, as it has the date in it.

In Row 10 is the Headline of the processed data, 11-... contain the data.

Is there any way to do it?

Thanks for any ideas!

Michael

[bramkn]

Hi,

It is not possible to store into Excel files from within the QV scripting.
You can store into CSV and QVD.
The modification of the data is of course possible within the script. If you need any help with this please provide some sample data and your wishes.

[michael_wernsing]

Hi,

if Excel doesn't work, then you all should drive me out of town with torches and stuff, because I am probably some kind of witch, because the program does exactly that.

Anyway:

Here's a sample of what I do in QV and how it should be exported to a spread sheet (csv or any other type of file doesn't matter to me). It's pretty basic because I just want to know how to get the file into the format of the Excel file which is also attached.

The yellow highlighted fields have to be added to the Header and the red fields shall be filled with Information to be able for the other program to read the data. The rest of the file should contain the data from the Qlikview script.

If you need more Information, just let me know.

Michael

[bramkn]

You can read the xlsx data no worries. But storing into xlsx isn't supported.
Exporting to Excel is possible from the dashboard but not the script.

To add this kind of header data you can for example create an inline table or use autogenerate to create the rows you want. After you created these rows you can concatenate your data to it and store it into CSV.

[michael_wernsing]

Do you have an idea how I can do it in a simple manner?  I have tried to do it for a couple of hours now, but I  don't find a solution...

[marcus_sommer · Accepted solution]

The simplest way is probably to load the excel twice. At first the few header rows and in the second real data-load you add the info from the first load, maybe with something like this:

temp: first 3 load A, B from xlsx (ooxml, no labels, table is XXX);

final: load *, fieldvalue('B', 1) as Date, fieldvalue('B', 2) as AccountingGroup, ...
from xlsx (ooxml, embedded lables, header 10 lines, , table is XXX);

If all your xlsx are so simple and have always the same structure you could try it in this way. Are they more dynamic you might need some more steps in between - like counting the records with/without content to define the first and header lines and/or looping through the temp-table and to read their content into n variables which are then used in the final load instead of the fieldvalue().

- Marcus