January 20, 2014 at 8:08 am
Hi,
am new to SSIS. i have Excel sheet like,
Column 1 Column 2 Column 3
Zone Prefix Prefix1
Zone 1 044 54121,78221,4100
Zone 2 055 1240,142,14578,14501,1400
I need to separate the column3 (Prefix1) with reference of ',' Separator.
is it possible to generate the derived column column4
like 04454121 and Column5 like 04478221 and column6 like 0444100.
The number of comma separator different for each row of the sheet.
Thanks in Advance
January 20, 2014 at 8:18 am
What are you going to do with the data once you have separated the columns - import it into SQL Server, or something else? Is there a maximum number of comma separators that you can have?
John
January 20, 2014 at 8:19 am
Try using a derived column transformation in conjunction with the TOKEN expression.
For example, for what you are calling "Column4" the expression would be something like:
[Prefix]+TOKEN([Prefix 1],",",1)
"Column5" would be:
[Prefix]+TOKEN([Prefix 1],",",2)
....and so on.
http://technet.microsoft.com/en-us/library/hh213216.aspx
January 21, 2014 at 1:45 am
Hi,
After separation ,Need to compare the values in other Excel sheet. The other excel sheet looks like
Excel Sheet 1
Column1 Column 2 Value
044 5142 108.8
044 0200 145.21
044 0100 90.00
Excel sheet 2
Column1 Column 2 Value
044 5142,0200,0100 100.10
055 12010,1452 95
After separation of the ',' in Excel sheet 2 Need to compare the values with Excel sheet 1.
Thanks in advance
January 21, 2014 at 2:05 am
In that case, I think I'd import into SQL Server tables as is, then use a splitter (search this site for how to do that) to separate the values, and finally use a join to do the compare.
John
January 21, 2014 at 4:00 am
Thanks John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply