SQL Server - Exporting sql query result as excel file in a specified location

Asked By S on 27-Jan-11 06:06 AM

Hello,

I have a select query to retrieve the data. I want to export the result as excel file in a specified location thru query (Not Manually saving into excel file ).Any help would be highly appreciated.

Regards,
S.Guru

Reena Jain replied to S on 27-Jan-11 06:17 AM

hi,

Export data to existing EXCEL file from SQL Server table

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=D:\testing.xls;',

'SELECT * FROM [SheetName$]') select * from SQLServerTable

will export from a local SQLServer to an excel file on the same machine

hope this will help you

Rohan Dave replied to S on 27-Jan-11 06:21 AM

you can do it directly. Follow the below steps :

1)You just need to run your SQL Query.
2) On the Result Tab , select whole result area and right click on it.
3) You will see Save Result As option. Click on it and it will show you Save As dialog box.
4) Enter the File name and click on Save

It will save the file as CSV format. So you can convert it into Excel and also able to open it up in Excel..

Anoop S replied to S on 27-Jan-11 06:29 AM

you can easily, with just one method call, export http://www.gemboxsoftware.com/LA/Import-Export-DataTable-XLS-XLSX-CSV-HTML-.NET.htm with this http://www.gemboxsoftware.com/GBSpreadsheet.htm library.

Here is a sample http://www.gemboxsoftware.com/GBSpreadsheet.htm code how to export http://www.gemboxsoftware.com/LA/Import-Export-DataSet-XLS-XLSX-CSV-HTML-.NET.htm:

// Create new ExcelFile.

var ef = new ExcelFile();

// Imports all the tables from DataSet to new file.

foreach (DataTable dataTable in dataSet.Tables)

{

// Add new worksheet to the file.

var ws = ef.Worksheets.Add(dataTable.TableName);

// Insert the data from DataTable to the worksheet starting at cell "A1".

ws.InsertDataTable(dataTable, "A1", true);

}

// Save the file to XLS format.

ef.SaveXls("DataSet.xls");