Excel to SQL 2012 db

  • 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 Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

  • Why don't you just put your working SQL in the connection submenu of a Lookup transformation?

  • 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

  • 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.




    Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

  • 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