Read Excel sheet (.xls and .xlsx) and upload data in database table

Read Excel sheet (.xls and .xlsx) and upload data in database table

  
Dear Community,

We are doing some research on Outsystems and wanted to evaluate if we can parse an excel sheet (both .xls and .xlsx) formats and upload its data in database table. I'm completely new to Outsystem so would really appreciate your quick response.

What do I want?

1. I want to call an outsystem web service which will accept a file name parameter of string data type which will be the full path of the file located on server.
2. The webservice should be able to fetch the excel file from that location, parse it and then upload the contents in a database table.

Can you please explain me in detail using some code snippet or at least give me pointers, how to acheive this in Outsystem?

This is really urgent.

Thanks,
Shiva
Dear Shiva,

Hi and welcome to the OutSystems community forums.

The best way to do what you're looking for is to see our How To section in Service Studio. In the start screen, click "See All How To's", and then choose the 4th How To, which should say "Create Tables from Excel Files".

Alternatively, visit the How To section in our website for all How Tos, and open the "Create Tables from Excel Files" one.

I hope this helps.

Regards,

Paulo Tavares
Dear Paulo,

Thank you so much for quick response and your suggestion helped me a lot. I studied the link mentioned in your below response and figured out the in built excel to record list method needs binary data to upload the data in DB table. I thus converted my excel sheet into a byte stream and send it to an Outsystem web service and thus got everything working.

However, my concern is that I do not want to send a heavy file across network to a web service method. As mentioned in my previous post, is it possible to pass the path of a file located on server machine and let the Outsystem read that file and convert into binary on its own. How can I do it?

Awaiting for your response.

Warm Regards,
Shiva
Hi,

Yes, that can be done - and actually, there's a similar topic going on at the moment where one of our community members was discussing doing the exact same thing. Probably the best way to do it is to resort to the FileSystem extension or NetworkFileSystem extension, depending on whether the file is local or stored in a network path.

You may want to read our other topic on the subject, it might give you further information on how to achieve what you're trying to do.

Finally, I hope you don't mind, but I'm going to edit out the URGENT part in the topic title, since everybody always assumes that everybody's problems are urgent, and answer them as soon as possible if they know how to do so. Otherwise, everybody will start creating topics with the URGENT word in it, and then it will just be chaos.

Regards, and let us know how it goes.

Paulo Tavares
Dear Paulo,

Thank you so very much for your input. I have done what I wanted.

You are a star.

Cheers,
Shiva
Dear Shiva,

I'm glad to hear all went well.

Let us know if there's something more you need help with.

Regards,

Paulo Tavares