Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Reading Excel files in Qlikview

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

 

 

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

Re: Reading Excel files in Qlikview

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

 

 

View solution in original post

11 Replies
Highlighted
Specialist
Specialist

Re: Reading Excel files in Qlikview

LOAD *

FROM [Excel....]

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

To help community find solutions, please don't forget to mark as correct.
Highlighted
Contributor II
Contributor II

Re: Reading Excel files in Qlikview

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

Highlighted
Specialist III
Specialist III

Re: Reading Excel files in Qlikview

Hi,

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

Marcus

Highlighted
Specialist III
Specialist III

Re: Reading Excel files in Qlikview

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

 

Highlighted
Contributor II
Contributor II

Re: Reading Excel files in Qlikview

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.

Highlighted
Contributor II
Contributor II

Re: Reading Excel files in Qlikview

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

Highlighted
Specialist III
Specialist III

Re: Reading Excel files in Qlikview

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

 

 

View solution in original post

Highlighted
Contributor II
Contributor II

Re: Reading Excel files in Qlikview

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.

Highlighted
Specialist III
Specialist III

Re: Reading Excel files in Qlikview

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