Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Not applicable

Can I use the script to store into xlsx?

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?

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

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.

11 Replies

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

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?

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.

Not applicable

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

XLSError.pngHi,

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?

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

Not applicable

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

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?

Hi

may be Rob Wunderlich or Michael Solomovich

Can help here.

Regards

ASHFAQ

Highlighted
Partner
Partner

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

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?

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.

Not applicable

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

Thank you for the prompt response this is great!

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

Much appreciated