topic Re: Reading Excel files in Qlikview in QlikView

Reading Excel files in Qlikview

prachisangewar1 — Sat, 16 Nov 2024 02:47:36 GMT

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

Re: Reading Excel files in Qlikview

Claudiu_Anghelescu — Mon, 12 Aug 2019 14:50:00 GMT

LOAD *

FROM [Excel....]

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

Re: Reading Excel files in Qlikview

prachisangewar1 — Thu, 15 Aug 2019 08:28:06 GMT

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

Re: Reading Excel files in Qlikview

marcus_malinow — Thu, 15 Aug 2019 08:38:56 GMT

Hi,

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

Marcus

Re: Reading Excel files in Qlikview

marcus_malinow — Thu, 15 Aug 2019 09:08:55 GMT

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

Re: Reading Excel files in Qlikview

prachisangewar1 — Thu, 15 Aug 2019 09:12:51 GMT

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.

Re: Reading Excel files in Qlikview

prachisangewar1 — Thu, 15 Aug 2019 09:14:11 GMT

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

Re: Reading Excel files in Qlikview

marcus_malinow — Thu, 15 Aug 2019 09:44:03 GMT

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$`;

Re: Reading Excel files in Qlikview

prachisangewar1 — Thu, 15 Aug 2019 11:06:08 GMT

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.

Re: Reading Excel files in Qlikview

marcus_malinow — Thu, 15 Aug 2019 12:23:40 GMT

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

Re: Reading Excel files in Qlikview

prachisangewar1 — Fri, 16 Aug 2019 03:36:42 GMT

Understand. is there any way i can read from row 2 as the header ? I tried to modify the script a bit but gives error.

Thanks

Re: Reading Excel files in Qlikview

prachisangewar1 — Wed, 28 Aug 2019 10:54:53 GMT