Delete 3rd column in excel sheets using SSIS package

SAJID RAZA KHAN 1 Reputation point
2022-05-02T23:23:32.427+00:00

Hi,

I have 10 Excel files in a folder D:\Excel_Files\ (file_1.xlsx, File_2.xlsx ......and so on)

Each excel file has one sheet. The name of the sheet is same as file name.

This is what I wanted to do;
The 3rd column of all 10 excel sheets needs to be deleted. I am using Microsoft Visual studio 2010 and I want a script task code which can delete the 3rd column of all 10 excel sheets.

thanks in advance.

Sajid

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,518 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 35,226 Reputation points
    2022-05-03T07:20:28.657+00:00

    Hi @SAJID RAZA KHAN ,

    Please check below code.

    string[] files = Directory.GetFiles(@"C:\Test", "*.xlsx");  
    foreach (var item in files)  
    {  
    Excel.Application excel = new Excel.Application();  
    Excel.Workbook workbook = excel.Workbooks.Open(item);  
    Excel.Worksheet worksheet = workbook.Worksheets["Sheet1"];  
    worksheet.Columns[3].Delete();  
    workbook.Save();  
    Console.WriteLine("yes");  
    }  
    

    Don't forget to add Microsoft.Office.Interop.Excel in NuGet package.

    198428-image.png

    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.

    1 person found this answer helpful.