topic Exporting large tables in excel in QlikView

Exporting large tables in excel

claudio1 — Tue, 27 Sep 2011 10:13:06 GMT

Hi all,

I'm in need of exporting a table in my report with more than 65536 records, so i have to use the excel format "xlsx" given that using the format "xls" i end up having a worksheet with the content of the table splitted in more than one sheet.

I already tried to give to the ExportBiff function (in my export macro) a filename with the "xlsx" extension but the format of the generated file remains xls and Excel can not open it until i rename it back to xls.

Anyone knows if there is a way to export in xlsx format?

Thank you in advance.

Cheers,

Claudio

Exporting large tables in excel

Tue, 27 Sep 2011 10:22:31 GMT

are you open to use xlsx feature toconvert the same in xlsx..

as in qv there is nooption to set it in xlsx..

the default export format will always be xls.. i.e of 65356 records per sheet

...

u can import this generated xls into xlsx.. which is more easier way to load the data in desired format

Exporting large tables in excel

marcohadiyanto — Tue, 27 Sep 2011 11:10:45 GMT

hi,,

what version you use?

if you use new version, you can send to excel more than 65536 records. but it convert to .csv not to .xlxs

regards,

Marco

Exporting large tables in excel

Anonymous — Tue, 27 Sep 2011 12:00:14 GMT

csv only can export more than 65536 records.

Exporting large tables in excel

claudio1 — Tue, 27 Sep 2011 12:56:37 GMT

sangyt ha scritto:
are you open to use xlsx feature toconvert the same in xlsx..
as in qv there is nooption to set it in xlsx..
the default export format will always be xls.. i.e of 65356 records per sheet
...
u can import this generated xls into xlsx.. which is more easier way to load the data in desired format

I have to find a "batch way" to do that, given that the reload and the export itself is already a command line batch : first it calls the reload of the report by command line, then the onpostreload event of the report is called and the exportation macro attached to the event exports the report object (in this case, a chart table).

Anyway if i use the manual built in "convert" inside Excel the sheets can't be merged togheter, am i missing something?

Re: Exporting large tables in excel

claudio1 — Tue, 27 Sep 2011 13:15:30 GMT

Marco Hadiyanto ha scritto:
hi,,
what version you use?
if you use new version, you can send to excel more than 65536 records. but it convert to .csv not to .xlxs
regards,
Marco

Indeed if i export it in this way :

obj.Export "c:\temp\report_table.csv", ", "

all my 150000 records are exported correctly.

Anyway now i should find a way (i think using another macro or some something like that) to convert this csv to an xlsx because i need it to be an excel document not a csv.

Maybe from csv to xlsx it's easier because i don't have that "merging" problem.