how to format a concanated field at DTS
-
hi guys, i have a small problem, I am doing a dts from an excel spreadsheet to a sql server table. the fields are the area code and the phone number column. I need to concanate these two columns into one column in my sql server table. that part is done; however i also need to format this numbers (right now coming out as 3055625889 instead of (305) 562-5889, I have to do this from the dts point of view, can someone help me??? this is what i have so far..
i did this in the tranformation active xscript
Function Main()
DTSDestination("Account Manager Phone") = DTSSource("ACCT MGR AREA CODE") & "-" & DTSSource("ACCT.MANAGER PHONE")
DTSDestination("Top Parent Name") = DTSSource("SYSTEM NAME")
Main = DTSTransformStat_OK
End Function
two problems with this transformation:
1. but my fields that are null are coming out as -
2. phone numbers (account manager phone) are coming out as 305-5625889.
help would be greatly apreciate it!!!
-
Hello,
Coudl not source any data to replicate your scenario. SO tried with this sample
Function Main()
x = null
if isNull(x) then
Msgbox "Empty"
Else
Msgbox x
End If
Main = DTSTaskExecResult_Success
End Function
Hope this helps
Thanks and Regards
Ranjit
-
Function Main()
IF ISNULL(DTSSource("ACCT MGR AREA CODE").Value) _
OR _
ISNULL(DTSSource("ACCT.MANAGER PHONE").Value) _
THEN
DTSDestination("Account Manager Phone") = ""
ELSE
DTSDestination("Account Manager Phone") = _
FormatPhoneNumber(DTSSource("ACCT MGR AREA CODE").Value & _
DTSSource("ACCT.MANAGER PHONE").Value)
END IF
'...
Main = DTSTransformStat_OK
End Function
Function FormatPhoneNumber(strPhone)
' strPhone parameter is the area code and phone number concatenation
Dim strNew
strNew = "(" & Left(strPhone,3) & ") " & Mid(strPhone,4,3) & "-" & Mid(strPhone,7,4)
FormatPhoneNumber = strNew
End Function
-
thanks so much Michael!!!! , i put these code but i keep getting an error message that says Duplicate column name "Account Manager.Phone", any ideas why I am getting this error message?
-
Alter your code to use a variable to build the value for the phone number, then assign the variable as the value to the column. That should get rid of the error you are seeing.
-
thanks, but i am a little confused is it that what Michael did in his code?
Function FormatPhoneNumber(strPhone)
' strPhone parameter is the area code and phone number concatenation
Dim strNew
strNew = "(" & Left(strPhone,3) & ") " & Mid(strPhone,4,3) & "-" & Mid(strPhone,7,4)
FormatPhoneNumber = strNew
End Function
-
i got it to run ok with no errors but now my rows come out with all the fields as nulls, any ideas why?
-
Well, everybody has write a VBScript to you, while you are using ActiveX. Personally I like more ActiveX but in Microsoft world it's more difficult to make things...
All nulls? strange. A thing that you can try to do is to log inside the script. I do it quite often, using a lookup to a table for logs.
Josep.
-
thanks everyone i just rewrote the script a little and got it to work perfectly, thanks for all your help, i extremely apreciate it! have a great day
-
Here's a function I just wrote to format phone numbers:
CREATE FUNCTION dbo.FormatPhone(@inphone varchar(20))
RETURNS varchar(20)
AS
BEGIN
declare @outphone varchar(20)
declare @ext varchar(6)
declare @pos smallint
--remove any non-numeric characters
set @pos = patindex('%[^0-9]%',@inphone)
while @pos > 0
begin
set @inphone = stuff(@inphone,@pos,1,'')
set @pos = patindex('%[^0-9]%',@inphone)
end
--remove a leading 1 if exists
if charindex('1',@inphone)=1
set @inphone = stuff(@inphone,1,1,'')
set @ext = isnull((nullif('x' + substring(@inphone, 11, 5),'x')),'')
--lpad to 10 with 0's - when area code is not known then (000)
if datalength(@inphone)<10
set @inphone = replicate('0',10-datalength(@inphone))+@inphone
else
--strip off the extension
set @inphone = left(@inphone,10)
--make it pretty
set @outphone = stuff(stuff(stuff(@inphone,4,0,')'),8,0,'-'),1,0,'(')
return @outphone + ' ' + @ext
END
/*
select dbo.FormatPhone('7361844')
select dbo.FormatPhone('1(512)7361844')
select dbo.FormatPhone('1-512-736-1844-311')
select dbo.FormatPhone('5127361844x311')
select dbo.FormatPhone('15127361844 311')
select dbo.FormatPhone('1(512)736-1844 x311')
*/
[font="Courier New"]ZenDada[/font]
-
I wrote that function in t-sql for myself today, but you can do the same thing in your activeX Jess.
When you are on the transformations tab
remove your automapping
highlight areacode and phone fields in the source column
highlight concatenatedphonefield in the destination column
THEN select new activeX
You will see a Y shaped arrow connecting the two source fields to the one destination field. That's what you need to do to correct the error you had regarding duplicate column.
[font="Courier New"]ZenDada[/font]
-
BTW, a good rule of thumb is to store your data scrubbed and display your data formatted. In other words, don't format your phone numbers when you import them into your database unless you have a darn good reason.
The T-SQL function that I wrote is used for display in ASP, but I actually store my phone numbers completely scrubbed with only numeric characters in the them - no spaces, no parens, no dashes, no "ext", etc...
Have a great weekend!
[font="Courier New"]ZenDada[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply