Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2005
    Posts
    119

    Unanswered: Exporting data from SQL table to Excel File - How to delete rows before inserting new

    Hi,

    Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can erase all the records in my excel file before i export the new data from SQL table?

    What i want is to delete the rows in the destination file before inserting new records.

    Thanks a lot.

  2. #2
    Join Date
    Feb 2004
    Posts
    193
    Is it possibe to create a link to the Excel and run a delete statement from the SQL Server side?

    http://www.informit.com/guides/conte...eqNum=135&rl=1

    ddave

  3. #3
    Join Date
    Feb 2004
    Location
    Hamburg, Germany
    Posts
    22
    LimaCharlie, take a look at this post:

    http://www.dbforums.com/showthread.php?t=981661

    Cheers

    kbk

  4. #4
    Join Date
    Oct 2005
    Posts
    119
    hi kbk,

    that was my first design: i drop then recreate the table and it was working properly. but the problem is, the users are not permitted to drop & create tables in the database (only me & the DB admin are allowed to do this). so what i did was create a temporary table, from this temp table i export the data to excel. is there any other way so i can delete the rows before inserting new?

    thanks a lot.

  5. #5
    Join Date
    Feb 2007
    Posts
    38

    Post

    What i want is to delete the rows in the destination file before inserting new records.

    I sugggest delete the worksheet instead of delete all the rows. Then recreate the worksheet later.

    1. Create an activeX script as below: -This will remove the worksheet.

    Function Main()

    Dim srccsvfile
    Dim objExcel
    Dim objWorkbook, objWorksheet

    srccsvfile = "C:\Test.xls"

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False
    objExcel.displayalerts = False

    Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
    Set objWorksheet = objWorkbook.WorkSheets("New_Table")
    objWorksheet.Activate
    objWorksheet.Delete 'this is removing the worksheet instead of rows
    objWorkbook.Save 'you must save the change otherwise in trouble

    objExcel.Workbooks.Close
    Set objWorkbook = Nothing
    objExcel.Quit
    Set objExcel = Nothing
    Main = DTSTaskExecResult_Success
    End Function

    2. Create Execute SQL task as below: - create the worksheet again
    CREATE TABLE `New_Table` (
    `OfficePK` VarChar (8) ,
    `DealerGroupFK` VarChar (4) ,
    `StateManagerGroupFK` VarChar (20) ,
    `OfficeType` VarChar (1) ,
    `OfficeName` VarChar (255) ,
    `OfficeAddress1` VarChar (255) ,
    `OfficeAddress2` VarChar (255) ,
    `OfficeSuburb` VarChar (255) ,
    `OfficeState` VarChar (255) ,
    `OfficePostCode` VarChar (255) ,
    `OfficeCountry` VarChar (255) ,
    `OfficePhone` VarChar (255) ,
    `RIOfficeID` VarChar (50) ,
    `RIFranchiseName` VarChar (255) ,
    `VPServerIP` VarChar (50) ,
    `LastReceived` DateTime
    )
    3. Then use data transform as you are doing now.
    Make sure that the destination table name is New_Table and the connection is Excel.

  6. #6
    Join Date
    Oct 2005
    Posts
    119
    Hi TerryP,
    I'll try this.
    Thank you very much. =)

  7. #7
    Join Date
    May 2009
    Posts
    1

    Lightbulb

    Quote Originally Posted by TerryP
    What i want is to delete the rows in the destination file before inserting new records.

    I sugggest delete the worksheet instead of delete all the rows. Then recreate the worksheet later.

    1. Create an activeX script as below: -This will remove the worksheet.

    Function Main()

    Dim srccsvfile
    Dim objExcel
    Dim objWorkbook, objWorksheet

    srccsvfile = "C:\Test.xls"

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False
    objExcel.displayalerts = False

    Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
    Set objWorksheet = objWorkbook.WorkSheets("New_Table")
    objWorksheet.Activate
    objWorksheet.Delete 'this is removing the worksheet instead of rows
    objWorkbook.Save 'you must save the change otherwise in trouble

    objExcel.Workbooks.Close
    Set objWorkbook = Nothing
    objExcel.Quit
    Set objExcel = Nothing
    Main = DTSTaskExecResult_Success
    End Function

    2. Create Execute SQL task as below: - create the worksheet again
    CREATE TABLE `New_Table` (
    `OfficePK` VarChar (8) ,
    `DealerGroupFK` VarChar (4) ,
    `StateManagerGroupFK` VarChar (20) ,
    `OfficeType` VarChar (1) ,
    `OfficeName` VarChar (255) ,
    `OfficeAddress1` VarChar (255) ,
    `OfficeAddress2` VarChar (255) ,
    `OfficeSuburb` VarChar (255) ,
    `OfficeState` VarChar (255) ,
    `OfficePostCode` VarChar (255) ,
    `OfficeCountry` VarChar (255) ,
    `OfficePhone` VarChar (255) ,
    `RIOfficeID` VarChar (50) ,
    `RIFranchiseName` VarChar (255) ,
    `VPServerIP` VarChar (50) ,
    `LastReceived` DateTime
    )
    3. Then use data transform as you are doing now.
    Make sure that the destination table name is New_Table and the connection is Excel.

    How to delete rows in the destination file before inserting new records.

    I suggest create worksheet Master with only header row and copy the worksheet Master into worksheet to fill every time.

    Steps to do in a store procedure are:
    1. Create a “Master.xlsx” worksheet with header columns: Name and Date
    2. exec master..xp_cmdshell 'copy c:\Master.xlsx c:\test.xlsx'
    3. INSERT INTO OPENROWSET(
    ''Microsoft.ACE.OLEDB.12.0''
    , ''Excel 12.0 Xml;Database=C:\ test.xlsx;''
    , ''SELECT * FROM [foglio1$]''
    )
    SELECT [Name], GETDATE() as Date FROM msdb.dbo.sysjobs

    Happy coding by carpel

  8. #8
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    I would simply create a template like above, it is the fastest method. but I would do it a little differently
    1. Copy the worksheet with rows already as Master_template.xls. Then I would format the sheet all pretty and such. Then I would delete all of the rows of data. Because I normally make my titles bold, I leave row two hidden which is not bold. (you will see why in a sec)
    2. Then I would create my package. I would create a new data flow and choose execute sql task. And use step 2 above.
    Then I would create a data transformation that copies data from sql to excel destination file. Starting at row 3.
    3. Then execute package to test. then open the excel file and take a look. If you don't do the row two hidden, all records will be bold.

    the problem if you use an activex script task excel usually needs to be installed on the sql server in order for the dim object to open. None of our production servers have microsoft excel installed for security reasons.

    Note: have numbers/and text in a column being returned to excel from SQL and your data comes out as null? In older versions of SQL 2000 and earlier you would need to disconnect edit on the excel destination oledb properties, Extended properties and add the IMEX=1 on the line: Excel 8.0;HDR=YES;IMEX=1; this will ensure each record is copied as viewable in sql;
    In SSIS it is easier because now you edit the code directly to define your datatypes in visual studio.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •