DTS-Excel result-Two decimal places

  • Hi, I created the following table and defined this table in DTS-Transform-Data-Task-Properties to import file into Excel file.

    A_Cost, B_Cost and C.Cost but when I get result into Excel then A_Cost, B_Cost and C.Cost shows General type value i.e.

    23.40, 23, 145.2 where as I need results like 23.40, 23.00, 145.20. Results with two decimal places.

    Please help why I am not getting result in excel with two decimal places. Please help. Thanks.

    CREATE TABLE `Payroll_Audit1` (

    `Empid` VarChar (10) ,

    `Startdate` DateTime ,

    `A_Cost` DECIMAL(6,2) ,

    `B_Cost` DECIMAL(6,2) ,

    `C_Cost` DECIMAL(6,2) )

    --Getting Results

    23.40, 23, 145.2

    --Desired Results

    23.40, 23.00, 145.20 --with two decila places.

  • Try formatting the Excel cells.

    Clearly, the values are correct, and you are requiring a different format.

    This would be a formatting issue for whatever application is displaying the results.

    Cell Properties -> format as number with 2 dec places.

  • Hi,

    But when I run the DTS then again format become change to General. Thats why I wanted to know how can I format through DTS. I also changed the type in Database but still getting result in Excel in general format.

  • Sorry, can't help.

    It seems to work on my PC.

    I'd suggest an Excel problem. DTS should not be reformatting an excel sheet ... I don't know if it can!!

    Unless, of course, you are asking DTS to create an excel sheet, in which case it will always default to a general format.

  • This is an Excelproblem.

    I dont use DTS. To control the behaviour of Excel the best way is to use VBA in Excel

    and import a recordset from the database.

    /Gosta

  • In the source window where you are mentioning the query to populate xls, press the preview button and check what is the format of the decimals is getting displayed there.

    If preview is displaying the format u wish , then the problem is in xls and if not then you have to fix the styling in SQL query.

    Cheers,

    Avi

Viewing 6 posts - 1 through 5 (of 5 total)

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