Validate the excel header

Question

implement an Appian Expression Rule that validates the sequence of header values, ensuring that:

The first value is "SAP Number" ,

The second value is "Qty" ,

The third to sixth values, if present, are "Spare" , "Price" , "Delivery Date" , and "Remarks" ,

The third to sixth values are optional and may be missing,

Shubham Aware · Answer

You can read data from Excel files using the Excel Tools plugin and its associated smart services. For more information, please refer to the following resources: Excel Tools plugin documentation: https://community.appian.com/b/appmarket/posts/excel-tools The attached documentation for additional details . PDF

Yashwanth Akula · Answer

Hi Rishu Kumar Gupta Use constant for storing the column names. I hope this is what you are expecting. a!localVariables(
 local!columnNames: {
 "SAP Number",
 "Qty",
 "Spare",
 "Price",
 "Delivery Date",
 "Remarks"
 },
 local!excelDataSubset: readexcelsheetpaging(
 excelDocument: ri!excelDoc,
 sheetNumber: 0,
 pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 1)/*batchsize is 1 Because we need only first row from the excel to validate, which will be column names*/
 ),
 local!columnsNamesFromExcel: index(
 local!excelDataSubset,
 "data",
 "values",
 1,
 {}
 ),
 reject(
 a!isNullOrEmpty,
 a!forEach(
 items: local!columnNames,
 expression: if(
 contains({ 1, 2 }, fv!index),
 /*mandatory check for first two columns*/
 if(
 tostring(
 index(
 local!columnsNamesFromExcel,
 fv!index,
 ""
 )
 ) = tostring(fv!item),
 null,
 concat(
 "Invalid header at A",
 fv!index,
 ", Expected header is '",
 fv!item,
 "' but it has: ",
 index(
 local!columnsNamesFromExcel,
 fv!index,
 ""
 )
 )
 ),
 if(
 a!isNullOrEmpty(
 index(
 local!columnsNamesFromExcel,
 fv!index,
 ""
 )
 ),
 null,
 if(
 tostring(
 index(
 local!columnsNamesFromExcel,
 fv!index,
 ""
 )
 ) = tostring(fv!item),
 null,
 concat(
 "Invalid header at A",
 fv!index,
 ", Expected header is '",
 fv!item,
 "' but it has: ",
 index(
 local!columnsNamesFromExcel,
 fv!index,
 ""
 )
 )
 ) 
 )
 )
 )
 )
)