• State Suggested Answer
  • Replies 8 replies
  • Answers 2 answers
  • Subscribers 15 subscribers
  • Views 221 views
  • Users 0 members are here
Related Discussions
Home » Discussions » New to Appian

Dynamic mapping of excel data to staging table

iswarya2812

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 ?

  Discussion posts and replies are publicly visible

Parents
  • 0
    Certified Senior Developer

    Hi  ,

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

  • 0 in reply to Shanmathi Ponnusamy

    Hi  

    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.

  • 0
    Certified Senior Developer
     in reply to iswarya2812

    Hi  ,

    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?

  • 0 in reply to Shanmathi Ponnusamy
    Mentee ID Enter Mentor name Enter Mentor email Enter Mentee Name Enter Mentee email Enter Chief Mentor email Understanding of Maven Setup & Basic AEM servers Reason Understanding of different consoles in AEM Reason Understanding of AEM Core Components & Extending them Reason Understanding of HTL language Reason Understanding of having Client Libraries as part of AEM Reason Understanding of AEM Templates (Static and Editable Templates) Reason Analytical Skill Reason Problem Solving Reason Communication Skills Reason Please provide detailed feedback of your mentee
    9008752 Test User test@gmail.com Test User test@gmail.com test@gamil.com 4  demonstrated quick learning and has completed all the assignment on time.  4 He understand allt he console concept in AEM  3 Good understanding of core components 4 good 3 need more understanding on clientlib 4 good understanding of AEM template.   created multiple assignment on template 3 verbal communication is good and can be more proactive in communication 3 verbal communication is good and can be more proactive in communication 5 verbal communication is good and can be more proactive in communication  has learned AEM quickly and he is good fit to deploy in projects.

    This is the structure of the excel . I could find how to upload as excel. Here the understanding of maeven setup is attribute for that i want to give score and reason why we give the score. its out of 5 . so the attributes will vary based on technologies here we have adobe ,if we take appian the attribute will change. and number of attributes will also change. 

  • 0
    Certified Senior Developer
     in reply to iswarya2812

     ,

    Just want to be clear on few things.

    1)Is the first 6 columns always static? Or for the static columns you have any fixed lables?

    2)So all the other headers and those values will be from excel which will be dynamic, and you need to just read and save to your table to use?

  • 0 in reply to Shanmathi Ponnusamy

    the headers are fixed for first 6 but the values for them are dynamic. we are updating data of multiple trainees.

    from 7 the header names changes depending on technology and yes I need to read and save these into table.

  • 0
    Certified Senior Developer
     in reply to iswarya2812

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

Reply
  • 0
    Certified Senior Developer
     in reply to iswarya2812

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

Children
  • 0 in reply to Shanmathi Ponnusamy

    Thanks for the code, I added some modification to get the reasons and scores but I could not get the scores.

    a!localVariables(
  local!excelRawData: index(
    readexcelimportfile(
      ri!document,
      0,
      0,
      30
    ),
    "result",
    null
  ),
  local!headerRowValuesTemp: a!flatten(
    index(
      index(local!excelRawData, 1, null),
      "values",
      null
    )
  ),
  local!fixedHeadersCount: 6,

  /* Filter headers to exclude "Reason" columns */
  local!headerRowValues: reject(
    rule!NGT_CheckIsNullOrEmpty,
    reject(
      fn!contains(_, "Reason"),
      if(
        length({ local!headerRowValuesTemp }) >= local!fixedHeadersCount,
        remove(
          local!headerRowValuesTemp,
          enumerate(local!fixedHeadersCount) + 1
        ),
        local!headerRowValuesTemp
      )
    )
  ),

  /* Filter only "Reason" headers */
  local!reasonHeaders: index(
    local!headerRowValuesTemp,
    wherecontains("Reason", local!headerRowValuesTemp),
    {}
  ),

  /* Calculate count of valid non-"Reason" headers */
  local!noOfHeaderValues: length({ local!headerRowValues }),
  local!excelDataRowsTemp: index(local!excelRawData, "values", null),

  /* Remove headers from data rows */
  local!excelDataRows: if(
    length({ local!excelDataRowsTemp }) > 0,
    remove(local!excelDataRowsTemp, 1),
    null
  ),

  /* Indices for headers excluding "Reason" columns */
  local!indicesForHeaders: a!forEach(
    items: enumerate(local!noOfHeaderValues) + 1,
    expression: "attribute" & fv!index
  ),
  local!constructDataForHeaders: a!update(
    data: 'type!{urn:com:appian:types:NGT}NGT_UPLD_STG_FEEDBACK_EVALUATION'(),
    index: { local!indicesForHeaders },
    value: { local!headerRowValues }
  ),

  /* Dynamically create indices for score values based on non-"Reason" headers */
  local!indicesForValues: a!forEach(
    items: local!headerRowValues,
    expression: "score" & fv!index
  ),
  local!constructDataForValues: a!flatten(
    a!forEach(
      items: local!excelDataRows,
      expression: a!localVariables(
        /* Filter out "Reason" columns in the current row */
        local!currentRowValues: reject(
          fn!contains(_, "Reason"),
          if(
            length({ fv!item }) >= local!fixedHeadersCount,
            remove(
              fv!item,
              enumerate(local!fixedHeadersCount) + 1
            ),
            null
          )
        ),

        if(
          rule!NGT_CheckIsNullOrEmpty(inputValue: local!currentRowValues),
          null,
          a!update(
            data: 'type!{urn:com:appian:types:NGT}NGT_UPLD_STG_FEEDBACK_EVALUATION'(),
            index: { local!indicesForValues },
            value: {
              index(
                local!currentRowValues,
                enumerate(local!noOfHeaderValues) + 1,
                null
              )
            }
          )
        )
      )
    )
  ),

  /* Process "Reason" columns and map them to reason attributes */
  local!indicesForReasons: a!forEach(
    items: local!reasonHeaders,
    expression: "reason" & fv!index
  ),
  local!constructDataForReasons: a!flatten(
    a!forEach(
      items: local!excelDataRows,
      expression: a!localVariables(
        local!currentReasonValues: index(
          fv!item,
          wherecontains("Reason", local!headerRowValuesTemp),
          null
        ),

        if(
          rule!NGT_CheckIsNullOrEmpty(inputValue: local!currentReasonValues),
          null,
          a!update(
            data: 'type!{urn:com:appian:types:NGT}NGT_UPLD_STG_FEEDBACK_EVALUATION'(),
            index: { local!indicesForReasons },
            value: { local!currentReasonValues }
          )
        )
      )
    )
  ),

  {
    local!constructDataForHeaders,
    local!constructDataForValues,
    local!constructDataForReasons
  }
)

© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.