locked
EXPORT TO EXCEL RRS feed

  • Question

  •  Hy ,
       I would like to know how the sql server 2005 can export data to excel , because I have that implemeting something like that, but remember that my SGBD not have installed microsoft office  .

       Anybody could help me ?


    andre
    Tuesday, December 8, 2009 11:52 AM

Answers

  • Enable Ad Hoc Distributed Queries. Run following code in SQL Server Management Studio – Query Editor.
    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
    GO
    RECONFIGURE;
    GO

    Create Excel Spreadsheet in root directory c:\contact.xls (Make sure you name it contact.xls). Open spreadsheet, on the first tab of Sheet1, create two columns with FirstName, LastName. Alternatively you can download sample Spreadsheet from here.

    Run following code in SQL Server Management Studio – Query Editor.
    USE [AdventureWorks];
    GO
    INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
    'SELECT * FROM [Sheet1$]')
    SELECT TOP 5 FirstName, LastName
    FROM Person.Contact
    GO

    Open contact.xls spreadsheet you will see first five records of the Person.Contact inserted into the first two columns.

    Make sure your spreadsheet is closed during this operation. If it is open it may thrown an error. You can change your spreadsheet name as well name of the Sheet1 to your desired name.

    In fact, I have another solution for you. You can have a try of RAQ Report, because you can export data to Excel easily with it. The only thing you need to do is clicking the function in the menu.

    • Marked as answer by Zongqing Li Monday, January 4, 2010 3:35 AM
    Sunday, December 20, 2009 4:06 AM

All replies

  • Did you check this link http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1306800,00.html which talks about creating a package to move data from SQL Server to Excel
    What is SGBD?
    Nitesh Rai- Please mark the post as answered if it answers your question
    Tuesday, December 8, 2009 11:57 AM
  • Thank you , so my cenary is not very easy . 
     
      I have a task ( For each Loop ) that must export to excel that read Source that is column table storage a query but daily can be many rows with differents querys. I would like vb.Scrpt  to solve my problem because i cannot mapping to excel because must  be dynamic the mapping .

      Example


      id  -                       ds_Query                                    -  dt_Tran

       1               Select id  From Company                                20091210
       2               Select id ,name   From Company                      20091210
       3               Select id ,name , age From  Company               20091210

    andre
    Tuesday, December 8, 2009 12:20 PM
  • Hi,

    From your description, the SQL query is dynamic, so you want to use Script Task to export the data from SQL Server table into EXCEL, correct? If I have understood right, please try to use the sample code from the following article in Script Task.

    http://www.vbdotnetheaven.com/UploadFile/lcamlibel/DataTransferfromSQL04082005084827AM/DataTransferfromSQL.aspx




    Please feel free to let me know if I’ve misunderstood anything.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, December 10, 2009 7:14 AM
  •  Sorry , I don't have instaled Office in my SGBD Server , so i can't call ( Excel 9.0 Objects Library ) !



    Dim excel As New Excel.Application()

    excel.Application.Workbooks.Add(True
    )



    Private Function GetData() As System.Data.DataTable
    Dim conn As New SqlConnection("server=(local)\vste;uid=sa;pwd=;database=northwind;")
    Dim adapter As New SqlDataAdapter("select * from Customers", conn) '
    Dim myDataSet As New DataSet
    Try
    adapter.Fill(myDataSet, "Customer")
    Catch ex As Exception
    MessageBox.Show(ex.ToString())
    End Try
    Return myDataSet.Tables(0)
    End Function 'GetData



    Dim col As DataColumn
    For Each col In table.Columns
    colIndex += 1
    excel.Cells(1, colIndex) = col.ColumnName
    Next col
    Dim row As DataRow
    For Each row In table.Rows
    rowIndex += 1
    colIndex = 0
    Dim col As DataColumn
    For Each col In table.Columns
    colIndex += 1
    excel.Cells(rowIndex, colIndex) = row(col.ColumnName).ToString()
    Next col
    Next row





     Thanks ,
       Andre
    andre
    Thursday, December 10, 2009 8:19 AM
  • Hi,

    In order to use EXCEL at your server, we have to install Jet driver at first.

    Please refer to the following links to get Jet driver.

    http://support.microsoft.com/kb/239114

    http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en




    Please feel free to let me know if I’ve misunderstood anything.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, December 17, 2009 3:31 AM
  • Ok I isntalled the Jet Driver, now I would like to export to Excel or Access or PDF . Is possible through using Script Task ?  Do You hane any example ?


     Thanks !

     Andre



    andre
    Thursday, December 17, 2009 11:31 AM
  • Just a suggestion
    what i am doing is that i have a Empty template Excel file that has its own format , sheets(tab), Pie charts and etc... that is confirmed by the customer
    and each time that i want to run the ETL i just copy the empty sample excel file from the SAMPLE folder to the ToBeEmailed folder, insert the data into the excel file and email it to the users that needs to be emailed to .
    we don't have any office or etc.. installed on the production server and this package is been working for almost 2 years now
    the other benefit is that in a empty excel file i will have fixed sheet names  that will be easy to set in SSIS

    so my suggestion is to make a empty excel file insted of making one each time within the SSIS
    by the way after emailing i just copy the file to the backup folder so that i know what was emailed
    sincerely
    Sh
    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Thursday, December 17, 2009 11:56 PM
  • Enable Ad Hoc Distributed Queries. Run following code in SQL Server Management Studio – Query Editor.
    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
    GO
    RECONFIGURE;
    GO

    Create Excel Spreadsheet in root directory c:\contact.xls (Make sure you name it contact.xls). Open spreadsheet, on the first tab of Sheet1, create two columns with FirstName, LastName. Alternatively you can download sample Spreadsheet from here.

    Run following code in SQL Server Management Studio – Query Editor.
    USE [AdventureWorks];
    GO
    INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
    'SELECT * FROM [Sheet1$]')
    SELECT TOP 5 FirstName, LastName
    FROM Person.Contact
    GO

    Open contact.xls spreadsheet you will see first five records of the Person.Contact inserted into the first two columns.

    Make sure your spreadsheet is closed during this operation. If it is open it may thrown an error. You can change your spreadsheet name as well name of the Sheet1 to your desired name.

    In fact, I have another solution for you. You can have a try of RAQ Report, because you can export data to Excel easily with it. The only thing you need to do is clicking the function in the menu.

    • Marked as answer by Zongqing Li Monday, January 4, 2010 3:35 AM
    Sunday, December 20, 2009 4:06 AM