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
WilliamAKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
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.
Mark WillsTopic AdvisorCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WilliamAKAuthor Commented:
Thanks Mark!  That checkbox was the culprit.
Mark WillsTopic AdvisorCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.