vd
asked on
DTS - Vbscript transformation
One column in a DTS package (Excel to SQL Server 7) contains
zipcode. The column is defined as varchar 5 in the SQL Server
table. Any zero's in the first position of the zipcode get
truncated, so for example, 03341 would end up as 3341 in
the destination table.
What vbscript code could I use to transform this correctly?
zipcode. The column is defined as varchar 5 in the SQL Server
table. Any zero's in the first position of the zipcode get
truncated, so for example, 03341 would end up as 3341 in
the destination table.
What vbscript code could I use to transform this correctly?
Sorry, that should have read:
(difference in DTSSource line)
Function Main()
dim zipcode
ZipCode = DTSSource("zipcode")
ZipCode = string(5-len(ZipCode),"0") & ZipCode
DTSDestination("zipcode") = ZipCode
Main = DTSTransformStat_OK
End Function
(difference in DTSSource line)
Function Main()
dim zipcode
ZipCode = DTSSource("zipcode")
ZipCode = string(5-len(ZipCode),"0")
DTSDestination("zipcode") = ZipCode
Main = DTSTransformStat_OK
End Function
ASKER
This looks great, but I'm getting errors, apparently
on NULL's in the source zipcodes.
on NULL's in the source zipcodes.
as a guss, try
Function Main()
dim zipcode
ZipCode = DTSSource("zipcode")
If Not Null(ZipCode) then
ZipCode = string(5-len(ZipCode),"0") & ZipCode
Else
ZipCode = "" ' Or any "Default" value you prefer
End If
DTSDestination("zipcode") = ZipCode
Main = DTSTransformStat_OK
End Function
This will put any entries in your source db in with an empty string in your destination table.
Hope this helps.
Function Main()
dim zipcode
ZipCode = DTSSource("zipcode")
If Not Null(ZipCode) then
ZipCode = string(5-len(ZipCode),"0")
Else
ZipCode = "" ' Or any "Default" value you prefer
End If
DTSDestination("zipcode") = ZipCode
Main = DTSTransformStat_OK
End Function
This will put any entries in your source db in with an empty string in your destination table.
Hope this helps.
ASKER
Does the code above prefix every zipcode with
'O'? This wouldn't work if the zipcode does not have
zero in the first position.
'O'? This wouldn't work if the zipcode does not have
zero in the first position.
as an afterthought,
the lines
Else
ZipCode = Null
End if
should work if you are allowing nulls in your destination table, but as a rule I never allow nulls in my db's unless there is no logical concept of a default value. For example, a field called BirthDay could have a default value of Null because if I don't know the true value, then some other value is more wrong than null, but
a Field of Name I would prefer an empty string ("" in vb, or '' in tsql) to Null.
the lines
Else
ZipCode = Null
End if
should work if you are allowing nulls in your destination table, but as a rule I never allow nulls in my db's unless there is no logical concept of a default value. For example, a field called BirthDay could have a default value of Null because if I don't know the true value, then some other value is more wrong than null, but
a Field of Name I would prefer an empty string ("" in vb, or '' in tsql) to Null.
I am not familiar with US zip codes (assuming these are us?) but if all of the zip codes you are getting are supposed to be 5 digits long, this will put 0's in front of any other number, and leave you with zip codes all of 5 digits long. for examples the following would result.
Source Destination
1243 01234
12345 12345
11 00011
9 00009
10000 10000
If you have zip codes that 'could' be shorter than 5 digits, then you may need to change the column properties in the Excel spreadsheet first to say a text, then manually adjust those zip codes which need a zero put on the front of them.
Source Destination
1243 01234
12345 12345
11 00011
9 00009
10000 10000
If you have zip codes that 'could' be shorter than 5 digits, then you may need to change the column properties in the Excel spreadsheet first to say a text, then manually adjust those zip codes which need a zero put on the front of them.
ASKER
I'll try out your suggestions and back get to you.
No, actually the NULLS are on the input file, appear
to be blank records - I'll have to look at that.
No, actually the NULLS are on the input file, appear
to be blank records - I'll have to look at that.
ASKER
Doesn't work. An error "invalid use of NULL" occurs,even when I eliminate most everything but
the "ZipCode = string(5-len(ZipCode),"0") & ZipCode" statement.
Can I stop processing when it gets to the NULL
records toward the end of the spreadsheet,
like the traditional "end of file" processing?
the "ZipCode = string(5-len(ZipCode),"0")
Can I stop processing when it gets to the NULL
records toward the end of the spreadsheet,
like the traditional "end of file" processing?
try using a string function to convert your dtssource data
In oracle you can use to_char. I dont know the equivalent in vb.
In oracle you can use to_char. I dont know the equivalent in vb.
I think it is ctsr in vb.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Modify your line
ZipCode = DTSSource("zipcode")
into
ZipCode = DTSSource("zipcode").Value
Explanation:
VBScript is not really typed, and therefore has problems with the default properties
ZipCode = DTSSource("zipcode")
into
ZipCode = DTSSource("zipcode").Value
Explanation:
VBScript is not really typed, and therefore has problems with the default properties
ASKER
We were able to avoid the problem by redefining
the column in excel to TEXT. The approach
described here will be useful in the future!
the column in excel to TEXT. The approach
described here will be useful in the future!
Function Main()
dim zipcode
ZipCode = DTSDataSource("zipcode")
ZipCode = string(5-len(ZipCode),"0")
DTSDestination("zipcode") = ZipCode
Main = DTSTransformStat_OK
End Function