Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
KHSDM
Creator III
Creator III

Skipping Rows in Excel

Hi,

I have this one Excel template where there is an empty row between the first and third row.

MjtJCiQGom.png

For some reason, in 2 different file but same template, QlikView will skip the empty row in one of it, but not the other. Anyone know what is the reason/why this is happening? I'm having issue loading the files now because of the different format even though it look exactly the same in Excel.

File 1:

9Mb8yrLd5H.png

 

File 2:

ksYwdwkd4l.png

1 Solution

Accepted Solutions
marcus_sommer

Quite probably the second row in both files are not identically - one is really empty and one not. This could happens through any invisible content or maybe just a formatting or maybe the way how this row was created.

Invisible content could mean that the font- and background-color are the same or that there is just an expression result = "" or any empty space or something similar. Any formatting could be there if for example an user copied there any cell from anywhere just to check something - afterwards he pressed delete and removed the content but the formatting will be remain. Also if this row didn't exists within the origin data and were then inserted it will inherit some formatting from the row above/below.

I don't know the technically details how an excel-file is loaded but I doubt that Qlik identified these rows itself else I think they just read per any standard-libraries the Excel meta-data which contain various informations about the used range within the Excel sheets. Therefore I think it's mainly an issue on the Excel side.

Further differences could occur through different Excel releases and also if they were created online/offline respectively through any third-party tools. It looked that they are always the same but it's not completely true.

To resolve your problem you could try to open this file and just storing it again (this worked often if the issue is caused through the fact that the files were created/saved with another release/tool). If this isn't successful you could take any really empty cell/row and copy & paste it over this row or you copy & paste row 1 and afterwards all the other rows into a new sheet or maybe even a new file (but not the whole range because you would copy the issue, too).

- Marcus

View solution in original post

4 Replies
zhadrakas
Specialist II
Specialist II

did you try header size = 2 rows?

that should do it.

 

header.png

KHSDM
Creator III
Creator III
Author

Actually my current logic is using the header size = 1 because QlikView skip the empty rows. But for some reason, in the new Excel, QlikView can't skip the empty rows causing it to mismatch. (Header not loaded)

marcus_sommer

Quite probably the second row in both files are not identically - one is really empty and one not. This could happens through any invisible content or maybe just a formatting or maybe the way how this row was created.

Invisible content could mean that the font- and background-color are the same or that there is just an expression result = "" or any empty space or something similar. Any formatting could be there if for example an user copied there any cell from anywhere just to check something - afterwards he pressed delete and removed the content but the formatting will be remain. Also if this row didn't exists within the origin data and were then inserted it will inherit some formatting from the row above/below.

I don't know the technically details how an excel-file is loaded but I doubt that Qlik identified these rows itself else I think they just read per any standard-libraries the Excel meta-data which contain various informations about the used range within the Excel sheets. Therefore I think it's mainly an issue on the Excel side.

Further differences could occur through different Excel releases and also if they were created online/offline respectively through any third-party tools. It looked that they are always the same but it's not completely true.

To resolve your problem you could try to open this file and just storing it again (this worked often if the issue is caused through the fact that the files were created/saved with another release/tool). If this isn't successful you could take any really empty cell/row and copy & paste it over this row or you copy & paste row 1 and afterwards all the other rows into a new sheet or maybe even a new file (but not the whole range because you would copy the issue, too).

- Marcus

KHSDM
Creator III
Creator III
Author

Tried to clear the rows (clear content / delete row and reinsert / etc), but I still can't get QV skip the rows.

I really need to know the exact scenario on when QV decide skip the rows... and whether this behaviour can be disabled.