Read excel and write to other excel file
Hi, I have a excel file(.xls) like these: ID Name Group Seq Acno Addition Subtraction 1 ABC A 1 0111 5 0 2 BCD B 2 0222 9 0 6 0 3 EDF B 1 0333 0 2 The Seqno column has values depending upon the value of the sequence number the no.of columns will change I need to create a output (.xls) file like these: ID Name Group Seq Acno Amount 1 ABC A 1 0111 5 2 BCD B 2 0222 9 2 BCD B 2 0222 6 3 EDF B 1 0333 -2 My requirement is depending upon the sequence number no.of rows will be added. Can anyone direct me in achieving the above requirement. Thanks, latha
July 12th, 2011 2:53pm

Hi latha, You basically is adding a raw so a Script Transformation can be used similar to what is described in this article: http://www.bimonkey.com/2009/09/the-script-transformation-part-2-as-a-source/Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
July 12th, 2011 2:59pm

is the number of columns different ? or they are static? for example how many columns you expect to see after 6 0 ?http://www.rad.pasfu.com My Submitted sessions at sqlbits.com
July 12th, 2011 3:40pm

The number of columns are not static. Depending upon the Seqno the no.of columns changes. For eg: in above second row has seq no=2 so two extra columns (6 0) added if it is 3 there will be still 2 more rows added(like 6 0 8 0) like that.latha
Free Windows Admin Tool Kit Click here and download it now
July 12th, 2011 3:55pm

@arthur: Is there a possibility of using script task than script component.latha
July 12th, 2011 3:56pm

They are similar, but expose different methods, Since you need to add a raw to the buffer you ought to use the Script Transformation.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
July 12th, 2011 4:04pm

you can do this with script component as source, this is a sample of script component as source: http://www.rad.pasfu.com/index.php?/archives/38-Script-Component-as-Source-SSIS.html in the script component you should manually connect to excel from code and read data from it, this is a sample c# code to read data from excel file: http://www.davidhayden.com/blog/dave/archive/2006/05/26/2973.aspxhttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com
July 12th, 2011 4:15pm

Have a look at the blog I have on splitting single line to multiple lines. I have used an example of flat file you could use Reaz's link to get the execl connectivity.My Blog | Ask Me | Test your SSIS skills
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2011 5:06am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics