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

[LimaCharlie]

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 I 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.

Note:
I cannot do the drop & recreate table because the users has no permission to do this in the database. Only the DB admin is allowed to drop & create table.

Thanks a lot.

[iburyak]

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 I 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.

Note:
I cannot do the drop & recreate table because the users has no permission to do this in the database. Only the DB admin is allowed to drop & create table.

Thanks a lot.

Create a view that limits number of exported rows and use it in DTS package instead of a table.

Good Luck.

[LimaCharlie]

hi iburyak,

just the same, the records are being appended to the excel file. what i want is to delete the rows in the destination file before inserting new records.

thanks.

[iburyak]

Did you create a view that removes unwanted records?
Execute a view on a Server side and make sure this is the data you want to export, don't create a view to select * from table_name it will not correct a problem.

You need to give me more information then just I want to delete records.
It is a wrong approach to load all records and delete on destination side you need to load only necessary records.

[billion]

Hi Iburyak,

The Question is... this excel file is getting populated everyday and everyday before porting data to the excel the old data has to be erased and new data filled up.

Any help on that.

Thanks in advance..

[tketelaars]

I have succesfully executed a dts-package from SQL server 2000 to an excelfile
How can I delete a .xls destinationfile in the Job.

[carpel]

@LimaCharlie

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

[ck9663]

My recommendation would be to create your target file dynamically. This way, you can have more flexibility.

--- CK

[carpel]

Hello CK,

you are right!

the full version is:

ALTER PROCEDURE [dbo].[ExportStopreProcedureInExcel]
(
@QueryString VarChar(8000) ='',
@MasterFile VarChar(100),
@CopyFile VarChar(100)
)
AS
BEGIN TRY


DECLARE @SQLSTR nvarchar(MAX)

SET @SQLSTR = 'exec master..xp_cmdshell ''copy ' + @MasterFile +' '+ @CopyFile+''''

exec( @SQLSTR)

SET @SQLSTR = '

INSERT INTO OPENROWSET(
''Microsoft.ACE.OLEDB.12.0''
, ''Excel 12.0 Xml;Database=C:\GiorniLavoroSuiFV.xlsx;''
, ''SELECT * FROM [foglio2$]''
)
'+ @QueryString


EXEC (@SQLSTR )


END TRY
BEGIN CATCH
DECLARE @ErrMsg VARCHAR(8000),
@ErrSeverity INT,
@ErrState INT;

SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY(),
@ErrState = ERROR_STATE();

RAISERROR (@ErrMsg,
@ErrSeverity,
@ErrState
);
END CATCH

'If anything can go wrong, it will' (Murphy)

See you soon ...carpel