Odd formatting in Excel from SSIS

Question

Post reply

Odd formatting in Excel from SSIS

Stuart Davies

SSCoach

Points: 19271
More actions
March 16, 2011 at 9:52 am

#246926

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

Viewing 8 posts - 1 through 7 (of 7 total)

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

Maxim Picard SSCrazy Points: 2052 More actions · Answer 1

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?

Stuart Davies SSCoach Points: 19271 More actions · Answer 2

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

Maxim Picard SSCrazy Points: 2052 More actions · Answer 3

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

Stuart Davies SSCoach Points: 19271 More actions · Answer 4

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

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 Farrell

Never 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

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

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/

Stuart Davies SSCoach Points: 19271 More actions · Answer 7

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