Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

export query result to excel file Expand / Collapse
Author
Message
Posted Friday, November 07, 2008 2:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, November 07, 2008 7:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 04, 2013 8:06 AM
Points: 7, Visits: 1,108
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
Posted Friday, November 07, 2008 5:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse