October 9, 2014 at 2:03 pm
Hi folks
I posted similar thing previously and not much input.
So I try again.
I have this .xls file that I get with a column that has following values for example:
8.06 GB
10.1 GB
5.64 GB
48.6 MB
2.84 GB
3.49 GB
0 bytes
0 bytes
0 bytes
0 bytes
235 KB
330 MB
34.8 MB
0 bytes
56.0 KB
0 bytes
41.1 MB
101 KB
7.29 GB
0 bytes
273 KB
66.0 KB
3.80 MB
2.64 MB
8.34 GB
-27.1 GB
What I want to do is remove GB and put the value in the db
remove KB, Bytes and ignore them
Remove TB, multiply it by 1000 and insert the value in the db.
Not really how to proceed. In the SSIS Package I am using:
(DT_DECIMAL,2)REPLACE(["Aggregate Total Space"]," TB","")
When I try the same using pure sql
"SELECT (CAST(REPLACE([Aggr_Daily_Growth_Rate],'tb','') AS SMALLINT(8))*1000) AS 'xx'
FROM NetAppReports.dbo.Aggregate_Reports
WHERE Aggr_Daily_Growth_Rate NOT LIKE '%bytes' AND Aggr_Daily_Growth_Rate NOT LIKE '% KB'"
I have no issues.
Should I just first put all values the way they come into the db and then use t-sql and then run convert? or purify the data to begin with?
Any help is welcome
Paresh
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
October 9, 2014 at 3:26 pm
Why don't you just put your working SQL in the connection submenu of a Lookup transformation?
October 10, 2014 at 1:17 am
First I would remove the data I don't need using a conditional split.
Check for KB and bytes and sent those rows to an output you don't use.
Next I would use a derived column to get rid of the GB value with a REPLACE function.
Using a second derived column, I would check if TB is still present and if it is remove it and multiply by 1000.
Finally I would convert the column to a number data type.
ps: why are you multiplying TB and not GB with 1000?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 10, 2014 at 8:59 am
Thank you folks for the response.
I have done what Koen suggested.
But the values in the database are very different from the ones in the spreadsheet.
ugh.
Cheers
Paresh
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
October 13, 2014 at 1:18 pm
Without anymore info, the fact that your result is unexpected, I would guess you are performing mathematical functions on text. Things to think about:
Are you removing the space leading the abbreviation?
Are you replacing the " GB" with "" after you replace the " TB" with "000"?
Are you sure you only have numbers in your result set when you convert?
Put a data viewer before the conversion and make sure you don't have any text characters.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply