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.

• Heinrich Braun

  SSC Rookie

  Points: 36

  More actions

  November 7, 2008 at 7:50 am

  #895556

  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

  November 7, 2008 at 5:10 pm

  #895883

  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