November 18, 2004 at 3:57 am
I have created DTS package in SQL Server 2000. writen select query and export to excel to the desired location. Whenever i execute the DTS package, the data getting appended in the excel file.
Some one help me,
how i can rename the file for every execution or clear the data before insert into excel if the file name is same?
November 18, 2004 at 4:34 am
When you export data from SQL to excel first time, it creates table or name range in excel. When you again export data to excel to the same name(table) in excel, it appends data to that table. You have to drop table (you can not delete record with delete * from ..., but using drop table ...) first and create existing table(create table ... with the structure) again then export data.
November 18, 2004 at 5:54 am
there are various different ways of resolving this. Create an Excel file with unique name everytime, Have a template Excel file that you populate and save as a different name, blow the data away prior to load.
The last two options will require vb coding inside the DTS package to automate Excel. I am not an Excel expert but I know that clearing the sheet is feasible through automation.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
November 19, 2004 at 3:56 am
Try previous link -
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=93756#bm93832
Also some ones put it in pictures http://www.sqlservercentral.com/columnists/jsack/capturingtheerrordescriptioninastoredprocedure.asp
Hope this helps
November 22, 2004 at 1:10 am
Drop table is working fine. Thanks lot.
Is it possible to create the new file name with current date whenever export into excel, instead of keeping one file dropping the records for every time?
thanks lot
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply