Reading Excel files in Qlikview

[prachisangewar1]

Hi,

I am reading excel files as my source to generate QVDs. I am capturing the excel row no. to do some mapping.

Some files have NULL rows, which qlik excludes while reading and the row no. it popluates is not correct.My requirement is to also capture the null rows and with the correct row number.

I have used rowno() and Recno() function. Both do not solve the purpose. Please advise if any other workaroung or solution for this scenario.

Thanks

[Claudiu_Anghelescu]

LOAD *

FROM [Excel....]

Where RowNo() >= 0 and Rowno() <= 25;

[prachisangewar1]

thanks,but i have already tried it and it does not work. Please advise if any other solution

[marcus_malinow]

Hi,

I'd suggest uploading a small sample excel file, and an indication of the result you're looking for.

Marcus

[marcus_malinow]

Actually, scratch that. Have tried locally and am seeing the same.

Couple of questions

- why do you need the null rows?

- do you have a row number indicator in your source file? If so, then you can possibly fill in the gaps after your initial load

[prachisangewar1]

hi,

Attached is the sample file. what i need is, all the rows should be counted in. there are 2 blank rows which qlikview skips.

I am generating a key using the recno()/Rowno(). So instead of giving me 12 rows qlik loads only 10 leaving the blank rows.

Please advise.

[prachisangewar1]

i dont have the rowno. indicator in my source, thats why need to generate it based on the excel row number.

[marcus_malinow · Accepted solution]

This is possible if you use the Excel ODBC driver. You may need to locate this and install it on your machine.

Code as below:

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\marcus.malinow\Desktop\NullTest\Book1.xlsx];

LOAD
RecNo() as RecNo,
RowNo() as RowNo,
`Key`,
    Value;
SQL SELECT `Key`,
    Value
FROM `C:\Users\marcus.malinow\Desktop\NullTest\Book1.xlsx`.`Sheet1$`;

[prachisangewar1]

hi Marcus,

Thank you for the suggestion. the solution works fine when using the embedded headers,but in my case i need to use this

"(ooxml, no labels, table is Table1)" As my file has first row i.e. header row also blank for many columns. Please advise if any workaround for this. Thanks in advance.

[marcus_malinow]

Doesn't look to be possible.

Either you load as an Excel file, with no embedded headers, and lose your NULL rows

Or you load using ODBC and the first row is interpreted as being the header

I think your original question has been answered though, so please mark the answer