By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,636 Members | 1,814 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,636 IT Pros & Developers. It's quick & easy.

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

P: 17
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.
Feb 6 '07 #1
Share this Question
Share on Google+
8 Replies


iburyak
Expert 100+
P: 1,017
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.
Feb 6 '07 #2

P: 17
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.
Feb 6 '07 #3

iburyak
Expert 100+
P: 1,017
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.
Feb 6 '07 #4

P: 1
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..
Mar 17 '07 #5

P: 1
I have succesfully executed a dts-package from SQL server 2000 to an excelfile
How can I delete a .xls destinationfile in the Job.
Nov 18 '08 #6

P: 2
@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
May 3 '09 #7

ck9663
Expert 2.5K+
P: 2,878
My recommendation would be to create your target file dynamically. This way, you can have more flexibility.

--- CK
May 4 '09 #8

P: 2
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
May 5 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.