Board index » delphi » Excel export field format

Excel export field format

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,

 

Re:Excel export field format


Quote
> 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?

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

Oliver

Re:Excel export field format


I am using a query in SQL server 2000

Quote
"Oliver Townshend" <oli...@zip.com.au> wrote in message

news:3e4a31eb@newsgroups.borland.com...
Quote
> > 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?

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

> Oliver

Re:Excel export field format


Quote
GM <gfmi...@hotmail.com> wrote:
> I am using a query in SQL server 2000

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

Re:Excel export field format


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

thanks,

Quote
<oli...@zipworld.com.au> wrote in message

news:3e4ac382@newsgroups.borland.com...
Quote
> GM <gfmi...@hotmail.com> wrote:
> > I am using a query in SQL server 2000

> 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

Re:Excel export field format


Quote
"GM" <gfmi...@hotmail.com> wrote in message

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

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

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

Other Threads