none
How to remove a cells range in an Excel using SSIS

    Dotaz

  • Hi there,

    I've got an Excel with two sheets: let's say A and B. My goal is remove the contents of sheet 'B' B:K cells, and then, put fresh data from a SQL server table.

    So, at the end, I need to respect the contents of sheet 'A' and the rest of the cells from B: A and L on.

    Thanks,

    pátek 18. května 2018 6:47

Všechny reakce

  • you can define a table for the range B:K in Sheet B inside excel sheet 

    Then inside SSIS have a standard data flow task with oledb source pointing to your sql server source and have excel destination. Inside Excel destination choose the newly created table and it will export data to the cell ramge within excel

    You can see an example here

    http://getsetsql.blogspot.ae/2012/01/using-ssis-load-data-to-excel-sheet-at.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    pátek 18. května 2018 7:07
  • ok, using Microsoft Office 12.0 Access Database Engine OLEDB provider I could read ranges (SELECT * FROM [Raw Data$B2:K6])  but.. how can I figure out ahead the scope of the range to be deleted ?

    Ideally from OLEDB Source I would do this:

    INSERT INTO [Raw Data$B2:K2000]

    SELECT COL1,COL2 FROM <my table> where..

    Sometimes I need to delete 2000 rows and other time 2500.

    pátek 18. května 2018 7:17
  • Hi Enric Vives,

    If you want to clear all data of one column, you can just set bigger value in the particular range, for example

    DROP TABLE `Raw Data$B2:10000`

    Otherwise you may need a variable combined the expression so that you can change the value dynamically.

    Also see: SSIS Delete Rows in Excel File without using Script Task

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    pondělí 21. května 2018 2:53
  • ok, using Microsoft Office 12.0 Access Database Engine OLEDB provider I could read ranges (SELECT * FROM [Raw Data$B2:K6])  but.. how can I figure out ahead the scope of the range to be deleted ?

    Ideally from OLEDB Source I would do this:

    INSERT INTO [Raw Data$B2:K2000]

    SELECT COL1,COL2 FROM <my table> where..

    Sometimes I need to delete 2000 rows and other time 2500.

    Why do you delete rows?

    Please note that deleting rows doesn't work well using OPENROWSET or other methods

    You can update the rows to make them blank but even them rows will remain as blank rows inside excel and will get returned when you select from the sheet as NULL rows using most providers

    May be you can give us bigger picture for us to suggest a better alternative


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    pondělí 21. května 2018 6:04