Link to home
Create AccountLog in
Avatar of WilliamAK
WilliamAK

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
Avatar of Mark Wills
Mark Wills
Flag of Australia image

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
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of WilliamAK
WilliamAK

ASKER

Thanks Mark!  That checkbox was the culprit.
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.