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