March 30, 2012 at 3:45 pm
Data Flow Task
-Excel Source
-Data Conversion
-Derived Column Transformation
-Flat File Destination
My flat file is in Ragged Right format. My columns have been defined and I use the derived column transformation to pad out one of the columns (String type) with zero's to fulfill a total of 18 characters.
Value before package execution: 1234567892
Value after execution: 000000001234567892
How do I split 14 characters from left to right and the remaining 6? Then concatenate after.
Reason for this is that the example above only padded from left to right b/c the original number was (1234567892). So in a case where I have this value with a decimal 12345.555000 I ultimately want it to look like this 000000012345555000.
April 2, 2012 at 7:10 am
Try the following expression:
RIGHT((DT_WSTR,18) "000000000000000000" + REPLACE((DT_WSTR,18) myInputColumn),".",""),18)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 5, 2012 at 4:14 pm
Koen,
I tried the expression RIGHT((DT_WSTR,18)"000000000000000000" + REPLACE(((DT_WSTR,18)[Copy of SHARES]),".",""),18)
Before I run the package my values are:
(Note that these values are coming from excel sheet)
Ex.
555555.666666
888888.000000
After execution:
000555555666666001
000000000000888888
I was hoping to receive:
000000555555123456
000000888888000000
Why do you think I'm getting a 001 appended at the end of the decimal position and secondly why are the zero's being dropped from this value 888888.000000. I prefer to keep the zero's.
Thanks for all your help.
April 9, 2012 at 11:14 pm
The derived column won't magically add "001" to a number.
My guess is they are already there in the Excel file, but formatting doesn't show it to you.
About the missing 0's, my guess is 888888 is being read and not 888888.00000.
Place a data viewer right after your Excel source and inspect the data coming in.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply