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

@richard.nolan Hi, have you had a chance to take a look at the following point in the documentation of the Plugin?

*To use: Create a spreadsheet and make the first row a reader row whose names correspond exactly to field names in your CDT (capitalization sensitive.) Header names that don't match CDT fields will be ignored.

I believe that the above mentioned point is the key constraint that I have observed in the implementation of it. Not sure if that answers your question and if not, I would suggest adding some more information re CDT structure and the Excel Template(with headers) and that leaves space for the community to come up with pointers.

Just in case, if you would like to overcome the header names limitation,you may opt for using Macro method as discussed at https://forum.appian.com/suite/tempo/entry/e-194503 and separate technical and end-user facing content.

I've attached four files. An xsd that defines my CDT and three example spreadsheets:
Works_no_problem.xlsx contains the correct column headers and no extraneous columns.
might_work_with_header_list.xlsx contains the correct columns but has different headers.
might_work_has_extra_columns.xlsx contains the correct data but has both different column headers and extraneous columns that should be ignored.

I did read the documentation, however there are two smart services, Parse Excel Spreadsheet to CDT, and Parse Excel Spreadsheet to CDT with Header List. The second formed the basis for my question, I'm not sure how to format that Header List input. Is it a data dictionary that allows me to map cdt values to particular headers in the spreadsheet, is it a text array of headers that should map to the cdt values? There isn't much documentation.

Part of the exercise we're trying to build is to use an automated process to capture archival data that's held in spreadsheets, so this tool would be hugely useful for us....

Cheers

might_work_has_extra_columns.xlsx

might_work_with_header_list.xlsx

Works_no_problem.xlsx

Example.xsd

So does anyone have an example of how to configure the data inputs tab of the parse Excel to CDT with Header List Smart Service?

@richard.nolan Hi, let me see your last two comments and I will try to see what can be done. I have actually forgotten to reply to this.

Cheers, anything you can clarify would be great. My main issues is trying to figure out the configuration of that "Header List" data input (or dictionary entry in the matching expression)

@richard.nolan Hi, so the Inputs of 'Parse Excel Spreadsheet to CDT With Headers' Smart Service should be configured as follows as per the CDT you have attached:

Cdt: {type!Example()}
Excel Doc:
Header List: {"quantity_ordered","special_instructions","fulfillment_employee","fulfillment_manager","warehouse_aisle","warehouse_shelf","robot_picker_id","order_priority","backlog_estimate_date"}
Is Header Present: True
Max Row Count: 10000
Sheet Number: 1
Start Row: 2


Note:
The Headers in the Excel should exactly match the names of the fields in CDT. For example, you can't expect to map the values of 'Priority' in the Excel to map to 'order_priority' in CDT. In short, the Headers (the very first row) in the Excel should exactly match the names of the fields in the CDT, else the columns with unmatched headers will be ignored by smart service.

Not sure if my explanation answers your question, but please do let me know in case if you have follow up questions or concerns.

That makes sense, but is unfortunate. I had hoped that the 'Parse Excel Spreadsheet to CDT with Header List' offered significantly enhanced functionality over 'Parse Excel Spreadsheet to CDT' Smart Service.

Given that I would like to read historical spreadsheets (and cannot therefore control the headers) as part of a process model, is there a better way to read the data out of them? Any advice?

richard.nolan I believe it's not straight forward in-case of this plugin. If you would like to proceed with usage of this plugin, as per my knowledge, you need to implement Macro method as suggested earlier(where I did point to a forum post) in this post.

Meanwhile I will take a look at the plugins if any that can accomplish your use-case without much changes.

Thanks so much! I did read the Macro posts! Helpful, but still not entirely what we need. If you do stumble across something useful, please do let me know!

Sure, will do. Correct, even Macros do need a rework but the advantage is that the templates will be business friendly and off-course again you need a CDT again to read the values based on headers.