topic Re: remove end rows in excel without data in QlikView

remove end rows in excel without data

Gopi_E — Fri, 27 Jul 2018 15:43:46 GMT

Hi,

I have an excel file with 5 fields, In this excel file end row count is 1307 but the problem is while loading excel file to QlikView it is loading 1048576 rows and the same time I have a data with blank values also. when I try to remove empty rows while loading its effect on my original data,

Can anyone suggest me here I have attached excel file

Thank you,

Gopi

Re: remove end rows in excel without data

m_woolf — Fri, 27 Jul 2018 16:24:56 GMT

Go to first empty row.

Do Shift + Ctrl + down arrow to select all the empty rows.

Do Home|Cells|Delete Sheets Rows

Save the file.

When you re-open the file, the previously empty rows will be forgotten by Excel.

Re: remove end rows in excel without data

BalaBhaskar_Qlik — Sat, 28 Jul 2018 15:41:38 GMT

May be:

There are some special characters available in excel.

To avoid that use: where len(Trim(First Field)>0); condition.

Then it'll remove all the Empty rows those are not required in loading.

Re: remove end rows in excel without data

BalaBhaskar_Qlik — Sat, 28 Jul 2018 15:46:41 GMT

Or try this:

where isnull(Column)=0 and not Column ='';

Ex:

ABC:

Load

from ABC.xlsx where isnull(A)=0 and not A ='';

Re: remove end rows in excel without data

Gopi_E — Sun, 29 Jul 2018 04:53:59 GMT

Thanks for all the inputs