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
Microsoft ExcelMicrosoft SQL Server 2008Microsoft SQL ServerSSIS

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon
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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy