November 30, 2012 at 3:59 am
col1 col2 col3
5.06.03.5
9.59.86.3
9.06.08.0
Col4col5col6
9.56.08.0
6.39.33.9
8.78.08.8
9.08.36.9
8.69.06.2
I have data like this in excel, how to load into 2 different tables
first table contain col1,col2,col3as columns
second table contain Col4,col5,col6 as columns.
Note: Here the number of columns are fixed(3).
November 30, 2012 at 5:54 am
Read everything in with an Excel source.
After that, put a script component as a transformation.
Send all the rows to a certain output, but check first if the value of the first column equals Col4.
Once you reach that row, send all subsequent rows to another output.
Connect your first output to your first table and the second output to the second table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 30, 2012 at 6:08 am
Thank you ,could you plz provide the sample code, i dont no know how to write the code.
November 30, 2012 at 6:27 am
So you would rather blindly take code off the internet, copy paste it into your application and support/maintain it in production? You know you're responsible for the code you deliver, right?
Why don't you try to code it, and once you get stuck somewhere ask specific questions. People are more glad to help with those type of questions instead of the "please do my work for me" questions.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 30, 2012 at 7:01 am
ok thank you, ill try. if i structured any where ill come back to you , thank you again.
November 30, 2012 at 2:20 pm
Some pointers:
1) Create one extra output in your Script Component. And change the ExclusionGroup property for both to a non-zero value (same value for both).
2) The SynchronousInputID property of the new output port must be set to the existing input (same as the first) to make this script synchronous.
3) Now you can use Row.DirectRowToXXXXX(); in your script where XXXXX is the name of one of your outputs
4) You have to come up with an if construction. Here are some hints:
// C# code
bool useSecondOutput = false;
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
if (row.YourColumnName.Equals("bla bla"))
{
// some code
}
// some more code
}
Now do some programming and let us know where you get stuck.
March 20, 2014 at 4:58 pm
If the row ranges are predefined, I would rather use named ranges in the spreadsheet and refer the named range in the excel source; instead of reading entire sheet and then splitting it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply