Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2008-09-02 : 10:03:26
|
| Hi thereWhen importing data from an excel spreadsheet, i want to nullify any values that are either invalid or don't match the data type. Is this possible in one of the transformation tasks?Example: In the example below, the ? should be converted to null. However, i do no know that the value will always be '?', therefore, I want to nullify any failuresPrice Product---------------1.02 Bread? RollsHearty head pats |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-02 : 10:06:50
|
You can use an expression in a derived table.1) case when price = '?' then null else price end2) NULLIF(Price, '?')
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2008-09-02 : 10:14:04
|
| Hi PesoThanks for the reply.Problem is that I don't actually know what the invalid character(s) is going to look like. For instance, for all i know, the price could be ?,kl,nothing,etc.The ultimate aim is to import all the values from the excel spreadsheet, and rather than fail a record insert as a result of a data issue, insert a null value instead.Hearty head pats |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2008-09-02 : 10:19:01
|
| Actually, don't worry, this is no longer a problem.Hearty head pats |
|
|
|
|
|
|
|
|
Data conversion from excel import