asked on 7/17/2008

DTS converts numbers to text during export to Excel

I have a DTS job that exports a table to a formatted Excel spreadsheet. Even though the spreadsheet is pre-formatted as numbers, DTS converts them to text. Is there a way around this? I found a registry hack for TypeGuessRows but it did not seem to make a difference.
...timmy

Microsoft SQL Server

Last Comment

CiciO

7/31/2008

frankytee

7/18/2008

is the relevant field numeric in your table? if not then use a select statement in your dts transformation to excel

select cast(yourfield as decimal(19,2)) as yourfield,
cast(yourfield2 as int) as yourfield2,
etc....
from yourtable

CiciO

7/19/2008

ASKER

Yes - the SQL data is numeric. The problem as I undertsand it is that the JET engine used by DTS sees the column header in Excel and assumes that it should post text data in the column.
...timmy

nmcdermaid

7/21/2008

Two solutions that I can think of:
1. Export as CSV, which opens in Excel and doesn't have any formatting
2. Write some ActiveX script to go through the Excel file afterwards and fixes it up.

CiciO

7/23/2008

ASKER

Thanks nmcdermaid,

We already have it in a CSV and we are trying to improve the presentation.
I hadn't thought about an ActiveX script but I am still holding out hope for a solution in DTS.

...timmy

ASKER CERTIFIED SOLUTION

nmcdermaid

7/27/2008

THIS SOLUTION IS 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

CiciO

7/31/2008

ASKER

I will close now. Thanks for your help.