How to Identify if first row is BLANK

[Anonymous]

I have a csv file that some times has 1st row blank and sometimes has it filled. how do I identify when 1st row in csv file is blank. can I use rowno().

[PrashantSangle]

What kind operation you want to do if you know first row is empty????

Yes you can use rowNo() it will return row number.

You can also try with first in load.

Regards

[MindaugasBacius]

I guess you should simple use WHERE not(isnull(FieldName) clause in your loading state.

[Anonymous]

where does not work because the first row before the header is empty. I have multiple files. some have headers from row 1. some have it from row 2. how to identify the two?

[saimahasan]

I think it cannot be handled in qlikview because qlikview by default fetches the first row. I think you need to rectify and correct the format for excel file itself.

[trdandamudi]

Regardless of first row is blank or not blank the load will still work. For an example please take a look at the attached example where I loaded three excel files where two of them has a blank row and one which do not have a blank row. Hope this helps..

[antoniotiman]

Try like this

LOAD @1 as FIELD1,
@2 as FIELD2
FROM
[Cartel1.xls]
(biff, no labels, table is Foglio1$)
Where Len(Trim(@1)) > 0 and @1 <> 'FIELD1';

'FIELD1' is Label in Excel File

[tamilarasu]

I am not sure what Thirumala has suggested in the above qvw file. I am not able to open the file, since I'm using personal edition. You can try something like below,

vChkTable:

Load A

From

Excel.xlsx

(ooxml, no lables, table is sheet1);

Let vChk = Peek('A',0, 'vChkTable');

Drop Table vChkTable;

If  Len(Trim($(vChk) ))>0 Then

Do some action

Else

Do something

End If

This is just a concept for one file. You can easily adapt this code in a loop to load multiple files. There are plenty of loop code available in the forum. If you have any difficulties, let us know.

[Anonymous]

Tamil, Thanks for replying

so here is more details:  I am loading all files at once (csv). excel files automatically deletes first blank row in QV. but with csv, there is an option of choosing header is 0 or 1 line as u can see below from the script.

Temp1:

LOAD [Field A],

    [Field B],

    [Field C],

    [Field D],

    FileName() as Filename

FROM

[..\Data Quality\*July28*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 0 lines);

trying to create a table that lists types of data issue. 1st Expn'July28_3' - if fieldD is null, bad or else good.

Here is the Real Question: My 2nd Expn 'Header Issue':- if a filename has 1st row empty(before header), i want a label Good, if it does not, 'Bad'. In the sample, 28_3 has a blank row. so header issue sud be good for this and bad for rest. how to make this possible.

[..\Data Quality\*July28*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 0 lines);

HINT: when header is 0 lines, it will not load if there is a blank row correct? same rule when header is 1 line. so how do I set it to accept both....may be??

[tamilarasu]

Hi qlikdash,

Got it. Here is the code,

Data:
Load '' as Dummy AutoGenerate 0;

Sub ScanFolder(Root)
     For Each FoundFile in Filelist( Root & '\*.' & FileExtension)
        Check:
        LOAD @1
        FROM
        [$(FoundFile)]
        (txt, codepage is 1252, no labels, delimiter is ',', msq);

        Let vHeaderSize = If( Len(Trim(Peek('@1',0, 'Check')))>0,0,1);
        Drop Table Check;

        Concatenate (Data)
        LOAD [Field A],
             [Field B],
             [Field C],
             [Field D],
             FileName()as Filename,
             If ('$(vHeaderSize)'=0, 'Good','Bad') as FileStatus
        FROM
        [$(FoundFile)]
        (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is $(vHeaderSize) lines);
      next FoundFile

End Sub

Call ScanFolder('..\Data Quality')

DROP Field Dummy;