Import Excel to different tables in database

We may need to use a two-step process for this requirement:

1. **Upload to a Staging Table**: First, when the user uploads the Excel file, the entire data can be written to a temporary staging table. This ensures that the raw data from the Excel file is captured in a structured manner without directly affecting the main tables. Any validations or initial transformations can also be performed here.

2. **Populate Data to Main Tables**: After the data is in the staging table, we can read the relevant information and populate the required main tables—such as the *deals* table, *loans* table, and a separate table for the deal amount values. The necessary business logic to relate deals and loans should be handled in this step.

3. **Clear Staging Table**: Once the data is successfully written to the main tables, the staging table can be cleared, ensuring it's ready for the next upload 

We may need to use a two-step process for this requirement:

1. **Upload to a Staging Table**: First, when the user uploads the Excel file, the entire data can be written to a temporary staging table. This ensures that the raw data from the Excel file is captured in a structured manner without directly affecting the main tables. Any validations or initial transformations can also be performed here.

2. **Populate Data to Main Tables**: After the data is in the staging table, we can read the relevant information and populate the required main tables—such as the *deals* table, *loans* table, and a separate table for the deal amount values. The necessary business logic to relate deals and loans should be handled in this step.

3. **Clear Staging Table**: Once the data is successfully written to the main tables, the staging table can be cleared, ensuring it's ready for the next upload 

Okay

Thank you.

Hey  ,

I have tried but there are some date fields in the excel and when the file is uploaded the date fields are updating as null.

Any Idea what to do here.

Thank you.

As said in your other discussion post.

Please change all the columns in the database table to varchar() then the import will succeed. And you will have all your data from excel in the staging table as text. Then you can manipulate the data as per your need.

Hi!

To import your Excel file into the database, follow these steps:

1. Read the Excel file: Use a library like Pandas (Python) or OpenXML (.NET) to pull data from the file.

2. Process the data: Extract deals, loans, and amount details from the file.

3. Update the database:

   • Insert or update each deal in the deals table.
   • Add loans related to each deal in the loans table.
   • Update the amounts in a separate table for financial data.

This way, all your tables stay in sync with the uploaded file!

Thanks & Regards
Rizwan Team Union Developers

Hi Soma ,

I tried you approach and its working fine.

But I am facing another issue in the process.

When the file is uploaded the data is processing properly but for deals to loans table we have one to many relationship. so when we have multiple loans the deal id is not storing accordingly.

The code is written based on index in deals record, but the data for loans we are using it from datastore so the index for deals is not proper.

The above picture is for calculating Index.

The above pictures are for loans.

Can you help here.

Thank you.