export query result to excel file

  • 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.

  • 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

  • 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

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

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