July 31, 2002 at 4:42 am
I know there is a simple answer to this, but I can't find it.
I'm running SQL Server 2000 SP2 and Excel 2000
I need to export the entire contents of a table, together with a customised field header row, to an Excel spreadsheet ready for import into another companies system.
The data is prepared into a table ready for export.
A DTS package has a connection to an Excel 97-2000 destination. It uses this to issue a 'Drop Table' command to remove the original spreadsheet (my ultimate aim is to schedule this).
The next step issues a 'Create Table' to the Excel connection to create the field headers.
The third stage is a DTS data pump which queries the SQL table to extract the data, transform it, and pump it to the Excel connection. The row delimiter is left at {CR}{LF}, Column delimiter as a vertical bar, and text qualifier as <none>.
All goes well with one exception, EVERY text field in the excel spreadsheet has a 'General' format and a single quote at the start of the values.
Currently, I'm having to run a macro, manually, against the spreadsheet every time it is created to reset the format of the columns to 'Text' and quite literally execute Cells(r,c).value = Cells(r,c).value on every cell to remove the single quote.
Doing this means that I cannot schedule the task.
Does anyone know of any way to export to the excel spreadsheet, retain the customized field headers (these need to have spaces for some reason, and so do not match the SQL table field names as I refuse to have spaces in these), and ensure that text goes across 'without' the single quote?
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
August 6, 2002 at 12:25 pm
I've tackled this two different ways, but haven't found the exact answer you want. Here's what we've done.
To write to an Excel file, the best tool available IMHO is Excel Writer from http://www.softartisans.com/. It's a great utility to turn recordsets into Excel spreadsheets. It'll allow you to do any formatting you need, it's designed to live on the server, and it's FAST. Probably overkill for what you're trying to do though.
That said, there's also an extremely low tech answer. If all you need is to get your data into a spreadsheet with the field names in the first column, try this.
declare @sql nvarchar(4000)
set @sql = 'bcp '
set @sql = ' " select field1, field2 from OutPutTable '
set @sql = @sql + ' union '
set @sql = @sql + 'select ''field name 1'', ''field name 2'' " '
set @sql = @sql + ' queryout "\\myserver\myshare\myfilename.xls" -T -c'
exec master..xp_cmdshell @sql
The query will return your field names in the first row, and the file created by the bcp command will be in tab delimited format. With the .xls extension, if a user double clicks on it, it will open in Excel, and Excel will load each field into it's own column. One caveat. You must cast or convert numeric and date data to varchar or the union operation will fail.
Like I said, it's not exactly what you're looking for, but both of these methods work.
Good luck!
John
August 7, 2002 at 1:25 am
Thanks, I'll give them a try
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply