export query result to excel file

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Prashant Pandey » export query result to excel file

export query result to excel file

Rate Topic

Display Mode

Topic Options

Author

Message

arias_melvin

Posted Friday, November 07, 2008 2:54 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, November 21, 2008 11:21 PM
Points: 2, Visits: 7

how can i export the result of my query below to excel file using
ms sql script?

select empno, name, premium from application

could anyone provide me a script

thanks.

Post #598750

Heinrich Braun

Posted Friday, November 07, 2008 7:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 2:47 AM
Points: 6, Visits: 1,104

First possible method, if you need to export data to excel only now and then:
Execute the query in MS SQL Management Studio, right klick on the result grid, save data as csv-file, import this file into your excelsheet

Second possibility: using MS-Query, an option included in MS-Office suite, must be installed separatedly.
First create ODBC-connection. Open excel, select Data->import external data ->create new query
Select your ODBC-Connection, log in, select table, select fields, sorting ... quite easy

Third: create a viusal basic macro in excel, opening a database using an ADO connection-object:

Sub Retrieve_Records_from_Mytable1()

Dim sql As String
Dim fst As String

Dim wsht As Worksheet
Dim row, col, col1 As Integer
Dim frdid
Dim Con

' ADODB Connection

Set Con = CreateObject("ADODB.Connection")
Con.ConnectionString = "Provider=sqloledb;Data Source=localhost\sqlexpress;Initial Catalog=mydb;User Id=sa;Password=sapasswort"

Con.Open

Set rs = CreateObject("ADODB.Recordset")

frdid = InputBox("search for references ", "Referenz")

sql = "select * from mytable1 where reference like '" & frdid & "' order by reference"

rs.Open sql, Con

Set wsht = Worksheets(1)
wsht.Cells.Clear

col1 = 1

While col1 < rs.Fields.Count + 1
wsht.Cells(1, col1).Value = rs(col1 - 1).Name
wsht.Cells(1, col1).Font.Bold = True
wsht.Cells(1, col1).Font.Size = 12
wsht.Cells(1, col1).Font.Color = 50000
wsht.Cells(1, col1).Columns.AutoFit

'MsgBox "Fieldname " & rs(col1 - 1).Name & " Typ " & rs(col1 - 1).Type & " Predefined " & dbSTring
col1 = col1 + 1
Wend

'MsgBox "no of fields = " & rs.Fields.Count

row = 2 'no col titles
col = 1 'start at first cell

While Not rs.EOF
While col < rs.Fields.Count + 1

If Not IsNull(rs(col - 1).Value) Then
If rs(col - 1).Type = 200 Then wsht.Cells(row, col).NumberFormat = "@" 'als Text (Typ 200 ist String)
wsht.Cells(row, col).Value = CStr(rs(col - 1).Value)
'MsgBox "Wert von Feld " & col & " = " & rs(col-1).Value
Else
wsht.Cells(row, col).Value = ""
End If 'If Not IsNull(rs(col - 1).Value) Then

col = col + 1 'next col
Wend 'While col < rs.Fields.Count + 1
row = row + 1 'next line
col = 1 'first col
rs.MoveNext
Wend 'While Not rs.EOF

rs.Close
Con.Close
wsht.Columns.AutoFit
wsht.Name = "DS_as_" & frdid
Beep
MsgBox (row & " records retrieved")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

HopeThishelps

Heinrich

Post #598958

arias_melvin

Posted Friday, November 07, 2008 5:10 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, November 21, 2008 11:21 PM
Points: 2, Visits: 7

i already did the first solution. thanks for that. however i need to automate the extraction of data direct to excel that when i run the script it will create an excel file containing the selected data from a table in my database. i tried the bcp but it did not work.

could u give an idea or sample code regarding the above.

thanks

Post #599350

« Prev Topic | Next Topic »

Permissions