Post reply

Flat File destination Ignoring decimals

  • November 21, 2011 at 3:20 pm

    #260456

    am having a package which loads the data into flatfile and having column with currency datatype like 14.56,30.00,16.66,29.00

    when am loading data that into flatfile its coming like 14.56,30,16.66,29

    the last 2 decimals are missing when it is having no value after decimal..

    How can i achive the zeros after decimal like 29.00,13.00???

  • November 21, 2011 at 4:06 pm

    #1411099

    what kind of flat file?

    And how is your destination data type defined (text, numeric, other)?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 21, 2011 at 4:09 pm

    #1411100

    Its a ragged right file and datatype is currency.

    when the value is 13.66 its is taking as it is...

    but when it is 0.00 in source it is taking as 0

    i think i need some derived column expression

  • November 21, 2011 at 4:12 pm

    #1411102

    Convert to text/string/varchar before dumping to that file and it will retain the .00

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 21, 2011 at 4:15 pm

    #1411105

    Ya i have tried that But it is not working..is there any other way to do this??

  • November 21, 2011 at 4:18 pm

    #1411107

    Are you opening this file up in excel by chance?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 21, 2011 at 4:26 pm

    #1411109

    No am opening as text. Can u gve me sum derived column exp

  • November 21, 2011 at 6:41 pm

    #1411139

    Please can any one help me in this??

  • November 22, 2011 at 12:29 am

    #1411225

    If you convert the data to VARCHAR in your select query, how is the formatting?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • November 22, 2011 at 6:47 am

    #1411440

    Thank you.

    It is in money datatype..i will try to convert into varchar..and let u know...

    Thank you

  • November 22, 2011 at 7:40 am

    #1411506

    That is also not working is coming as 10,56,65.64 etc

  • November 22, 2011 at 7:55 am

    #1411535

    i have converted into varchar but it is same as before

  • November 22, 2011 at 8:01 am

    #1411549

    removing insignificant digits in an export is normal (3.00 is exactly the same as 3), since the assumption is, if you import "3" into a money datatype, it will be 3.0000 in the data.

    try using CONVERT(varchar,yourmoneyfield,0) to force two digits, no commas as the exported style.(note the "0"? that's the optional style to use )

    see cast and convert in BOL:

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

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 22, 2011 at 8:06 am

    #1411559

    I have tried that one..result is same like before

    Thank you

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply