locked
How to export data from sql server to excel file through queries? RRS feed

  • Question

  • I want to export the records in sql server file through sql server queries

    How can i do it?


    Thanks and Regards, Pranil Yambal Pranil.Yambal@hotmaail.com
    Monday, June 28, 2010 4:48 AM

Answers

  • Hi.

    Here is a c# code:

          SqlConnection con;
          SqlCommand cmd;
          SqlDataAdapter da;
          DataSet ds;
          
          con = new SqlConnection("YourConnectionString");
          cmd = new SqlCommand("SELECT * FROM Your_Table", con); 
          con.Open();
    
          da = new SqlDataAdapter(cmd);
          ds = new DataSet();
    
          da.Fill(ds);
    
          ds.WriteXml(@"c:/File_Name.xls");


    Noam B.
    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you...
    • Proposed as answer by Noam B Monday, June 28, 2010 11:13 AM
    • Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
    Monday, June 28, 2010 11:13 AM
  • Hi,

    You can try to use OPENROWSET T-SQL statement Export the output of SELECT statement to Excel. Try the following.

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=C:\YourExcelFile.xls;', 
    'SELECT * FROM [SheetName$]') 
    SELECT * FROM yourTable

    Refer the following links.

    SqlServer to Excel export with OPENROWSET

    Export data from SQL Server to Excel

    • Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
    Monday, June 28, 2010 5:26 AM
  • I want to export the records in sql server file through sql server queries

    How can i do it?


    Thanks and Regards, Pranil Yambal Pranil.Yambal@hotmaail.com


    Y don't u try simple select and insert command

    Select for SQL and Insert Command for Excel


    Regards Kumar Gaurav. Please Mark as an answer in case the post is helpful to you so that it became helpful to others
    • Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
    Monday, June 28, 2010 5:45 AM
  • Hi Pranil,

    Do you want to export the records from SQL Server using T-SQL and SELECT statements? If so, you can try to use the method posted in my previous reply. Also you can try post this question in SQL Server Forums .

    Also please let me know if you want to export the content from SQL Server into Excel using C#.

     

     

    • Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
    Monday, June 28, 2010 12:26 PM
  • Everyone here has given great answeres, I just wanted to add that if this is a live DB or serving many users while you export, be sure to add WITH NOLOCK in your select *.
    • Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
    Monday, June 28, 2010 5:49 PM

All replies

  • Hi,

    You can try to use OPENROWSET T-SQL statement Export the output of SELECT statement to Excel. Try the following.

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=C:\YourExcelFile.xls;', 
    'SELECT * FROM [SheetName$]') 
    SELECT * FROM yourTable

    Refer the following links.

    SqlServer to Excel export with OPENROWSET

    Export data from SQL Server to Excel

    • Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
    Monday, June 28, 2010 5:26 AM
  • I want to export the records in sql server file through sql server queries

    How can i do it?


    Thanks and Regards, Pranil Yambal Pranil.Yambal@hotmaail.com


    Y don't u try simple select and insert command

    Select for SQL and Insert Command for Excel


    Regards Kumar Gaurav. Please Mark as an answer in case the post is helpful to you so that it became helpful to others
    • Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
    Monday, June 28, 2010 5:45 AM
  • Hi.

    Here is a c# code:

          SqlConnection con;
          SqlCommand cmd;
          SqlDataAdapter da;
          DataSet ds;
          
          con = new SqlConnection("YourConnectionString");
          cmd = new SqlCommand("SELECT * FROM Your_Table", con); 
          con.Open();
    
          da = new SqlDataAdapter(cmd);
          ds = new DataSet();
    
          da.Fill(ds);
    
          ds.WriteXml(@"c:/File_Name.xls");


    Noam B.
    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you...
    • Proposed as answer by Noam B Monday, June 28, 2010 11:13 AM
    • Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
    Monday, June 28, 2010 11:13 AM
  • Hi Pranil,

    Do you want to export the records from SQL Server using T-SQL and SELECT statements? If so, you can try to use the method posted in my previous reply. Also you can try post this question in SQL Server Forums .

    Also please let me know if you want to export the content from SQL Server into Excel using C#.

     

     

    • Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
    Monday, June 28, 2010 12:26 PM
  • Everyone here has given great answeres, I just wanted to add that if this is a live DB or serving many users while you export, be sure to add WITH NOLOCK in your select *.
    • Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
    Monday, June 28, 2010 5:49 PM
  • this solution is working correctly but for more than 10 lakhs records it will take more time
    Thanks and Regards, Pranil Yambal Pranil.Yambal@hotmaail.com
    Friday, July 23, 2010 9:37 AM