When I run a query that exports data from SQL server to Excel, all columns
in excel are formated and treated as text. I tried casting in the sql
statement and they still come out as text. Any ideas how to solve this
without having to do a "Text to Column" in Excel to each column?

thanks,

How are you exporting the data?  If you use MS-Query or DTS, you shouldn't
see this problem.

Oliver

I am using a query in SQL server 2000

news:3e4a31eb@newsgroups.borland.com...

Well that tells me a lot.  Do you mean a TQuery in Delphi, MSQuery, Query
Analyzer, Enterprise Manager, or some other tool?  Which tool are you
using?

Oliver

i tried it in Query analyzer and in a stored procedure.

thanks,

news:3e4ac382@newsgroups.borland.com...

news:3e4adee4$2@newsgroups.borland.com...

As I see it you have several options:

1) Use the Excel Data Refresh option (Data|Import External Data|New Database
Query) to create a query in Microsoft Query and populate the spreadsheet.

2) Use some export components to export directly to a spreadsheet, eg see
the SMExport suite at http://www.scalabium.com/.

3) Use OLE Automation (see http://www.djpate.freeserve.co.uk/Automation).

4) But if you are going to use Query Analyzer, then you might find that
setting the current connection optons "Use Regional settings when outputting
currency, etc." and "right align numerics" will help excel decide how the
columns are formatted.  Then use the Data|Text to Columns routine to choose
the format of each column if necessary.

Oliver