topic Re: Store data in the same File Excel but different sheet in Integration, Extension & APIs

Store data in the same File Excel but different sheet

livio218526 — Sat, 16 Nov 2024 19:18:35 GMT

Hello to all, I would like to know if there is a way to store data in the same excel file but in different sheets, using the load script.

Thanks,

Livio

Re: Store data in the same File Excel but different sheet

davidfdezcalleja — Tue, 14 Jan 2020 16:45:24 GMT

Hi,

for example, in this file excel there is two diferent sheets (AirPassangers and Hoja1)

using the load script

I hope it fits you.

Re: Store data in the same File Excel but different sheet

livio218526 — Tue, 14 Jan 2020 13:12:31 GMT

Hello,

I am referring to the store and not to the load

Re: Store data in the same File Excel but different sheet

davidfdezcalleja — Tue, 14 Jan 2020 13:40:11 GMT

Add these lines in bold

[AirPassengers]:
LOAD
[ID] AS [AirPassengers.ID],
[Date] AS [AirPassengers.Date],
[Year] AS [AirPassengers.Year],
[Month] AS [AirPassengers.Month],
[AirPassengers] AS [AirPassengers.AirPassengers]
FROM [lib://datos/AirPassengers_prueba.xlsx]
(ooxml, embedded labels, table is AirPassengers);

Store * from AirPassengers into 'lib://datos/AirPassengers.qvd';

[Hoja1]:
LOAD
[ID] AS [Hoja1.ID],
[Date] AS [Hoja1.Date],
[Year] AS [Hoja1.Year],
[Month] AS [Hoja1.Month],
[AirPassengers] AS [Hoja1.AirPassengers]
FROM [lib://datos/AirPassengers_prueba.xlsx]
(ooxml, embedded labels, table is Hoja1);

Store * from Hoja1 into 'lib://datos/Hoja1.qvd';

Re: Store data in the same File Excel but different sheet

livio218526 — Wed, 22 Jan 2020 10:39:33 GMT

No, in thisway i stored in two different qvd....

Re: Store data in the same File Excel but different sheet

jonathandienst — Wed, 22 Jan 2020 12:09:00 GMT

I don't think that its possible to direct a store to more than on tab in a sheet using the load script alone. You could write a VBS or VBA macro to consolidate two outputs into a single file after the load is complete, and you may be able to find some code searching here and/or Excel macro coding web sites.

Re: Store data in the same File Excel but different sheet

andoryuu — Wed, 22 Jan 2020 13:27:05 GMT

Qlik will tell you to get NPrinting. We have it here (although I can't recommend it) and it is used to generate multi-sheet excel outputs.

Re: Store data in the same File Excel but different sheet

simonaubert — Fri, 25 Nov 2022 11:47:38 GMT

Hello @livio218526

You can't as of today.
However, you can promote this idea by voting
https://community.qlik.com/t5/Suggest-an-Idea/QS-Script-store-with-more-format/idi-p/2005668

Best regards