Exporting from SQL to Excel

[hooggie]

I am trying to export data to send to a third party in an Excel Spreadsheet. The third party is extremely fussy about how their data arrives it must be in Excel and numbers have to be numbers.

When I try to DTS data to Excel or script data to Excel using

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Test.xls;',
'SELECT * FROM [Sheet1$]')
select COL1, COL2 from SQLTABLE order by COL1

The number columns are always put into Excel as Text.

I have tried formatting the Excel sheet, specifying that the columns exported are numbers but I can't find anything that works.

Anyone got any ideas on this.

 

[micang]

Not sure if this i sefficient enough, but what I do in these cases is export to txt then import that txt file into Excel and then during import I chhose the relevant format for each column.

A bit long winded, but if it's once off it's not too bad!

Hope this helps.

Michael

 

[hooggie]

Unfortunately the goal of this is to have the file emailed to the third party every night automatically so there can't be any manual intervention. Thanks for the reply anyway

 

[FlaBusInt]

Do you use any reporting software like MS Reporting Services or Business Objects? Most reporting front-ends will allow you to schedule e-mailed reports in a variety of formats, such as Excel or PDF.