Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
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
jonathandienst
Partner - Champion III
Partner - Champion III

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

View solution in original post

4 Replies
tamilarasu
Champion
Champion

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

HirisH_V7
Master
Master

Hi ,



Please, post Sample Data .

-Hirish

HirisH
“Aspire to Inspire before we Expire!”
jonathandienst
Partner - Champion III
Partner - Champion III

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
Author

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?