Dynamic mapping of excel data to staging table

Question

I am new to appian 
 I need read excel data along with header which is dynamic it will change for excel to excel some fileds are static and insert them into a staging table both header and data for that column . my staging table contains fileds like attribute1, reason1, score1 till 20 . I want to insert headers to attriubute field from 1st row and reason and score to data from second row 
 I am trying with readexcelsheet/readexcelwithfiter/readexcelwithpaging functions but since its dynamic i am having confusion how to do the mapping 
 Imp : Number of header column and name of the header column will be dynamic 
 Can anyone help me how to do or is there any other approaches there ?

Shanmathi Ponnusamy · Answer

Hi iswarya2812 , 
 Please check and try if the below code helps. a!localVariables(
 local!excelRawData: index(
 readexcelimportfile(
 ri!docId,
 0,
 0,
 /*For now given as 30 which is maximum. But we have less than 30 rows*/
 30
 ),
 "result",
 null
 ),
 local!headerRowValuesTemp: a!flatten(
 index(
 index(local!excelRawData, 1, null),
 "values",
 null
 )
 ),
 local!headerRowValues: reject(
 rule!CSCM_CheckIsNullOrEmpty,
 { local!headerRowValuesTemp }
 ),
 /*To find how many columns are present*/
 local!noOfHeaderValues: length({ local!headerRowValues }),
 local!excelDataRowsTemp: index(local!excelRawData, "values", null),
 /*remove Headers*/
 local!excelDataRows: touniformstring(
 if(
 length({ local!excelDataRowsTemp }) > 0,
 remove(local!excelDataRowsTemp, 1),
 null
 )
 ),
 local!indicesForHeaders: a!forEach(
 items: enumerate(local!noOfHeaderValues) + 1,
 expression: "attribute" & fv!index
 ),
 local!constructDataForHeaders: a!update(
 /*Instead of map you can give your CDT or record*/
 data: a!map(),
 index: { local!indicesForHeaders },
 value: { local!headerRowValues }
 ),
 local!indicesForValues: a!forEach(
 items: enumerate(local!noOfHeaderValues) + 1,
 expression: "value" & fv!index
 ),
 local!constructDataForValues: a!update(
 /*Instead of map you can give your CDT or record*/
 data: a!map(),
 index: { local!indicesForValues },
 value: {
 index(
 local!excelDataRows,
 enumerate(local!noOfHeaderValues) + 1,
 null
 )
 }
 ),
 {
 local!constructDataForHeaders,
 local!constructDataForValues
 }
)

Shanmathi Ponnusamy · Answer

Okay. You can use the below code to get started. In the below I removed First 6 columns and returned Headers and values. You can index and construct your data in Record or CDT. 
 a!localVariables(
 local!excelRawData: index(
 readexcelimportfile(
 ri!docId,
 0,
 0,
 /*For now given as 30 which is maximum. But we have less than 30 rows*/
 30
 ),
 "result",
 null
 ),
 local!headerRowValuesTemp: a!flatten(
 index(
 index(local!excelRawData, 1, null),
 "values",
 null
 )
 ),
 local!fixedHeadersCount: 6,
 local!headerRowValues: reject(
 rule!CSCM_CheckIsNullOrEmpty,
 {
 if(
 length({ local!headerRowValuesTemp }) >= local!fixedHeadersCount,
 remove(
 local!headerRowValuesTemp,
 enumerate(local!fixedHeadersCount) + 1
 ),
 local!headerRowValuesTemp
 )
 }
 ),
 /*To find how many columns are present*/
 local!noOfHeaderValues: length({ local!headerRowValues }),
 local!excelDataRowsTemp: index(local!excelRawData, "values", null),
 /*remove Headers*/
 local!excelDataRows: if(
 length({ local!excelDataRowsTemp }) > 0,
 remove(local!excelDataRowsTemp, 1),
 null
 ),
 local!indicesForHeaders: a!forEach(
 items: enumerate(local!noOfHeaderValues) + 1,
 expression: "attribute" & fv!index
 ),
 local!constructDataForHeaders: a!update(
 /*Instead of map you can give your CDT or record*/
 data: a!map(),
 index: { local!indicesForHeaders },
 value: { local!headerRowValues }
 ),
 local!indicesForValues: a!forEach(
 items: enumerate(local!noOfHeaderValues) + 1,
 expression: "value" & fv!index
 ),
 local!constructDataForValues: a!flatten(
 a!forEach(
 items: local!excelDataRows,
 expression: a!localVariables(
 local!currentRowValues: {
 if(
 length({ fv!item }) >= local!fixedHeadersCount,
 remove(
 fv!item,
 enumerate(local!fixedHeadersCount) + 1
 ),
 null
 )
 },
 if(
 rule!CSCM_CheckIsNullOrEmpty(value: local!currentRowValues),
 null,
 a!update(
 /*Instead of map you can give your CDT or record*/
 data: a!map(),
 index: { local!indicesForValues },
 value: {
 index(
 local!currentRowValues,
 enumerate(local!noOfHeaderValues) + 1,
 null
 )
 }
 )
 )
 )
 )
 ),
 {
 local!constructDataForHeaders,
 local!constructDataForValues
 }
)

Dynamic mapping of excel data to staging table

Top Replies