How to read multiple sheets of an excel file in Outsystem?

How to read multiple sheets of an excel file in Outsystem?

  

Dear Community,

I'm able to read a single sheet of an excel file in Outsystem and insert the data in an entity. However, we need to read multiple sheets of an excel file and load its content in different entities.

It would be great if you could please give me a detailed explanation to this and point me to right resources for this.

Regards,
Shiva

Hello Shiva,

I know 2 way's you could do this:

1) Set the data in multiple sheets in excel. In the Excel2RL you can set the sheet name of which sheet you want to use.

2) You can think about using the FileSystem widget. For this you must save the excel lists in a folder, get them in the application by the ReadBinary action (FileSystem extension) and select on filename.

Kind regards,
Evert

Dear Evert,

Thanks for the quick reply.


Can you please explain me how to acheive this iteratively. Actually what we are doing at present:


1.We are passing the full path of the excel sheet to an Outsystem web service. The file exists on the serverand Outsystem has full access to this file.
2.We now are looking for a mechanism in Outsystem that can read the multiple sheets of that excel file iteratively probably and we would also want to upload the data into different entities of different data sheets. I mean I'm looking for kind of For-Each loop to iterate through each sheet or if For-Each is not possible then please share some other strategy that I can use in this scenario

Can you please help me?

Regards,
Shiva

Hi Shiva,

While there is no way at the moment to iterate through the several Excel sheets without knowing their names, that question came up in the past, and it was solved using an extension to get the sheet's names. After that you can iterate through the several sheets by name.

I hope this helps.

Regards,

Paulo Tavares
Hello Shiva,

It's good to hear more about what you're trying to achive, but I still don't understand it all.

If I understand correctly:

1) You call a webservice from OS to get the folderpath where the excel file is located
2) You get the excellist from the received folder path

Then it's get a little bit fussy:

3) The excel list contains multiple sheet, where you want to set the data from the sheet into different entities?

On which way the selection is made or on which way you want the selection be made? (so by which element/name you know which sheet must be imported to which entity or is this set as colom in the excel list?)

And do I also understand there must be set data back in the excel sheet?

Waiting for you're reply,
Evert

Dear Evert,

Let's understand this from an example.
  1. Suppose I have an excel file called Data.xls. Inside it, I have two data sheets. First is Employee (which contains employee data) and other is Supervisor (which contains employee data). Both these sheets have no dependency on each other and they have valid data inside them.
  2. Similarly, I have 2 entities in Outsystem corresponding to these sheets. These entities are named Employee  and Supervisor. Structure of these entities is exactly similar to the columns of corresponding excel sheets.
  3. Now I want to upload the data of Employee sheet of  Data.xls in Employee entity and data of Supervisor sheet of  Data.xls in Supervisor entity of Outsystem.
How I can I do this thing in Outsystem? Basically I'm looking for a mechanism where in I can read all the sheets of excel iteratively or in a defined sequence so that I can insert their data in Outsystem entities.

Assumption

You can assume that I know the structure of my excel sheet i.e. I know how many sheets it contains and names of these worksheets.

What I'm doing?

Till now I have been able to successfully read the data of first sheet of the excel file. For this the steps I'm following are:
  1. I use File_ReadBinary to convert the file into stream of bytes.
  2. I pass it's output to ExcelToRecordList which parses the file and returns me the list of my entity.
Now If I have to read the second sheet of the same excel file, I will have to repeat the same process again in a sequence for the second sheet i.e. using same controls in the same web service method again. If this is the case then in case I have 5 data sheets in the same excel file, then that web service method will become so long. One option will be to break this big web service function into multiple actions.

I'm looking for a confirmation that is it right way to do such a task in Outsystem? If not, can you please guide me a better way to do this in Outsystem?

Can you please share your comments on the same?

Regards,
Shiva
Any update Guys??
Hello Shiva,

It was a little bit busy overhere :).

I was typing an answer but knowing it would take to long I just made an example in OutSystems, it's attached with this post (it's all empty but you can see what I've done in the flow with the comments start in action: "ReadExcelList"). Hope this is the solution you're looking for.

Kind regards,
Evert
Dear Evert,

Thank you so very much for finding out the time from your busy schedule for my problem. I have achieved what I wanted exactly the way you have mentioned in your test application. This has validated and confirmed that my POC was going in right direction.

Thank you again for the help.

Regards,
Shiva
Shiva,

That's where the community is for, helping each other!

Kind regards,
Evert
Dear Evert/Paulo,

I exactly followed the strategy used by you to read multiple worksheets of a workbook. As suggested, in order to keep the web method short and clean, I divided it into multiple actions in a sequence where one action do the job of reading only one worksheet and inserting the data in database.

Since transaction management is an important part of any web method, I implemented exception handling in every action.

Let's understand what I'm doing in detail
  1. I have created 4 actions  each to read one worksheet and I'm executing  these actions in a sequence. Let's call these actions as A, B, C and D.
  2. Inside each of the action, I convert excel workbook into binary and then use ExcelToRecordList function to convert it into list. I'm then passing its output into RecordListToXML in-built function which converts my data list into xml format. I finally then pass this xml into a SQL procedure whcih then parses this xml file and inseerts data in database table. Inside these procedures, I have done transaction management. If I encounter an error, I use raiseerror command to raise the error and rollback the changes made in the current stored procedure. This is what is happening in every action.
  3. In addition, I have done exception handling in each of the Outsystem action as well. In exception handling, I'm setting the value of a variable to true/false if an error comes up.

The problem I'm facing?

  1. Actions A,B,C and D of a web method are perfomed in a sequence and all goes well if no error is encountered. Data gets inserted into database tables without any issue.
  2. Now suppose, A gets executed successfully but action B encountered an error and rest of the actions C and D also executed without any issue. Now if I check my database tables, data for worksheets/actions C and D only gets uploaded. I agree that data for B shouldn't be in database because action B encountered an error. But what about A? Why it didn't get uploaded?
  3. Aimilarly if an error comes up in action C only then data for action D only gets into the database table while no data gets uploaded in database for actions A and B irrespective of the fact they executed successfully or not.
Observation Made

It thus appears that Outsystem is reverting all the transactions made before error occurred. Even if transaction commits successfully in respective stored procedures for actions A and B, their work reverts if stored procedure for action C rollbacks the changes. All these 4 stored procs are independent procedures and are not associated with each other in any way. Then how come rollback in one stored procedure affecting the other. It means that some high level transaction is operating above sql transaction management which is of Outsystem in this case. But this is not what I want. I just want to rollback the changes of the action in which the error has occurred and not of all other actions executed before.

Any help guys. This is an urgent issue so your guidance is required.


Regards,
Shiva

Hello Shiva,

The OS platform has a commit transaction action which is available in the Built-in actions (left menu in SS). Using this action commits the data that is set to the SQL-tables.

You can try to use this action as the final action in the flow (so as final in the flow A, B, C or D). Meaning this action is called when nothing goes wrong and the data is transmitted to the SQL server.

If an error occures in the other action this data is already saved (and committed) into the SQL server.

Kind regards,
Evert
Dear Evert,

That did the trick. Problem solved!!!

You are a star. Thank you so very much.

Cheers and have a great day ahead.

- Shiva