I have a excel file with three columns that are all empty (top to bottom) that need to be imported as float or money to a sql server table. Other excel files for those columns contain values with two decimal places. Since I can't import blank cells to a numeric column I used LEN(TRIM(F11)) > 1 ? (DT_R8)F11 : 0 within a derived column component to write a 0 inside the db columns.
But now when I import other Excel files with values I get values like 8,4971E+20 instead of 849,71.
Can I change the expression to take care of this or is there something else involved?
Also, instead of writing a '0' I'd like to insert NULL to the db float/money column.
Hope you can help me out with this.
UPDATE: let me try it another way:
I have an if expression in those empty excel columns which will just put nothing inside the cell if the value of another sheet is not a number.
If I could use the "else" statement to insert a numeric value to each cell or maybe NULL I could import directly as float. I tried putting '0' or 'NULL' in the else statement but I guess because of the quotation marks around them, they will be interpreted as string.
Any ideas on that?
Sounds like you're the same TonyC that posted a similar question on MSDN.