question

apple avatar image
apple asked

Excel formatting

Hi! In the template Excel sheet I formatted the 2 nd row to display the dollar sign and made it into a hidden row. But the output file does not show the dollar sign. Any suggestions to rectify this. I tried to create an empty table as suggested on Excel connection but that scenario did not work, Another route that I tried was through the script task wb.Worksheets("Test").Columns("D:D").NumberFormat = "$#,##0.00" This is not displaying the dollar sign -- Any suggestions on what I am missing? Thanks
ssisexcel
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

erlokeshsharma08 avatar image erlokeshsharma08 commented ·
Can you please elaborate more about the scenario and the source data
0 Likes 0 ·
apple avatar image apple commented ·
The source data is from a stored procedure and the fields that I want the $ sign are money. The destination is Excel file. Thanks
0 Likes 0 ·

1 Answer

·
erlokeshsharma08 avatar image
erlokeshsharma08 answered
I am sure it cant be as easy as I am thinking. Assumption - source column is money and has values say 1.00, 3.00, 800.232 and we want output in excel sheet as $1.00, $3.00, $800.23 Solution - Step1. create oledb source that would get data from sp Step2. add derived column, add a new column (say it is new_col) and with following expression:- "$" + (DT_STR,10,1252)((DT_DECIMAL,2)i) Step3. Bind the new_column to the corresponding column in excel.
4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

apple avatar image apple commented ·
Thank you but the issue when you convert into a string we lose the option of doing a sum in Excel sheet and when I convert into a currency in the next data conversion the sign disappears
0 Likes 0 ·
erlokeshsharma08 avatar image erlokeshsharma08 commented ·
If you have control over the excel template, select the entire column and right click on it. Format the destination column to $ English (U.S.). It works, just tried it.
0 Likes 0 ·
apple avatar image apple commented ·
Thank You Sorry for the delay. $English works when I directly input the data but when I am reading from the table through the package it does not. I am using Excel 2007 and the data types are Currency . Thanks
0 Likes 0 ·
erlokeshsharma08 avatar image erlokeshsharma08 commented ·
Oh is it...I tried it on my office machine and was working...think we were using excel 2010....but let me confirm it again. . I'll keep you posted
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.