SSIS and add totals in the column in excel file

Question

Hi All,
We have excel files that we get from vendor. The file has 4 columns. In that we need to get total for each column (for 2 columns). The total for each column should be as last row. For example:

Excel File:
column1 column2 column3 column4
1 xx 40.00 41.30
2 yy 50.50 51.00

I need to update excel file like this:
column1 column2 column3 column4
1 xx 40.00 41.30
2 yy 50.50 51.00
90.50 92.30

and save the same file.

Is it possible.
Thanks

Answer 1

Hi @Spunny ,

Why not just edit the excel to get the total? I think it could be more easier compared with SSIS.

add-a-total-row-to-a-table-9885a56c-51b5-487a-a168-054afd034631

If you want to do that in SSIS, you may use script task for a try which will be a little complex.

add-subtoal-as-new-row-in-data-flow-task-ssis

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi Zoe,
Thanks For response. We get multiple files from vendor without sums daily. We need to sum on certain columns in each file and email them every day. we are manually opening each file and doing sums, saving it and emailing. It's getting tedious. So, we want to automate the process.

Answer 3

I need code in activex script task to loop through excel files, open each file, sum on certain column and add as total to last row and save the file.