Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Excel in Compatibility Mode?

Hi community,

I have to load an excel file which is in compatibility mode to qlik. When I am trying to load it into qlik it is loading only 250 records out of 12000 records. How to resolve this, without making any changes in excel, I need to do it in qlik itself. Any solution?

1 Solution

Accepted Solutions
MVP
MVP

Re: Excel in Compatibility Mode?

An Excel file cannot be in "compatability mode" -- that applies to the appearance in the Excel application and has no affect on the file. Is there a completely blank line in the file? You may need to use the Qlikview filter functions to skip the empty line. Start the table file wizard by clicking on Table Files in the script editor. Click on the button "Enable Transformation Step" in the second page of the wizard. Click Conditional Delete..., and use Column 1 is empty as the skip condition. Click Add and OK and verify how many rows you have. Click Next and Finish. This will add the filter to the qualifier part of the From statement - eg:

FROM C:\Qlikview\Test\Book1.xlsx

(ooxml, no labels, table is Sheet1, filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))

));

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies

Re: Excel in Compatibility Mode?

I have tested sample data in my system and working fine for me. Could you post your excel. ?

hirishv7
Honored Contributor

Re: Excel in Compatibility Mode?

Hi ,



Please, post Sample Data .

-Hirish

MVP
MVP

Re: Excel in Compatibility Mode?

An Excel file cannot be in "compatability mode" -- that applies to the appearance in the Excel application and has no affect on the file. Is there a completely blank line in the file? You may need to use the Qlikview filter functions to skip the empty line. Start the table file wizard by clicking on Table Files in the script editor. Click on the button "Enable Transformation Step" in the second page of the wizard. Click Conditional Delete..., and use Column 1 is empty as the skip condition. Click Add and OK and verify how many rows you have. Click Next and Finish. This will add the filter to the qualifier part of the From statement - eg:

FROM C:\Qlikview\Test\Book1.xlsx

(ooxml, no labels, table is Sheet1, filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))

));

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Excel in Compatibility Mode?

Hi Jonathan

Thanks for the reply.

I don't have any blank lines in the sheet. It's just in comapatibility mode. I have to load it into qlik. But it is not loading. Any other solution?

Community Browser