Loading null rows from the excel

[#1]

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 ???

[#2]

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

[SunilChauhan]

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

[#4]

Hi Sunil,

          It does not work.

[#5]

LOAD  rowno() as field1,

Col1,

Col2

FROM

Xxxxx.xlsx

(ooxml, embedded labels, table is Sheet1);

[#6]

Hi Philippe,

                    It does not work, neither RecNo() works.

[#7]

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

[#8]

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

[#9]

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.

[prachisangewar1]

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

Thanks in Advance.