Skip to main content
S
shubhamy0001
February 9, 2022
Question

How to Ignore empty records while reading the excel file?

  • February 9, 2022
  • 2 replies
  • 0 views

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

    2 replies

    J
    jasmithak990
    February 9, 2022

    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
  )
)
    [View:/cfs-file/__key/communityserver-discussions-components-files/62/Sample-Excel.xlsx:320:240]

    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
    jamesm4933
    September 30, 2022

    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)

    Terms & ConditionsAccessibility statement