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().

[Anonymous]

Tamil,

Thank you for your prompt reply. I will test it and get back.

Regards.

[Anonymous]

Tamil, I tried your soln. it is calculating $(vHeaderSize) as having 0 value and thus loading the file without a blank row only.

vHeaderSize = If( Len(Trim(Peek('@1',0, 'Check')))>0,0,1)

in the code above it seems it is resulting only one value based on "if it is >0". can we make it consider less than 0 as well?

how can we pass both 'data types' so that they become avail on the front end to distinguish which file has a blank row and which one does not.

Thanks again. I really appreciate.

[tamilarasu · Accepted solution]

Hi Qlikdash,

vHeaderSize = If( Len(Trim(Peek('@1',0, 'Check')))>0,0,1)

Actually, we are storing the header size either 0 or 1 in the above variable for each csv file. The above script should load both the csv files along with file status. I did a test and it's working fine. I have attached the same for your understanding,

[antoniotiman]

Hi,

see attachment

Regards,

Antonio

[Anonymous]

Tamil,

Thanks again for sending that. I tested yours and it works but it did not work in my qvw. it might be the folder structure I don't knw. I will update.

[tamilarasu]

Hi Bro,

You can use debug mode to check whether the files are taken properly. Especially the line "FoundFile In FileList". You can also check log file to identify the path and file names.

Just remove the ..\Data Quality from the below line and paste your absolute path and try again.

Call ScanFolder('..\Data Quality')

[Anonymous]

Tamil, Thank you so much for your help. It took me a while because I was investigating why it was not working. Your code works in the sample but did not work in my actual csv data files. One reason was the cell below @1 had len =1 from a white space. this was being treated as having a populated row. Below is the code that worked for me. I have the major changes in Blue highlight

In summary, I am first forcing the csv files with 0 header lines which also gets 'bad' file status. if there is an error it will load the remaining files which have 1 header lines and will have 'good' as header status. need to notify one thing which is I am not using $(vHeaderSize) functionality, which would have been more dynamic.

Data:

Load '' as Dummy AutoGenerate 0;

sub ScanFolder(Root)

    for each FoundFile in filelist( 'C:\Users\DataIssue\*.csv')

        vChkTable:

        LOAD @1

  FROM

        [$(FoundFile)]

        (txt, codepage is 1252, no labels, delimiter is ',', msq);

        Let vHeaderSize = Peek('@1',0, 'vChkTable');

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

        Drop Table vChkTable;

        Concatenate (Data)

        LOAD C1,

            C2,

            C3,

            FileName()as Filename,

            'Bad' as FileStatus

        FROM

    [$(FoundFile)]

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

if ScriptError=11  then

Concatenate (Data)

        LOAD C1,

            C2,

            C3,

            FileName()as Filename,

           'Good' as FileStatus

        FROM

    [$(FoundFile)]

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

      end if

      next FoundFile

SET vPath='C:\Users\DataIssue\*.csv';

End Sub

Call ScanFolder('$(vPath)')

DROP Field Dummy;