topic Re: Export all list box columns to csv in QlikView

Export all list box columns to csv

terrusie — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

Just looking for a way to program a Macro to export all columns of a list box to a csv file.

I tried:

set ExportObject = ActiveDocument.GetSheetObject(ObjID)

ExportObject.Export FileName, ", "

but this seems to only export the first column.

Re: Export all list box columns to csv

vishsaggi — Sat, 04 Aug 2018 03:35:50 GMT

Check here if this helps:

Macro csv export | Qlik Community

Re: Export all list box columns to csv

BalaBhaskar_Qlik — Sat, 04 Aug 2018 07:11:06 GMT

May be this:

Sub ExportToCSV

Set ExportObject = ActiveDocument.GetSheetObject("(ObjectCode)")

ExportObject.Export "(FileAdress)\(FileName).csv", ";"

End Sub

Re: Export all list box columns to csv

terrusie — Mon, 06 Aug 2018 12:11:05 GMT

This works great for a chart, but when I use it with a listbox, I only get the first column. Is there something I'm doing wrong?

Re: Export all list box columns to csv

terrusie — Mon, 06 Aug 2018 12:11:10 GMT

This works great for a chart, but when I use it with a listbox, I only get the first column. Is there something I'm doing wrong?

Re: Export all list box columns to csv

tamilarasu — Mon, 06 Aug 2018 13:18:02 GMT

Hi Terry,

What do you mean by all columns? Could you explain your requirement bit more clearer. If possible attach a sample and expected output.

Re: Export all list box columns to csv

terrusie — Mon, 06 Aug 2018 13:45:07 GMT

Tamil,

Apologies for not being clear. The listbox has several expressions, but when I use the code above on the listbox, only the data in the initial field show up. None of the data from the expressions show up. For some reason charts produce the expected result, but not listboxes. Unfortunately, the data in my listbox cannot be charted. It contains textual data.

Example:

Listbox:

------------------------------------------------------------------------------------------------

Field datum 1 | Expression 1 datum 1 | Expression 2 datum 1

Field datum 2 | Expression 1 datum 2 | Expression 2 datum 2

Field datum 3 | Expression 1 datum 3 | Expression 2 datum 3

-------------------------------------------------------------------------------------------------

Actual CSV Output:

Field datum 1

Field datum 2

Field datum 3

Expected CSV Output:

Field datum 1, Expression 1 datum 1, Expression 2 datum 1

Field datum 2, Expression 1 datum 2, Expression 2 datum 2

Field datum 3, Expression 1 datum 3, Expression 2 datum 3

Re: Export all list box columns to csv

tamilarasu — Mon, 06 Aug 2018 15:58:00 GMT

Hi Terry.

There is no need to apologize . We just want some clear input to help you further. Now, I understood your question. I have one more request. Is it possible to attach one sample qvw file? It will be really easy for us to test and provide you the solution.

Re: Export all list box columns to csv

terrusie — Mon, 06 Aug 2018 16:11:10 GMT

Tamil,

Unfortunately, I cannot. I do not yet have a pro-license seat. I will soon, perhaps, but not as of yet.

Re: Export all list box columns to csv

terrusie — Mon, 06 Aug 2018 16:12:24 GMT

To reproduce my problem, simply use the code suggested by Bala, above, on a listbox that has a calculated field and at least one expression. I'm sure you will get the same results.

Re: Export all list box columns to csv

tamilarasu — Mon, 06 Aug 2018 16:30:51 GMT

Terry - I tried to reproduce the problem but it is working fine for me. You can attach some sample file like I created. I will provide the code here. You can copy and paste into your application.

Re: Export all list box columns to csv

terrusie — Mon, 06 Aug 2018 17:54:41 GMT

Here's the code. It's not that complex. It may have something to do with how I'm configuring the listbox, but I don't know what that could be.

Sub CSVTEST

set ExportObject = ActiveDocument.GetSheetObject("LB501")

ExportObject.Export "C:\...\MyFile.csv", ", "

End Sub

Re: Export all list box columns to csv

terrusie — Mon, 06 Aug 2018 18:02:32 GMT

It looks as though you defined the list box solely in the "General" tab. I think I was not clear with how the data are entered into the listbox.

In my listbox, "Field datum #" are the results of an expression entered in "General" >> "Field"

"Expression x datum #" are the results of expressions entered on the "Expressions" tab.

Only the data from the "General" tab show up in the CSV file.

Re: Export all list box columns to csv

tamilarasu — Tue, 07 Aug 2018 17:13:17 GMT

Yes. You are right. I have a workaround which will save the file as xls or xlsx and then we can save it as csv file format. But the problem is your folder will have two files one with csv and xls (or xlsx) format.

@ Marcus Sommer Any suggestions? I have also tried to use sendtoexcel syntax and then save it as csv file format. But it looks like activeworkbook syntax is not working here. So I am not sure how to save the opened excel (saved in temp folder) in a specified path. Thank you.

Re: Export all list box columns to csv

terrusie — Tue, 07 Aug 2018 20:19:09 GMT

Tamil,

As far as I can see this is the only way to do it.

The way I ended up doing it was exporting it as an Excel file and then running a .vbs script to convert the excel file to CSV. Works like a charm.

Re: Export all list box columns to csv

tamilarasu — Wed, 08 Aug 2018 07:01:32 GMT

Hi Terry,

Great. Here is the macro code to do the same. Also, If you can post the vbs code here, it may help someone someday.

Sub CSVTEST

'File path & name

vFile = "C:\Users\Tamil\Desktop\MyFile"

'Export to xls format

set ExportObj = ActiveDocument.GetSheetObject("LB01")

ExportObj.Exportbiff vFile & ".xls"

'Convert from xls to csv format

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = False

objExcel.Workbooks.Open(vFile & ".xls")

objExcel.Activeworkbook.Saveas vFile & ".csv", 6

objExcel.quit

'Delete xls file

Set Filesys = CreateObject("Scripting.FileSystemObject")

Filesys.DeleteFile vFile & ".xls"

Set objExcel = Nothing

Set Filesys = Nothing

Msgbox "File has been created!"

End Sub