Odd formatting in Excel from SSIS
-
My apologies if this has been covered before here - a search didn't find it.
I am trying to export, amongst other data, a decimal (10,2) to a pre-existing excel workbook.
Opening the workbook, the column is a number formatted as text (has the little green marker in the cell).
Although the format can apparently be changed within excel, any functions such as sum, max, avg etc do not work on this column.
I have tried altering the cell format in the excel spread sheet but nothing seems to work.
Has anyone come across this before?
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
Stuart Davies (3/16/2011)
I am trying to export, amongst other data, a decimal (10,2) to a pre-existing excel workbook.Opening the workbook, the column is a number formatted as text (has the little green marker in the cell).
Is it possible that SSIS exports the decimal with a dot and your regional settings expect a comma for decimal numbers?
-
Not as simple as that sadly, SSIS only puts out the decimal point in the data - no thousands marking.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
Stuart Davies (3/16/2011)
Not as simple as that sadly, SSIS only puts out the decimal point in the data - no thousands marking.Just to make sure we're on the same page I meant
1234.56 vs 1234,56
and not
1234.56 vs 1,234.56
If I copy data to Excel, for example from SSMS's result pane, I need to replace the dots with commas because my decimal separator is the comma.
When it's exported by SSIS it takes it into account my regional settings and exports properly.
Maybe I just don't have enough information to understand the issue you are describing
-
No - in either SSMS or SSIS data viewer I'm getting the results in the format I want - 1234.56
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
2 things.
1) Check the metadata that the excel file is expecting in design manager. My guess is it's sampling the rows and is determining it needs to drop the output out as text. Common annoyance with Excel files.
2) This is an older problem in excel with an easy fix, if you know about it. Type a 1 into a blank cell in the work book. Copy it. Highlight the target column, and choose paste special, then multiply. Hit okay, and it'll turn text to numbers.
The only way I can recommend getting away from the problem is to build a template file that has a sample row that forces the correct data types, and then change the target filepath during processing, once the metadata is locked in at runtime.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
If you can use third-party solutions, check the commercial CozyRoc Excel Destination Plus. It is completely independent from the old MS JET driver on which the standard SSIS component is based and you will have better ability to control the result data format.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Have given up on this on temporarily and am sending the user a csv - which works no problem.
I may revisit this at some point - given enough time.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply