topic Re: Can I use the script to store into xlsx? in QlikView

Can I use the script to store into xlsx?

Mon, 14 Jul 2014 13:05:07 GMT

Hi,

I need to store an Excel output to be used by a 3rd party application which cannot use txt or csv. I have used store for txt and qvw but cannot find a solution for excel anywhere. Any ideas?

Re: Can I use the script to store into xlsx?

ashfaq_haseeb — Mon, 14 Jul 2014 13:09:55 GMT

Hi,

Try below

Dept:

LOAD empid,

deptname

FROM

dept.qvd

(qvd);

store Dept into Dept.xls(txt);

Regards

ASHFAQ

Re: Can I use the script to store into xlsx?

its_anandrjs — Mon, 14 Jul 2014 13:19:27 GMT

Load your table and store with the QVD files

Ex:-

Sales:

Load

Name,

Sales

From ExcelSource;

STORE Sales into Sales.qvd(qvd);

Drop Table Sales;

Load

Name,

Sales

From QVD Location;

Some thing like this ways.

Re: Can I use the script to store into xlsx?

Tue, 15 Jul 2014 10:07:23 GMT

Hi,

Ashfaq Mohammed wrote:

Hi,

Try below

Dept:

LOAD empid,

deptname

FROM

dept.qvd

(qvd);

store Dept into Dept.xls(txt);

Regards

ASHFAQ

This works in principle if you are manually opening the file but it cannot be automatically consumed by the 3rd party system as the format isn't recognised. Is there any way that QV can create an actual spreadsheet? (only xls or xlsx available sadly)

Thanks

Natalie

Re: Can I use the script to store into xlsx?

its_anandrjs — Tue, 15 Jul 2014 10:12:15 GMT

Inplace of XLS use CSV because XLS is not support by the qlikview

Ex:-

Dept:

LOAD empid,

deptname

FROM

dept.qvd

(qvd);

store Dept into Dept.CSV(txt);

Re: Can I use the script to store into xlsx?

Tue, 15 Jul 2014 10:17:07 GMT

Thanks

Unfortunately the target system will not use anything but xls or xlsx so I'm a bit stuck. I'm looking into alternatives on that side as I have a lot of these to do each day and don't want to be creating them manually.

Re: Can I use the script to store into xlsx?

ashfaq_haseeb — Tue, 15 Jul 2014 10:18:17 GMT

may be Rob Wunderlich or Michael Solomovich

Can help here.

Regards

ASHFAQ

Re: Can I use the script to store into xlsx?

martynlloyd — Tue, 15 Jul 2014 10:21:22 GMT

What I do is save to a text file:

Store [tablename] into [path\KPIExport.csv] (txt);

then use a macro to convert:

Sub LoadFlatFile()

Workbooks.OpenText Filename:= _

"path\KPIExport.csv", Origin _

:=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _

xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _

Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _

Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _

Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), _

TrailingMinusNumbers:=True

Range("A1").Select

Call ...

End Sub

I embed the macro in a spreadsheet and use run-on-open, but you could alternatively place the macro in the QV script.

Regards.

Re: Can I use the script to store into xlsx?

its_anandrjs — Tue, 15 Jul 2014 10:24:08 GMT

I believe you can create a CSV file with store command which work similar to the xls or xlsx file or may be you convert the CSV file into XLS or XLSX file formats after creating.

Re: Can I use the script to store into xlsx?

Tue, 15 Jul 2014 12:22:36 GMT

Thank you for the prompt response this is great!

Almost there and should be able to figure out the tweaks.

Much appreciated

Re: Can I use the script to store into xlsx?

DavidFoster1 — Tue, 15 Jul 2014 12:37:13 GMT

Not directly. The closest is the CSV option in the STORE command. Alternatively you could look at VIZUBI's NPrrinting product.

Re: Can I use the script to store into xlsx?

Tue, 15 Jul 2014 12:44:01 GMT

Thank you David

I heard of this yesterday and will look into it. I'm hoping that we can contain it all to a smaller user base for now but if the requirement changes, it certainly seems like a viable option.

Re: Can I use the script to store into xlsx?

swat_agar — Fri, 28 Aug 2020 06:53:20 GMT

Hi, This is not working for me. It is showing parsing error. I am storing a qvd into csv file and I have to convert it to excel in the same QV script. Please help.