Skip to main content
Known Participant
Feb 9, 2022 1:02 PM UTC
Question

How to Ignore empty records while reading the excel file?

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

    Inspiring
    Feb 9, 2022 2:44 PM UTC

    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
    Known Participant
    Sep 30, 2022 1:00 PM UTC

    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)