Parse an Excel Attachment to JSON

[bettymoon]

Hi,

I am looking to implement a new workflow which, in principle seems quite simple, but I'm unsure of a few steps. The workflow is in simple terms:

1. User creates a request from a catalog item and Attaches their filled in Excel form to the request
2. The workflow creates a task, this task will need to be approved
3. Once approved, there is a script that parses each row in the Excel form as JSON and sends it as a payload to a web service

Surrounding this, I unsure of the following:

1. How do I make sure the user attachment stays attached to the task throughout?
2. How do I parse said Excel sheet, in step 3, and send as JSON? How can this be done? 

Can anybody help, I've read a few articles and questions, but I don't understand the steps required, or how Script Include / UI Scripts can be used? 

Thanks in advance!

[ARG645]

We can get Bytes of an Excel, but parsing a  Excel sheet cell by cell basis via Server side script may not be possible. 

One way I can think of to do this, with more control over parsing Excel is to 

1. Create a Data Source of File Type Excel, Import set and a transform map 

2. Create a OnStart transform script , where you can use the variable named source which is a GlideRecord representation of all the rows in Excel. Do your magic in this script to create a JSON and make the webserviceCall. Then set ignore=true after your web service call so that the transform stops. 

3. Copy the attachment to the DataSource via Script

4. Run the import and transform via code.

Sounds like moving Mountains, but ill be interested to know if there are any simpler solutions directly via Server Side scripts. I tried the code in How to read Excel file in Service request and map the data of excel to the variables, it gave me the contents of excel but along with that it also gave me all the junk characters, so this cannt be parsed, and it gets worse if the Excel sheet is huge.