Hi Community,
I am working on an Appian process where I need to read data from an Excel file using the readexcelsheetpaging function. My Excel sheet (sheet 0) contains many columns, but for my use case, I only need to read two specific columns: ENTITY_ID and BENCHMARK_TYPE. These columns are not always in the same position—they might move if the template changes.
readexcelsheetpaging
ENTITY_ID
BENCHMARK_TYPE
Currently, I am using the following code to read the first 6 columns, since I know my required columns are within those:
My question:Is there a way in Appian to use readexcelsheetpaging (or any other function) to read only specific columns by name (e.g., ENTITY_ID and BENCHMARK_TYPE), regardless of their position in the Excel sheet?If not, what is the best practice for efficiently extracting only the needed columns from a sheet with many columns, especially if the order can change?
Any advice or workarounds would be greatly appreciated!
Thank you!
Discussion posts and replies are publicly visible
Hi @rudraksht548412,
Unfortunately, the readexcelsheetpaging() function in Appian can’t directly select columns by name — it only works by position. But you can handle this dynamically with a small workaround:
readexcelsheetpaging()
Read only the header row first
local!headerRow: readexcelsheetpaging( excelDocument: ri!importDocumentId, sheetNumber: 0, pagingInfo: a!pagingInfo(1, 1), numberOfColumns: cons!AOM_READ_EXCEL_MAX_COLUMNS ).data
This gives you the column names and their order.
Find the index of your needed columns
local!entityIdIndex: index(local!headerRow, "ENTITY_ID", null) local!benchmarkTypeIndex: index(local!headerRow, "BENCHMARK_TYPE", null)
Read all data, then extract only those columns
local!allData: readexcelsheetpaging( excelDocument: ri!importDocumentId, sheetNumber: 0, pagingInfo: a!pagingInfo(1, -1), numberOfColumns: cons!AOM_READ_EXCEL_MAX_COLUMNS ).data, local!filteredData: a!forEach( items: local!allData, expression: { ENTITY_ID: fv!item[local!entityIdIndex], BENCHMARK_TYPE: fv!item[local!benchmarkTypeIndex] } )
This way, even if the column positions change, you’ll still get the correct fields without manually adjusting indexes.