Skip to main content
iswaryan3555
iswaryan3555
October 30, 2024
Question

Dynamic mapping of excel data to staging table

  • October 30, 2024
  • 8 replies
  • 0 views

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 ?

8 replies

shanmathip7466
shanmathip7466
October 30, 2024

Hi [mention:24ecc13e300c47b1a73ada75867e8d92:e9ed411860ed4f2ba0265705b8793d05] ,

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

iswaryan3555
iswaryan3555Author
October 30, 2024

Hi [mention:8f8eb6b7f316476598a071db8c05cf6a:e9ed411860ed4f2ba0265705b8793d05] 

I tried the code but its not working the way I expected .

My excel struture is  that first 7 columns will have some details header like name, email id, employee id after that this attriubute and reason column will start  it will be till  max 20 for example headers will be like (understanding of appian, process models, etc.),and Reason  and  last column will be detailed feedback in the excel. 

In the data row for each attribute header i want to give score and for each reason header need to give reasons for the score provided. my cdt structure is contains feild of same name,email,attribute1,score1,reason1 like till 20 , i want to map them accordingly while the attribute name and number of attribute can change.

shanmathip7466
shanmathip7466
October 30, 2024

Hi [mention:24ecc13e300c47b1a73ada75867e8d92:e9ed411860ed4f2ba0265705b8793d05] ,

Could you please share the excel file with sample data and share your expected result?

One question, You mentioned that you want to give score and reason. How do you give that any criteria?

Terms & ConditionsAccessibility statement