asked on

Sql Server 2008 R2 SSIS export to excel 2007 numbers formatted as text

Hello,

I'm using the sql server data export wizard to create excel files. When I use an Excel 97-2003 destination it works fine. But when I use an Excel 2007 destination then all numbers are formatted as text. (With the little green triangle in the upper left corner.)

Dates and text are formatted properly.

In the Edit Mappings screen, i've tied using decimal, long, currency, varchar, single, and double but all end up as text.

Any help would be greatly appreciated.

Thanks,
William

Mark Wills

It is a real pain and have yet to find a real solution...

What happens is although it looks like a number, excel doesn't think so, and pretty much have to do it yourself.

If you run TRIM it converts to number (go figure). If you edit the cell to try to find the space, it doesn't, but the cell magically become a number if you press enter.

There was a macro from eons ago (wish I could find the link) :

Sub macro() 
    Range("F:F").Select 'specify the range which suits your purpose
    With Selection 
        Selection.NumberFormat = "General" 
        .Value = .Value 
    End With 
End Sub

Open in new window

But haven't used it for a while.... I was fortunate enough to revert to the earlier (97) version for export and no one has said anything yet. I have also written T-SQL code to man-handle formats.

two reasons for posting this comment...
1) you are not alone, nor going nuts. It is a "feature"
2) maybe someone has a definitive answer as to an easy fix.

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

WilliamAK

ASKER

Thanks Mark! That checkbox was the culprit.

Mark Wills

Very happy to hear that :)

I am still struggling getting my SQL Money columns appearing as Excel Currency without resorting to code... I am hopeful the next Office drivers will behave properly.