Uploading data from excel to database

How can we upload data from excel to outsystems database or to external database from UI screen?

Hi Anasuya,

This is so incredibly basic, that I can only ask you to follow the tutorials. Pretty early on this is explained in great detail (hint: it's very easy!).

Hi ..I am new to outsystems .So I am learning now..I have  created the basic functionality by the upload widget on front end and creating a screen action to the upload button and connecting a server action to the the screen action.The server action has been made as it is done in bootstrap from excel functionality.But on clicking on submit button it is giving "Error - Empty (0 bytes) excel file received." error.I have been trying the fixes given in the community but of no benefit.

Use Excel To Record List action.



Your Excel binary (from upload widget) should be put into File Content property.

You also need record definition, create structure that mimic your excel file columns.

Lastly, choose Sheet Name, or empty it for first sheet.

Anasuya Chakraborty wrote:

Hi ..I am new to outsystems .So I am learning now..I have  created the basic functionality by the upload widget on front end and creating a screen action to the upload button and connecting a server action to the the screen action.The server action has been made as it is done in bootstrap from excel functionality.But on clicking on submit button it is giving "Error - Empty (0 bytes) excel file received." error.I have been trying the fixes given in the community but of no benefit.


Hi Anasuya,

How are you passing the uploaded Excel file to the Excel To Record List tool?

Thanks Harlin for your response.

I have done this.But still the error is coming.I think I need to do some change to the widget properties or UI. I ahve bootstrap an empty excel with the column headers to my entity.However I am unable to select this value in Record Definition.

Hi Martins..I have one upload widget in UI and upload button to Submit.The destination of the button is one screen action where I have linked a server action similar to the above screenshot.

Anasuya Chakraborty wrote:

Thanks Harlin for your response.

I have done this.But still the error is coming.I think I need to do some change to the widget properties or UI. I ahve bootstrap an empty excel with the column headers to my entity.However I am unable to select this value in Record Definition.

Hi,


You can share the solutions please?


Hi Thiago,

What is it you don't know how to do? Anything specific?

Kilian Hekhuis wrote:

Hi Thiago,

What is it you don't know how to do? Anything specific?

Hi,


I have a systems who generate EXCEL daily and i want to make the import flow with this excel



Yes, but what don't you know how to do? Reading an Excel is done via the Record List to Excel Statement, or in case the Excel is more complex, via a Forge Component like Advanced Excel. Once you have the data in a List, you can use the normal OutSystems way of storing that (For Each, CreateOrUpdate, etc.).

Kilian Hekhuis wrote:

Yes, but what don't you know how to do? Reading an Excel is done via the Record List to Excel Statement, or in case the Excel is more complex, via a Forge Component like Advanced Excel. Once you have the data in a List, you can use the normal OutSystems way of storing that (For Each, CreateOrUpdate, etc.).

Thanks, i will look the Forge Component



Hi,


I am trying to implement Excel to RecordList, using the Import button from UI. The Excel sheet will be Updated Weekly and the Updated Excel need to be imported to the existing Entity. Where as i am not getting the Updated excel imported if i am using the Above solution.

Can anyone please help me with this?

I want to import excel(Updated) from my local, every time i click on Import Button. What should i choose in File Content exactly, to get the Updated Excel everytime.


Regards,

Manasa.Ks

Hi Manasa K.S,

Like the original poster, you need to have an Upload widget on your screen, use the Submit method on your Link/Button, and then, on your screen action, use the Upload widget's Content runtime property as the value for the File Content property of the ConvertToExcel tool (instead of Resources.Policy_xlsx.Content like you have on the screenshot).

Hope this helps!

Hi Jorge,


Thanks for the reply.

Form the App side user won't be knowing which file to be Uploaded, this solution might not suit my requirement.

Could you please tell me is there any way, where I can define the path of the file to be chosen as predefined?

I want just an import button where the File from particular location need to be imported, where can give the path in Outsystems. Instead of Upload(can I just make its Visibility : false and five the file path ), is there a way?


Thanks & Regards,

Manasa.Ks




Manasa,

You can use the FileSystem forge component to read the file from the server's file system, would that meet your requirements?

Hi Jorge,

Jorge Martins wrote:

Hi Manasa K.S,

Like the original poster, you need to have an Upload widget on your screen, use the Submit method on your Link/Button, and then, on your screen action, use the Upload widget's Content runtime property as the value for the File Content property of the ConvertToExcel tool (instead of Resources.Policy_xlsx.Content like you have on the screenshot).

Hope this helps!

I tried giving the Upload widget's Content, But even file contains the Data I am getting the error - Empty file.

Could you please tell me what's the mistake.


The Uploaded file have the Data, still I am getting no data present. can you please help me with that.


Thanks & Regards,

Manasa.Ks


Hi Jorge,

Jorge Martins wrote:

Manasa,

You can use the FileSystem forge component to read the file from the server's file system, would that meet your requirements?

In the FileUpload server action in FileSystems will take the Path from Upload widget, right?

How can I point it to a local path, without using Upload/browse action.


Could you please him me to achieve it.


Regards,

Manasa.Ks


Hi Manasa.Ks,

I have seen your post in multiple places...here is an answer that might point out what the issue is.

As you've mentioned in multiple posts, here is a summary of your scattered issues:
1) you want a file uploaded to an OutSystems application, for further processing/availability.
2) the file is placed/created locally and additionally: New versions of the file are overwritten locally, the file content changes...
3) the user should be able to just push a button, the application loads the updated file, without the user knowing where the exact location of the file is...

Meaning: You cannot add the file to your bootstrap method, because then only that version is being used in bootstrapping, not the next updated version(s). Adding a file to your resources..means: it is static from that moment onwards.
You cannot use the Upload or File Upload widget, because that serves the user with a UI to select a file.

The application should look for the file itself, that can be done with the help of the FileSystem component, but... you need to make sure the FileSystem component has access to the specific location.

A different way to approach this... is to have the party that generates/updates the file, also send the same updated file to an API you create. This way you skip the whole...the application needs to look for the file..to: the new file is delivered to the application.

Hope I have you a bit more insight...

Regards,
Jeroen

Manasa K.S wrote:

Hi,


I am trying to implement Excel to RecordList, using the Import button from UI. The Excel sheet will be Updated Weekly and the Updated Excel need to be imported to the existing Entity. Where as i am not getting the Updated excel imported if i am using the Above solution.

Can anyone please help me with this?

I want to import excel(Updated) from my local, every time i click on Import Button. What should i choose in File Content exactly, to get the Updated Excel everytime.


Regards,

Manasa.Ks

Hi Manasa,


Your issue is that You are using content of file in your resource folder. 

The file content should be content of your uploaded file.

Hope this helps.


Regards,

Saugat



Solution

Manasa K.S wrote:

Hi Jorge,

Jorge Martins wrote:

Manasa,

You can use the FileSystem forge component to read the file from the server's file system, would that meet your requirements?

In the FileUpload server action in FileSystems will take the Path from Upload widget, right?

How can I point it to a local path, without using Upload/browse action.


Could you please him me to achieve it.


Regards,

Manasa.Ks


Hi Manasa K.S,

I don't think your requirements can be satisfied, at least how I have understood them.

Let me explain why, borrowing a lot of info Jeroen explained earlier:

  1. you want to process an external file via an OutSystems application. This means that somehow the server needs to have access to the file's contents
  2. you want the file used by your OutSystems application to be updated. This means that you cannot use a static file resource (like automatically generated bootstrap logic does)
  3. you don't want the end-users of the OutSystems application to provide the updated external file to your application. This means you need your OutSystems application to be able to find the file on its own, either because the server where the application is running:
    1. has direct access to the file: via network sharing or;
    2. can download directly via HTTP or FTP (for instance) or;
    3. defines an API (like Jeroen suggests), to be called by whatever other party updates the file (a SOAP Web Service/REST API method that receives the updated file and is called by another application).
  4. you are ok with the end-users of the OutSystems application to trigger the update of the file. This means your users would click a button that would tell your application to go and look for the file in a predefined location (see 3.1. and 3.2. above).

Regarding 3., there is no way I'm aware that would allow your OutSystems web application to know where to look for a file on the end-user's computer: this would be an important browser security breach. The only way to upload a file from the end-user's computer is if the users themselves select the file via the browser's UI.

You can use the FileSystem forge component I mentioned early to implement 3.1 above, by having a network share that is accessible from your OutSystems server, and also by whomever (or whatever system) updates the file.

You can use a solution similar to what is described in this post to download the current version of the file to the server if it is directly accessible via HTTP. If your file is in an (S)FTP server, you may be able to use one of the FTP-related forge components available to retrieve it.

Regarding 4., although you are ok with the end-user triggering the update of the file, a better approach would be to either have the retrieval be triggered automatically (via a Timer running regularly) or by providing an API as suggested in 3.3 that would allow the OutSystems application to receive the new version of the file (this would require the modifier of the file to call the API, of course, and this isn't always possible)

I know this is a lot of info, but hope this helps clarifying your options.

Solution