• State Verified Answer
  • Replies 3 replies
  • Subscribers 8 subscribers
  • Views 3553 views
  • Users 0 members are here
Related Discussions
Home » Discussions » General

Reading empty rows while reading excel file

arshbirs0001

Hi Team, 

I am using function 

a!localVariables(
local!output:readexcelsheet(
excelDocument:ri!inputfile,
sheetNumber:0,
startRow:2,


),
local!output1:local!output.result,

local!output2:a!forEach(
items: local!output1.values,
expression:
cast(typeof('type!{urn:com:appian:types}MPPSAMAccountabilityTable'()),
{
samname: fv!item[1],
samemail: fv!item[2],
sammanagername: fv!item[3],
sammanageremail:fv!item[4],
region: fv!item[5] ,
customername:fv!item[6],
fabname: fv!item[7],
creationDate: today(),
lastUpdateDate:today(),
createdBy:user(loggedInUser(),"lastName")&", "&user(loggedInUser(),"firstName"),
lastUpdatedBy:user(loggedInUser(),"lastName")&", "&user(loggedInUser(),"firstName"),
comments:"CREATED WITH FILE UPLOAD FROM MPP",
inactive: fv!item[13]
}
)
),
local!output2
)

My Excel file is filled with 2 rows (1 heading and other data), but it is returning 13 items... ideally it should return 1 .....1st index is data and other 11 indexed are empty rows.

How to fix it to not read empty rows of excel?

  Discussion posts and replies are publicly visible

Parents
  • +1
    Appian Employee

    Hi Arshbir,

    It should return the number of rows that you are expecting but you probably have additional blank rows in your spreadsheet.

    if you're reading a static document, can you clean it (copy the relevant only the relevant data into another excel file) and try again.

    If you're generating this file and adding blank rows yourself, consider removing the empty rows first according to a specific column that you know will always exist.

    Something like this:

    local!filteredList: remove(
      local!recordSets,
      where(
        a!forEach(
          items: local!recordSets,
          expression: isnull(
            fv!item[3]
          )
        )
      )
    )

Reply
  • +1
    Appian Employee

    Hi Arshbir,

    It should return the number of rows that you are expecting but you probably have additional blank rows in your spreadsheet.

    if you're reading a static document, can you clean it (copy the relevant only the relevant data into another excel file) and try again.

    If you're generating this file and adding blank rows yourself, consider removing the empty rows first according to a specific column that you know will always exist.

    Something like this:

    local!filteredList: remove(
      local!recordSets,
      where(
        a!forEach(
          items: local!recordSets,
          expression: isnull(
            fv!item[3]
          )
        )
      )
    )

Children

© 2024 Appian. All rights reserved.