topic Loading null rows from the excel in QlikView

Loading null rows from the excel

Tue, 03 Jan 2012 07:24:10 GMT

Dear All,

Do you have any idea on how can i load null rows from excel files. for example this is my excel sheet.

w	r

w	x



r	t

Qlikview interpret it as

w	r
w	x
r	t

....so, can I load it just as it is in excel, with 7 rows rather than 3 rows ???

Loading null rows from the excel

Tue, 03 Jan 2012 07:29:52 GMT

Seems that using ODBC/OLEDB is the only way. is there any other way...??

Loading null rows from the excel

SunilChauhan — Tue, 03 Jan 2012 08:07:28 GMT

try this

Table1:

LOAD if(isnull(fieldname1), 'Null'&rowno(),fieldname1) as fieldname1,

if(isnull(fieldname2), 'Null'&rowno(),fieldname2) as fieldname2

FROM

(ooxml, embedded labels, table is Sheet1);

hope this helps

Loading null rows from the excel

Tue, 03 Jan 2012 08:42:08 GMT

Hi Sunil,

It does not work.

Loading null rows from the excel

Tue, 03 Jan 2012 12:52:57 GMT

LOAD rowno() as field1,

Col1,

Col2

FROM

Xxxxx.xlsx

(ooxml, embedded labels, table is Sheet1);

Loading null rows from the excel

Tue, 03 Jan 2012 14:18:27 GMT

Hi Philippe,

It does not work, neither RecNo() works.

Loading null rows from the excel

Tue, 03 Jan 2012 19:21:31 GMT

If you save tour excel files à CSV file, you should be able to do the load

Using the rowno() function as I wrote in my last answer.

Hope that helps

Philippe

Loading null rows from the excel

Tue, 03 Jan 2012 19:23:20 GMT

In a better English

If you save your excel file as a CSV you should be able to load the nulls

Using the rowno() as I wrote before.

Philippe

Loading null rows from the excel

Thu, 05 Jan 2012 14:48:47 GMT

Hi Philippe,

I don't think it is a good idea to save an excel file having 50-60 sheets asa csv. We will need separate csv for every sheet.

One thing we can do is just can put full border in excel sheet and it will loadnull rows also. But this again involves manipulating with source, which I don'twant.

Rather, I feel the best solution in this case will be load data from excelusing ODBC which will give null rows as well without manipulating source data.

But, As we know one problem has many possible solutions, so I just fulfilledmy requirement with some more script rather than using ODBC.

Anyways, thanks for your suggestions.

Re: Loading null rows from the excel

prachisangewar1 — Thu, 15 Aug 2019 08:31:17 GMT

hi, can advise what solution you used ? I am also facing the same issue.

Thanks in Advance.