Qlikview excel exporting issue.

[Anonymous]

Hi All,

I have come accross a scanario where the exporting from QV to excel changes into csv file. Let me explain the scanario. If I have some limited volume of data suppose less than a million records data, it is correctly saving the text format as text format in excel. But when the no of records increases then it get automatically exported to csv file instead of excel file. And here the problem occurs. The text (Field) doesn't fetch the same information as chart value. It removes the 0's prefix. Is there any solution to force the export to only in excel. Any help will be appreciated.

Best Regards,

SKumar

[IAMDV]

SKumar,

This is not a QlikView issue rather it’s the limitation of Excel. I mean this behaviour is normal when you have more than 65K rows while exporting the data. So if you have more than 65K rows then you will see CSV file opening up in Excel. And if it’s less than 65K rows then you would see the Excel files which will preserve the leading zeros. However, if you are exporting more than 65K rows you are not losing the leading zeros in underlying CSV file. What is happening here is Excel is trying to interpret the CSV file in number format, which truncates the leading zeros. If you want you can test by using Right click> Export > Save As (CSV) file to a location and then import the file manually into Excel and while importing you need to select the format as Text that will preserve the leading zeros.

I would suggest you to use VBScript which exports the data into CSV or TXT format for rows more than 65K rows , then you train the users to manually import these files to Excel and selecting the Text format in the process.

I hope this helps!

Cheers,

DV

www.QlikShare.com

[Anonymous]

Hi Deepak,

Thanks for your reply. I do agree with your point of excel behaviour. It's strange but true.  Now when I am using your technique it's saying out of virtual memory. I have checked in csv format even there it's coming as number format when I have made script as text(field). I dont see any hope to pull that column as it is.

You said: If you want you can test by using Right click> Export > Save As (CSV) file to a location and then import the file manually into Excel and while importing you need to select the format as Text that will preserve the leading zeros.

Didn't work for me. any other way around.

Best Regards,

SKumar

[IAMDV]

Hi SKumar,

You are welcome. Approx. how many rows are you trying to extract? How big is this data set? Please close other applications when trying to extract this data. I mean if you have opened other windows/ applications while performing this task because you need Virtual Memory to perform this task. If you open many windows/ applications you are using your Virtual Memory on OS.

Not sure, why I didn't work for you. I have tested it again and it works with my data. How are you opening this CSV file after exporting with the above method I have suggested? Please make sure that you import the CSV into Excel and NOT open the CSV file directly. Because if you are opening directly then Excel by default converts the field into number format, which truncates the leading zeros. So please import the CSV by using Data Menu > Select From Text (Icon) > Select your CSV file > In step 3 you will see (Column data format). Please change this to Text which will preserve your leading zeros.

If you still have issues then please post QV file with subset of data and I'll show you the CSV file with leading zeros.

I hope this helps!

Cheers,

DV

www.QlikShare.com

[Anonymous]

Hi Deepak,

Thanks for your help. However I have closed all my other application and process and tried the way you said to export the csv but again the virtual memory issue in coming says allocating 1MB.The total rows which I was exporting is 160111 approx.

Thanking you in advance for your kind attentation.

Best Regards,

Skumar

[IAMDV]

Hi Skumar,

No problem. Please can you post the same data? How many columns do you have? And what is the RAM usage on your system when you are performing this activity? I'm sure it must be touching 100%. Please can you check this?

How much RAM do you have on the Server and on your system? Also, did you try from another system?

Thanks,

DV

www.QlikShare.com

[IAMDV]

Did you manage to resolve this issue?

Thanks,

DV

www.QlikShare.com