Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data type conversion error Money-> text in excel destination


Data type conversion error Money-> text in excel destination

Author
Message
MrReddy
MrReddy
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
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
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
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
MrReddy
MrReddy
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
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.
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
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
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
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
MrReddy
MrReddy
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 553
Thank you for your reply Mordred.
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
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
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
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
MrReddy
MrReddy
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
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??
Mordred
Mordred
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search