
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to Identify if first row is BLANK
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().
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I guess you should simple use WHERE not(isnull(FieldName) clause in your loading state.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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..

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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??

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- « Previous Replies
-
- 1
- 2
- Next Replies »