export query result to excel file

Question

export query result to excel file

arias_melvin

SSC Rookie

Points: 48
More actions
November 7, 2008 at 2:54 am

#383326

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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Heinrich Braun SSC Rookie Points: 36 More actions · Answer 1

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:

[font="Arial Narrow"]

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

[/font]

HopeThishelps

Heinrich

arias_melvin SSC Rookie Points: 48 More actions · Answer 2

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