Inside the Read Excel Spreadsheet Utilities plug-in, there is a smart service (a

Inside the Read Excel Spreadsheet Utilities plug-in, there is a smart service (and an expression) that imply some control over the header list. Specifically the Parse Excel Spreadsheet to CDT with Header List Smart Service.

The documentation is not clear on how this works. I have a CDT that contains a definition for some data I care about and a spreadsheet that contains the data I want to put into that CDT. The data is, unfortunately contained in columns that are interspersed with other columns that I don't care about.

Is there a good way to use the Header List parameter to pull the data I want?
Do I need to manually manipulate the spreadsheet to conform to the plugin?
Is there a better way?

OriginalPostID-215679

OriginalPostID-215679

  Discussion posts and replies are publicly visible

  • @richard.nolan Have you had a thought over using 'Read Excel File' at https://forum.appian.com/suite/tempo/records/item/lMBCLGOdlMUpdGVqW3dQaIKmclBmvvNEj8vu_cjb7T-5YiPr4Fu8ly5Yj1s09uenE4RYzA8zKyx7eiUhe-uLnOF-0Nns7GJH66UeF02OAU96rxFlg/view/summary? Here is an example of how to use it:

    fn!readexcelsheet(
    excelDocument: ,
    sheetNumber: 0,
    startRow: 2,
    numberOfColumns: 9
    )

    But you need to bear in mind that you need to type cast and map the result set to the target CDT. Good thing is that you don't need to worry about headers in Excel vs fields in CDT and downside is that you need to do some perform some homework (in terms of type-casting and mapping to target CDT) in the process.
  • An other option is to make use of fn!readexcelcolumn() in the 'Read Excel Spreadsheet Utilities' Shared Component(the one which we are discussing on at the moment) but the process needs to perform little bit home work prior to constructing the CDT.

    Example:
    fn!load(
              local!col1:fn!readexcelcolumn(4865,1,1),
              local!col2:fn!readexcelcolumn(4865,1,2),
              local!col3:fn!readexcelcolumn(4865,1,3),
              local!col4:fn!readexcelcolumn(4865,1,4),
              local!col5:fn!readexcelcolumn(4865,1,5),
              local!col6:fn!readexcelcolumn(4865,1,6),
              local!col7:fn!readexcelcolumn(4865,1,7),
              local!col8:fn!readexcelcolumn(4865,1,8),
              local!col9:fn!readexcelcolumn(4865,1,9),
              /* Perform Type Casting of the above data */
              fn!apply(
                        type!Example(
                                  product_id:null,
                                  quantity_ordered:_,
                                  special_instructions:_,
                                  fulfillment_employee:_,
                                  fulfillment_manager:_,
                                  warehouse_aisle:_,
                                  warehouse_shelf:_,
                                  order_priority:_,
                                  robot_picker_id:_,
                                  backlog_estimate_date:_
                        ),
                        fn!merge(
                                  local!col1,local!col2,local!col3,local!col4,local!col5,local!col6,local!col7,local!col8,local!col9
                        )
              )
    )

    Advantage is that you don't need to worry about how headers are named but the downside is, querying each column, then type casting and mapping to a CDT finally makes the approach expensive on the whole.
  • Hi, i read the above comments and accordingly, i have kept the header list of the excel document and the cdt exactly same. Also, the header list in the input parameters of the smart service matches with that of the cdt and the excel header row. Even then, the document is not getting parsed. Is there something else to configure?
  • Also, when i am providing the sheet no. as 0, i am getting the below error:
    Sheet index (-1) is out of range (0..2)
  • @ankitab918 can you please check the excel document extension should be ".xlsx" or else it is not parsing. Please check once.
  • @ankitab918, please follow these pre-requisites
    Pre-requisites
    * CDT passed in as input must be Multiple Type
    * Does not currently handle nested CDTs
    * Includes code borrowed from James Carter (CDT Helper) and Sandeep Kumar (Basic POI HSSF Functionality)
    * Input file must be .XLSX not .XLS
    * readExcelRow and readExcelColumn return Text arrays. You must do any type conversion on your own.
  • 1)CDT is multiple types
    2) it's not a nested cdt
    3)input file is .xlsx

    i am using "Parse Excel Spreadsheet to CDT"
    Header List:={"sortKey","storyKey","techDescription","sprint", "dependencies","assignedTo", "storyPoints","actualEffort","status", "comments"}
    Is Header Present: True
    Max Row Count:1000
    Sheet Number: 1 (when entered 0,following error is displayed Sheet index (-1) is out of range (0..2) )
    start Row: 2

    New Microsoft Excel Worksheet.xlsx

    APP_UserStoryDoc.xsd

  • I had switched my approach to using the readexcelsheet function from a different shared component, but it appears that component and the one we were just discussing are going from the shared components list, Does anyone know why?