How to Ignore empty records while reading the excel file?

Question

Hello Everyone, 
 Can anyone please tell me how can I read the excel file and ignore the is blank rows i.e if any column is blank then that particular row should be ignored. 
 Many Thanks

jasmithak · Answer

a!localVariables(
 local!exceldata : readexcelsheet(
 excelDocument:cons!JK_SMAPLE_EXCEL_DOC, 
 sheetNumber:0, 
 startRow:0
 ).result.values,
 
 local!excelColumnDatatoFilterRecords :wherecontains(
 null,
 touniformstring(
 readexcelsheet(
 excelDocument:cons!JK_SMAPLE_EXCEL_DOC, 
 sheetNumber:0, 
 startRow:0,
 numberOfColumns:1
 ).result.values
 )
 ),
 
 remove( 
 local!exceldata,
 local!excelColumnDatatoFilterRecords
 )
) Sample Excel.xlsx 
 
 It will read the excel data only if there is a value in column 1. if column 1 data is blank it will remove from the list.

jamesm4933 · Answer

Very useful! 
 But it only works for checking against the first column. If you cast the returned data to a dictionary, you can check against any column: 
 
 local!dataDictionary: ldrop(
 /*Drops the header row*/
 a!forEach(
 items: local!data,
 /*Casts to a dictionary, and gives the item a key that corresponds to the first item in that column*/
 expression: a!update(cast(94, {}), local!data[1], fv!item)
 ),
 1
),
local!emptyRows: wherecontains(
 /*Locates rows where there is no value in the "Date" column*/
 /*You could also just use an index number*/
 null,
 touniformstring(local!dataDictionary["Date"])
),

remove(local!dataDictionary, local!emptyRows)