Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simba_cubs
Starting Member
3 Posts |
 Posted - 2008-01-17 : 06:22:08
|
| I'm very new to SQL - please bear with me.1. I need to be able to export data from a database to an excel sheet (I have written the query and tested it works, so I don't need to know this stage :^)). What is the best way of doing this? Could you send me a link of a howto doc? 2. Once the data is export to excel, it then needs to be manipulated so that a final sheet is created. During the manipulation I need to add the values of certain columns to give me an end result.Should I use excel to manipulate the data or can sql add the values of certain columns and then export to an excel sheet?What's the best way please.Many thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-01-17 : 06:25:58
|
| Given that you are exporting the columns from sql server it would seem sensible to do do that processing in the export rather than afterwards - just include it in the export query.Easiest is to bcp to a csv file. If you name it .csv it will default to being opened in excel. You can create an excel workbook and populate it but that's a lot more complicated.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
simba_cubs
Starting Member
3 Posts |
Posted - 2008-01-17 : 06:50:56
|
| Does that mean the following is possible?Before the export Column1 Column2 Column3 Column4 Column5 Column6Value CodeNo 200 1 4 15 26I then need to perform the following equation(column2 + column3) minus (column4 + column5 + column6)and export the results into excel in the following format column1 column2value CodeNo Result of the above equationIf that possible or is that to complicated for a newbie like me?Thanks| |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-17 : 07:13:07
|
quote:
Originally posted by simba_cubs
Does that mean the following is possible?Before the export Column1 Column2 Column3 Column4 Column5 Column6Value CodeNo 200 1 4 15 26I then need to perform the following equation(column2 + column3) minus (column4 + column5 + column6)and export the results into excel in the following format column1 column2value CodeNo Result of the above equationIf that possible or is that to complicated for a newbie like me?Thanks|
You can use SSIS package to export data to excel from sql server after performing all these calaculations |
|
|
|
simba_cubs
Starting Member
3 Posts |
Posted - 2008-01-17 : 07:29:53
|
| Sorry I didn't explain I'm using SQL 2000 so would I use DTS? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-01-17 : 09:29:57
|
| try thismaster..xp_cmdshell 'bcp "select Column1,Column2, Column3, Column4, Column5, Column6, tot = (column2+column3)-(column4+column5+ column6) from mydb..mytbl" queryout "c:\myfile.csv" -SMyserver -T -c'If you want the file to be on the local m/c then just run the bcp in a bat file on the m/c.You can of course use dts but this is a lot simpler.Have a look at this for more complicated formatshttp://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Exporting Data from SQL