Data upload from Excel to Database Table in dynamic way

Hi Guys,

I had a scenario where i need to upload the Excel with Header and Data Coming with different Structure for each Plant,below is the Sample Data Structure

Each time I upload the Data with same/different Structure,I need to DELETE the HEADER and DATA table shown below where we will be storing the data

The HEADER  and DATA Table to be created Dynamically


For Sample Understanding Data with 5 Columns(Can have max 200 Columns only) are shown below


am trying with Excel Packages in outsystems,Can  any one suggest me on this with the oml file for reference.


Thanks

Sasidhar

Hi,


what's the problem with excel-components from the forge?

you can read every cell one by one, so that should not be a problem.


Adding to J answer.

What do you mean by "created dynamically"?

You see, you CAN'T create or drop tables, dynamically, in pure OutSystems.

And if you do this through an Extension, your OutSystems code will not know anything about those tables, and you'll have to do a LOT of code to work with it, basically defeating the platform purpose on being a rapid development tool.

So, don't do that. Change the database model and/or your excel file structure so that you become able to handle your requirement entirely from OutSystems.

Cheers.

P.s. I understand that you want to upload FROM the excel to database.

If it is not, please, elaborate your question to make it more explicit about what you need to achieve.

J. wrote:

My Input Excel File had around 200 Columns and 5Lacs of records which is hitting the Performance by using excel component and reading cell by cell

Each time I receive different Excel with different Columns and Data,Our Logic should be capable of Storing this Data into Database with the Header in header Table and Data in the DataTable as shown in the Diagram shown above conversation

Hi,


what's the problem with excel-components from the forge?

you can read every cell one by one, so that should not be a problem.




P.s. I understand that you want to upload FROM the excel to database:Correct


I am getting huge data from excel each time and with different structure So our Code should handle this.

am planning to store the header and Data in two separate tables which can handle this scenario I guess,If not please suggest me if any alternate method


Eduardo Jauch wrote:

P.s. I understand that you want to upload FROM the excel to database.

If it is not, please, elaborate your question to make it more explicit about what you need to achieve.



Hi Sasidhar,

1. What's the purpose of storing this data?

2. How your program use this data?

Cheers

Hello Eduardo,

Appreciate your efforts 

 1. What's the purpose of storing this data?--Data Science team is going to utilize this data we are planning have connection in the form of API

2. How your program use this data?--We will give drop down of the Columns in Excel for Selection(Multi).These selected column by end user will be sent to Data science team using  the API.

They will process only those selected columns data from the Data table and do some Predictive Analysis


I would like to know if there is any way to share the Excel data without storing in Database which is time consuming when we had Huge Data


Please suggest me the possible ways to achieve  this

Hello Sasidhar,

If your app will just store data to send away in an API, without processing it or using it any way, maybe you can avoid this. 

You could just store the header information from the file and the file itself, and upon request from data team retrieve the information from the file. But this probably will just postpone the problem (assuming the problem is in the processing, not in the upload).

You may also try to convert excel to CSV that is much faster to process, but this would work only if your excel had a consistent number of lines for each column. If not, you could preprocess your file to create multiple files with groups of columns that have the same number of lines, convert to CSV and then upload and process to store in database.

Cheers