locked
importing data to excel from sql RRS feed

  • Question

  • greetings, I need to import data into my excel spreadsheet with a query to the database, it is the execution of a procedure but it has the disadvantage that the procedure returns a temporary table and apparently does not recognize this excel
    somethin like
    select * from # temp
    IS THIS TRUE I NEED TO DO SOMETHING ELSE
    Tuesday, May 17, 2016 8:17 PM

Answers

  • I could add data returned by a SQL Server stored procedure by following steps

    Data > Get Data From Other Sources > From Microsoft Query

    Create a new Data Source using wizard or use existing one

    I created a new data source on databases tab using ODBC Driver for SQL Server.

    You have to define the server and default database, etc during configuration for the connection.

    Then using table and views, you have the option to use SQL query using Microsoft Query

    Microsoft Query

    After I import data from SQL Server, on Properties I could see following details.

    data for Excel from SQL Server


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

    Monday, May 23, 2016 11:16 AM

All replies

  • Hello,

    Here is a solution for importing data to excel from sql database provided by a free excel library:

                string strConn = "Data Source=;Initial Catalog=;User Id=;Password=;Integrated Security=true";
                SqlConnection connSql = new SqlConnection(strConn);
                connSql.Open();
                string sqlQuery = "SELECT * FROM Tb";
                SqlCommand cmdSql = new SqlCommand(sqlQuery, connSql);
                SqlDataAdapter adapterSql = new SqlDataAdapter(cmdSql);
                DataSet dataSet = new DataSet();
                adapterSql.Fill(dataSet, "Table");
                DataTable datatable = dataSet.Tables["Table"];
                connSql.Dispose(); 
                Workbook book = new Workbook();
                Worksheet sheet = book.Worksheets[0];
                sheet.InsertDataTable(datatable, true, 1, 1);
                book.SaveToFile("FileName.xlsx"); 
    Hope it helps you.


    • Edited by LeonDav Wednesday, May 18, 2016 2:04 AM
    Wednesday, May 18, 2016 2:03 AM
  • Please see the link below.  You will find everything you need right there.

    http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Introduction


    MY BOOK

    Friday, May 20, 2016 11:30 AM
  • I dont need to use macros

    I have to execute the procedure from the excel directly

    regards

    Friday, May 20, 2016 2:59 PM
  • Hi Palermo,

    This forum is used to discuss about Excel developing issue, since you will not use excel developing, I will move this thread to the more related forum.

    Reference: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql   

    Thanks for your understanding.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, May 23, 2016 6:35 AM
  • I could add data returned by a SQL Server stored procedure by following steps

    Data > Get Data From Other Sources > From Microsoft Query

    Create a new Data Source using wizard or use existing one

    I created a new data source on databases tab using ODBC Driver for SQL Server.

    You have to define the server and default database, etc during configuration for the connection.

    Then using table and views, you have the option to use SQL query using Microsoft Query

    Microsoft Query

    After I import data from SQL Server, on Properties I could see following details.

    data for Excel from SQL Server


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

    Monday, May 23, 2016 11:16 AM