none
percentage in excel

    Question

  • Hi,

    I'm trying to import data from excel into sql db. I have an excel source and an UNPIVOT operator and finally the OLEDB Destination. The incoming data is in the following format pic(2) and in the pic (1) after using the UNPIVOT operator and loading the data into sql db. How can i see the same exact format as in excel in my table in the sql db?If you see in the excel for 10AM i have the valu as 200.00% but when i load it into the table i can only see the value 2. The data type for UTILIZATION is FLOAT. Please need help.

    Thanks

    Wednesday, January 23, 2013 6:57 PM

Answers

All replies

  • Hi there,

    Simple solution to this is export all data as string to a table columns.

    you use another table to load the data by applying your conversions. You can use a stored procedure to convert and load data.

    It is very easy to maintain. If you have any issues you can fix easily in the proc.

    If you do that conversion in SSIS, every time you have an issue you have go and modify the package and it is lot of work. you may break some thing else while doing that.

    keep it simple.

    thanks

    kumar

    Wednesday, January 23, 2013 11:03 PM
  • Thanks Kumar, i'm still a beginner with TSQL so i'm more comfortable with SSIS. My question is between the source file and the destination table i have a UNPIVOT operator , what operation should i use before the destination table to load the column as it is what data type should i use?
    Thursday, January 24, 2013 2:56 PM
  • Hi thinkingeye,

    Here are the lists of data types that support in SQL server using T-sql.

    http://msdn.microsoft.com/en-us/library/ms187752.aspx

    And here are the list of SSIS data types and mapping sql server data types.

    http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations

    If you use decimal with scale and precision you can get the 200.00. But not "200.00%"

    Thanks

    Kumar

    Thursday, January 24, 2013 9:52 PM
  • Thanks a lot Kumar will try using this in the DATA CONVERSION transformation. I need 200.00 and not the % sign. Thanks again.
    Friday, January 25, 2013 4:35 PM
  • Kumar still getting the values like before, In the data conversion transformation i used DT_DECIMAL  scale set to 6,  cannot do the precision there :-(
    Friday, January 25, 2013 4:50 PM
  • Hi thinkingeye,

    The Excel driver recognizes only a limited set of data types, I suggest you can use derived column transformation to save the column type as DT_WSTR data type, please refer to: http://stackoverflow.com/questions/4762649/change-whole-number-to-a-decimal-in-ssis

    Thanks,
    Eileen


    Eileen Zhao
    TechNet Community Support

    • Marked as answer by Eileen Zhao Friday, February 08, 2013 6:50 AM
    Wednesday, January 30, 2013 9:16 AM