Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Data type conversion error Money-> text in excel destination Expand / Collapse
Author
Message
Posted Wednesday, August 15, 2012 3:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 4, 2014 4:50 PM
Points: 57, Visits: 553
Hello All,
My SQL data type is money, when I load the data into excel it takes the value as text but I want that data to be decimal or money to use for sum calculations in Pivots.
suggestions please,.,.,


Thanks,
Ram
Post #1345564
Posted Wednesday, August 15, 2012 3:52 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
Points: 96, Visits: 495
Have you used the Data Conversion in your Data Flow Task? Perhaps this link will be of some help for you.

Regards:
Mordred
Keep on Coding in the Free World
Post #1345592
Posted Wednesday, August 15, 2012 3:56 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 4, 2014 4:50 PM
Points: 57, Visits: 553
I did all the data conversions but the target excel file is taking all the values as text only, even if I change the data types in excel show advanced editor, it is not storing the values.
Post #1345593
Posted Wednesday, August 15, 2012 4:29 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
Points: 96, Visits: 495
K, I'm currently trying to recreate the scenario but am having un-related issues at run time with my laptop. I'll get back to you in a bit or someone else will.

Regards:
Mordred
Keep on Coding in the Free World
Post #1345600
Posted Wednesday, August 15, 2012 4:45 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
Points: 96, Visits: 495
Alright I see what you mean.

Questions:

Is the Excel destination pre-existing or are you generating a new Excel file each time this particular data flow task is run?

If the file is pre-existing then you should take the column in question that is to hold $$ data and format those cells appropriately.

If the file is being generated at dft time then you may want to consider connecting to your personal Excel book. That particular book should be in a folder similar to the following path:
C:\Documents and Settings\Your Name\Application Data\Microsoft\Excel\XLSTART
Open that book, alter the column of cells where you know the $$ format should be, save it and close it. From there, every time an Excel workbook is opened it will open in that format. You can customize this Workbook however you see fit.

I don't know if this is the best solution but it's all I have off the top of my head.


Regards:
Mordred
Keep on Coding in the Free World
Post #1345604
Posted Wednesday, August 15, 2012 4:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 4, 2014 4:50 PM
Points: 57, Visits: 553
Thank you for your reply Mordred.
Post #1345605
Posted Wednesday, August 15, 2012 4:50 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
Points: 96, Visits: 495
I just did as I suggested with my own starting Excel file and it didn't seem to have the proper effects. I'm going to keep trying though.

Regards:
Mordred
Keep on Coding in the Free World
Post #1345606
Posted Wednesday, August 15, 2012 6:50 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
Points: 96, Visits: 495
Alright so I formatted the columns that I am affecting with the data flow task and now it loads properly. Here's what I have done:
1. Created a simple table called tblMoneys
1.1 Created two fields in the table called ID & Money
2. Created a personally formatted Excel file with headers ID & Money
2.1 Formatted ID column as a number with no decimal spots, data type: double precision float [DT_R8]
2.2 Formatted Money column as currency with 2 decimal spots, data type: currency [DT_CY]
2.3 Saved and closed the workbook

SSIS:
1. Source - OLB DB
2. Added a Data Conversion (although I don't think it was necessary but still used it non-the-less.
3. Destination: Excel Connection Manager
4. Ran the package

No issues with the loading to Excel, all formats were good.


Regards:
Mordred
Keep on Coding in the Free World
Post #1345624
Posted Thursday, August 16, 2012 9:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 4, 2014 4:50 PM
Points: 57, Visits: 553
2.1 Formatted ID column as a number with no decimal spots, data type: double precision float [DT_R8]
2.2 Formatted Money column as currency with 2 decimal spots, data type: currency [DT_CY]

Hello Mordered, thank you for your reply.
How did you set that currenct[DT-CY] data type??
Post #1346048
Posted Thursday, August 16, 2012 9:25 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
Points: 96, Visits: 495
For the layout I created in the Excel file, the currencies were placed in column B. I highlighted column B, right clicked, and then selected "Format Cells". I then chose Currency. When I made the destination connection to the Excel file, I checked the mappings and the format was as I stated.

Which version of Excel are you using? The test I performed was with Excel 2010.


Regards:
Mordred
Keep on Coding in the Free World
Post #1346056
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse