Can I use the script to store into xlsx?

[#1]

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?

[ashfaq_haseeb]

Hi,

Try below

Dept:

LOAD empid,

     deptname

FROM

dept.qvd

(qvd);

store Dept into Dept.xls(txt);

Regards

ASHFAQ

[its_anandrjs]

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.

[#4]

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

[its_anandrjs]

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);

[#6]

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.

[ashfaq_haseeb]

Hi

may be Rob Wunderlich or Michael Solomovich

Can help here.

Regards

ASHFAQ

[martynlloyd · Accepted solution]

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.

[its_anandrjs]

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.

[#10]

Thank you for the prompt response this is great!

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

Much appreciated