| Author |
Topic |
|
vikram1
Starting Member
13 Posts |
 Posted - 2005-12-26 : 04:03:49
|
Hi All, I have a problem while i finish running my dts package.when my processing is over and i have got the results in a excel file the next time i want to use the same file i have to to extract the previous run data and keep it somewhere and delete the rows so that dts doesnt append the information that i will get in the next run.This process is very time consuming because i have certain packages which give there results in more than 100 workbooks  .now i have to manually clear all the workbooks keeping in mind that the top row which contains the heading doesnt get deleted.if someone knows the solution to automate this process pls let me know.it would be a big help. |
|
|
vikram1
Starting Member
13 Posts |
Posted - 2005-12-26 : 23:17:52
|
| wat happened guys i hope somebody knows solution to this?????????? |
 |
|
|
mcarahul
Starting Member
11 Posts |
Posted - 2005-12-29 : 05:41:39
|
| If you can take "csv" file intead of "excel" file, your problem is solved. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-12-29 : 06:18:50
|
| Why not create an Excel "template" with the required column headings/worksheets, use it in the DTS and then save the output as a workbook?....it's the right approach to take if you were loading the data manually. |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2005-12-29 : 13:56:35
|
| You could also write a Excel macro to clear the speadsheet on the launch of the file. That will ensure you always have a clear speadsheet |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2005-12-29 : 13:56:42
|
| You could also write a Excel macro to clear the speadsheet on the launch of the file. That will ensure you always have a clear speadsheet |
|
|
|
vikram1
Starting Member
13 Posts |
Posted - 2005-12-30 : 02:07:34
|
| taking template would reduce automation as i will have to save it again in a workbook.......how would csv file change my scenario...pls can u elaborate the solution..... |
|
|
|
mcarahul
Starting Member
11 Posts |
Posted - 2005-12-30 : 03:43:14
|
| IF u use the csv file then after every run DTS will not append the rows in ur file. It will update the updated records and will not duplicate the rows in ur csv file. Also you can set the properties of csv task to get the first row as columns from the database. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-12-30 : 07:50:57
|
| "taking template would reduce automation....."why would it?....you open fileA (template), modify it and save it as fileB (workbook).Have you tried this approach? |
|
|
|
vikram1
Starting Member
13 Posts |
Posted - 2006-01-03 : 05:28:24
|
| ya i have but again it needs copying.....well but i think csv solution might just do the trick.....gonna try it ...thanks... |
|
|
|
pofo14
Starting Member
1 Post |
Posted - 2006-01-05 : 18:11:28
|
| I had the same issue and resolved this problem by doing the following:1) Create an excel template file, with the corresponding Worksheets and columns you need.2) In you DTS Script create a 1 Connection for your DB with the dat, and one to a temp Excel file. 3) Then create you DataPump Tasks's that populate the worksheet's in the excel file.4) Create and ActiveX Task that will load the excel template, and create a new excel workbook from that template. Then change the filename property on the Excel Connection defined in the DTS script to be the new workbook you just created. 5) Make a on success workflow transition from the ActiveX script to you DB connection so it ensures that a new workbook is created before any data is extracted.Every time the DTS is run a new excel file will be created witht eh dat extracted. I will post sample code to the forum once I am logged onto my PC with the code. |
|
|
|
DougTheHealer
Starting Member
1 Post |
Posted - 2006-01-13 : 12:09:46
|
| pofo's reply looks great.Also, you can start the package with an ActiveX object to copy out the file from the prior run (example below). Then either use an ActiveX again to copy in an empty copy to the destination as a template (easiest, I think), or use an Execute SQL Task to "create table" in the destination.ActiveX example:'**********************************************************************' Visual Basic ActiveX Script'******************************************************************' This script copies a file, in effect archiving it.'' This ActiveX script can be the first job step, for scheduled jobs' that create files.'******************************************************************Function Main() Set objFSO = CreateObject("Scripting.FileSystemObject") Myfile = "\\Srvr1Name\Dir1\DestinationFile.xls" If objFSO.FileExists(Myfile) Then objFSO.CopyFile "\\Srvr1Name\Dir1\DestinationFile.xls", "\\Srvr1Name\Dir1\DestinationSave.xls" Else 'Process it End If Main = DTSTaskExecResult_SuccessEnd Function |
|
|
|
vikram1
Starting Member
13 Posts |
Posted - 2006-01-17 : 01:25:00
|
| hi pofo i think your solution will work but could you pls elaborate the 2nd point ...it would be gr8 if you could do that...thanks |
|
|
|
vikram1
Starting Member
13 Posts |
Posted - 2006-01-17 : 03:05:15
|
| hi DougTheHealer your solution solves the problem to some extent i mean it does copy the data from one file to another (so that the previous run data is saved always) but how to delete from the previous file i mean copying and deletion simultaneously.Moreover will it work for multiple worksheets in a workbook and also is it possible to automize the creation of the template. |
|
|
|
vikram1
Starting Member
13 Posts |
Posted - 2006-01-17 : 06:23:01
|
| hi DougTheHealer thanks a lot!!!!!!!!!!! for your suggestion ...it has worked for me what i have done is basically taken three template files as the files which can contain output.output goes only in one and then after that in the next run it is copied into the 2nd template workbook and the ten i copy the blank third workbook to the first one so that i get a blank workbook always in the begning.thanks.. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-01-17 : 06:46:06
|
Can me and pofo14 claim (some) credit for suggesting the template route, albeit not as the end-solution? |
|
|
|
vikram1
Starting Member
13 Posts |
Posted - 2006-01-18 : 04:28:40
|
| Well thanks a lot to all the people especially :pofo14Andrew murphyDough the healer |
|
|
|
13sara
Starting Member
1 Post |
Posted - 2006-01-26 : 14:14:49
|
| There is actually a really easy way to do this without templates. You can use a drop table/create table statement on the excel spreadsheet. (It will drop and recreate the sheet within the file - note: In my DTS it doesn't affect the other sheets, only the one I said to drop but test it first)Put the following code in a execute sql task and run it against the excel connection.DROP TABLE 'SheetName'CREATE TABLE `SheetName` (`Column1` VarChar (255) , `Column2` VarChar (20))The sheet will be called whatever you put in 'SheetName' |
|
|
|
vikram1
Starting Member
13 Posts |
Posted - 2006-01-27 : 04:37:20
|
| This solution might become really scary when by mistake some other user on the DTS might make use the same table name as my Spreadsheet.....which can be a case and this might hinder normal excution of the other users package. |
|
|
|
accbi
Starting Member
1 Post |
Posted - 2007-05-09 : 07:13:25
|
quote:
Originally posted by pofo14
I had the same issue and resolved this problem by doing the following:1) Create an excel template file, with the corresponding Worksheets and columns you need.2) In you DTS Script create a 1 Connection for your DB with the dat, and one to a temp Excel file. 3) Then create you DataPump Tasks's that populate the worksheet's in the excel file.4) Create and ActiveX Task that will load the excel template, and create a new excel workbook from that template. Then change the filename property on the Excel Connection defined in the DTS script to be the new workbook you just created. 5) Make a on success workflow transition from the ActiveX script to you DB connection so it ensures that a new workbook is created before any data is extracted.Every time the DTS is run a new excel file will be created witht eh dat extracted. I will post sample code to the forum once I am logged onto my PC with the code.
|
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-05-09 : 08:30:58
|
quote:
Originally posted by accbi
quote:
Originally posted by pofo14
I had the same issue and resolved this problem by doing the following:1) Create an excel template file, with the corresponding Worksheets and columns you need.2) In you DTS Script create a 1 Connection for your DB with the dat, and one to a temp Excel file. 3) Then create you DataPump Tasks's that populate the worksheet's in the excel file.4) Create and ActiveX Task that will load the excel template, and create a new excel workbook from that template. Then change the filename property on the Excel Connection defined in the DTS script to be the new workbook you just created. 5) Make a on success workflow transition from the ActiveX script to you DB connection so it ensures that a new workbook is created before any data is extracted.Every time the DTS is run a new excel file will be created witht eh dat extracted. I will post sample code to the forum once I am logged onto my PC with the code.
Did you want to give any information?  MadhivananFailing to plan is Planning to fail |
|
|
|
Next Page
|
DATA Deletion from Excel file