Reading empty rows while reading excel file

Question

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?

Pedro Simões · Accepted Answer

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]
 )
 )
 )
 )

Reading empty rows while reading excel file

Top Replies