June 8, 2006 at 10:03 am
Hi guys,
I am using BCP to export data to excel files. I am wondering if its possible to add different tables as sheets in a single excel file rather than creating a new excel file for each table?
Thanks
Shams
June 8, 2006 at 11:14 am
Not any remotely straightforward way using bcp. The files generated by bcp are flat files, the only markup being column and row terminators (standardly .csv (comma-delimited files) are viewable in excel, and commonly opened by default with excel, but they aren't themselves excel spreadsheets.
Sounds like the sort of thing DTS should be able to do, or you could write an app that does it using the excel object library. there might be an XML schema for excel sheets which could be used in some way...
If you just want all the results in one file, you can use xp_cmdshell to concatenate csv files into one long 'spreadsheet' (there may be a file terminator character, which would need to be removed from all but the last file, but I don't think the csv format uses one). File concatenation can be done a million ways, not necessarily in SQL server. If you went down this route, you would porbabky want to add an extra column to your results to differentiate results from idfferent queries.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 9, 2006 at 8:21 am
You could record a macro in excel to load up all of the files into worksheets and then have it save the completed spready. That way, you can run the macro from the command line (so it could run in a batch script) so it pulls in all the files and puts them together & saves the output. I do something similar with word macros, the syntax for that would be:
"C:\Program Files\Microsoft Office\Office\winword.exe" /m{marcro_name_goes_here} /q
I would imagine it's similar for excel & might be worth checking out.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply