Dynamic CDT

While I do not recommend to load a unknown volume of data into memory, is there a reason a map does not make it? Alternatively a dictionary. Did you try any of these two?

Here The data will be minimal only and map and dictionary both are not possible as the field names are dynamic and logic based ie.,headers of the Excel and the excel cannot be static.
Ex:  a!map(local!exceldata.result[1].values[1]):"value")
Error : Expression evaluation error at function a!map [line 48]: Total keys and values must be equal. Received 0 keys and 1 values

Hi , Can you share the output of local!exceldata variable above?

You'd probably need to read just the first (header) row and use those as your data keys.  It can be a bit tricky but in the past I've done it by manually transforming the result into a JSON string, then casting that back from JSON to dictionary.

Just apply some magic ;-)

a!update(
  a!map(),
  "new_field",
  "new_value"
)

Thanks , it worked.

Hi Stefan Helzle I tried with the code you gave. I got  {a!map('Mentee GGID': "9008752"), a!map('Enter Mentor name': "Harikrishnan")} only 2 fileds I thinks that is because i have 2 data row but the column are 25. Can you help me understand what i did wrong?

a!localVariables(
  /* Step 1: Read the data from the Excel file */
  local!excelData: index(
    index(
      readExcelSheet(
        excelDocument: ri!document,
        sheetNumber: 0,
        startRow: 2,
        numberOfColumns: 0
      ),
      "result",
      null
    ),
    "values",
    null
  ),

  /* Step 2: Extract headers from the first row */
  local!headers: index(
    index(
      readExcelSheet(
        excelDocument: ri!document,
        sheetNumber: 0,
        startRow: 1,
        numberOfColumns: 0
      ),
      "result",
      null
    ),
    "values",
    null
  ),
  local!firstRow:index(local!headers,1,null),

  /* Step 3: Map headers to each row’s values and prepare for database insert */
  local!rowsToInsert: a!forEach(
    items: local!excelData,
    expression: a!update(
      a!map(),
      local!firstRow[fv!index],
      fv!item[fv!index]

    )
  ),


  local!rowsToInsert


)

Hi Stefan Helzle I tried with the code you gave. I got  {a!map('Mentee GGID': "9008752"), a!map('Enter Mentor name': "Harikrishnan")} only 2 fileds I thinks that is because i have 2 data row but the column are 25. Can you help me understand what i did wrong?

a!localVariables(
  /* Step 1: Read the data from the Excel file */
  local!excelData: index(
    index(
      readExcelSheet(
        excelDocument: ri!document,
        sheetNumber: 0,
        startRow: 2,
        numberOfColumns: 0
      ),
      "result",
      null
    ),
    "values",
    null
  ),

  /* Step 2: Extract headers from the first row */
  local!headers: index(
    index(
      readExcelSheet(
        excelDocument: ri!document,
        sheetNumber: 0,
        startRow: 1,
        numberOfColumns: 0
      ),
      "result",
      null
    ),
    "values",
    null
  ),
  local!firstRow:index(local!headers,1,null),

  /* Step 3: Map headers to each row’s values and prepare for database insert */
  local!rowsToInsert: a!forEach(
    items: local!excelData,
    expression: a!update(
      a!map(),
      local!firstRow[fv!index],
      fv!item[fv!index]

    )
  ),


  local!rowsToInsert


)

Sure. foreach() creates one item for each input item. This means that you will get ONE new map for each item in your local!excelData. It will NOT append fields to the same map.

Appian uses a functional approach for expressions that includes immutability of variables.

I do my Excel imports in a different way. First I import the file to a staging table with the fields named like in Excel (A, B, C, ...) and the data types are just varchars, then I run a stored procedure that does the necessary data transformation into the final table.

Thanks for your reply Stefan. First with for each I didn't know which data I should use, i.e for looping over with data local!excelData from second row or to use local!headers. 

So for excel import what I understand from you is I should directly insert the data from first row i.e from headers into the staging table right. I am trying that. I couldn't find a way since my excel is dynamic which will have different headers columns and count of the columns will change each time. 

Trying to import Excel files with no clear structure is inherently difficult and prone to errors.

Again, what I do for the first step, is to do a full raw import without looking at any structure. The logic comes in the second step. You just have to make sure that your database staging table has enough columns.

Thanks. I understood yet if there were a way it would been easy right.