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

WilliamAK
WilliamAK used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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.
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
If you want the numbers to be used as numbers (even though they remain as a "general" format,
you can go into options and turn off numbers stored as text...

From : MS Office Link

1.Click the File tab.
2.Under Help, click Options.
3.In the Excel Options dialog box, click the Formulas category.
4.Under Error checking rules, clear the Numbers formatted as text or preceded by an apostrophe check box.
5.Click OK.

Author

Commented:
Thanks Mark!  That checkbox was the culprit.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial