Professional Web Applications Themes

create an Excel spreadhseet - Microsoft SQL / MS SQL Server

shank, It depends on how you want to do it. However, there are instructions in the following article that may answer your need. [url]http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b306125[/url] Russell Fields "shank" <shanktampabay.rr.com> wrote in message news:%23fo2ePWQDHA.1988TK2MSFTNGP12.phx.gbl... > How can I use SQL to create an excel spreadsheet? We have lists online and > depending on what a user selects, I'd like the date dumped into an *.XLS > file that they can download. > > What's the process on something like that? > thnaks > >...

  1. #1

    Default Re: create an Excel spreadhseet

    shank,

    It depends on how you want to do it. However, there are instructions in the
    following article that may answer your need.

    [url]http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b306125[/url]

    Russell Fields
    "shank" <shanktampabay.rr.com> wrote in message
    news:%23fo2ePWQDHA.1988TK2MSFTNGP12.phx.gbl...
    > How can I use SQL to create an excel spreadsheet? We have lists online and
    > depending on what a user selects, I'd like the date dumped into an *.XLS
    > file that they can download.
    >
    > What's the process on something like that?
    > thnaks
    >
    >

    Russell Fields Guest

  2. #2

    Default Re: create an Excel spreadhseet

    Ex:
    bcp "select * from northwind..orders" queryout
    c:\cust.xls -c -SMDC-MHP1DXP0025\PREDATOR -Usa -P

    above command will create a file with extension XLS but, in fact its a tab
    delimited flat file . Since it has extension XLS by default it will get
    opened in EXCEL assuming tab as a column delimiter.
    I've put TAB as a column delimiter assuming this character is not used in
    any of the varchar/char field. because if you are including such a character
    which exists in the varchar/char datatype whole format will get disturbed.
    comma seperated file is a good example for this.
    Also point to be noted that if any of the char/varchar field contains TAB
    character even this file's format will get disturbed.

    you can also run above command line using xp_cmdshell and thus can be used
    in T-SQL script.

    Ex

    exec master..xp_cmdshell 'bcp "select * from northwind..orders" queryout
    c:\cust.xls -c -S<server> -Usa -P'


    --
    -Vishal
    "shank" <shanktampabay.rr.com> wrote in message
    news:#fo2ePWQDHA.1988TK2MSFTNGP12.phx.gbl...
    > How can I use SQL to create an excel spreadsheet? We have lists online and
    > depending on what a user selects, I'd like the date dumped into an *.XLS
    > file that they can download.
    >
    > What's the process on something like that?
    > thnaks
    >
    >

    Vishal Parkar Guest

  3. #3

    Default Re: create an Excel spreadhseet

    I use this DTS Active Script Task to write to an .xls file....

    hth
    bob

    '************************************************* *********************
    ' Visual Basic ActiveX Script
    '************************************************* ***********************

    Function Main()

    Dim xlApp
    Dim xlBook
    Dim i

    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open("C:\~otg.util\ReservationList .xls")

    Dim sqlText
    sqlText = "SELECT " _
    & "QRCHeader.Status, " _
    & "QRCHeader.[QRC#], " _
    & "Units.Location, " _
    & "Addresses.Lastname, " _
    & "QRCHeader.Active ha, " _
    & "QRCDetail.Active da, " _
    & "QRCDetail.[Unit#], " _
    & "QRCDetail.OnRentDate, " _
    & "Units.Status us, " _
    & "Units.RentalStatus " _
    & "FROM " _
    & "QRCHeader " _
    & "INNER JOIN QRCDetail ON " _
    & "QRCHeader.[QRC#] = QRCDetail.[QRC#] AND QRCHeader.Division =
    QRCDetail.Division " _
    & "INNER JOIN Addresses ON " _
    & "QRCHeader.[Addr#] = Addresses.[Addr#] " _
    & "LEFT JOIN Units ON QRCDetail.[Unit#] = Units.[Unit#] " _
    & "WHERE " _
    & "QRCHeader.Active='Y' AND " _
    & "QRCDetail.Active='Y' AND " _
    & "Units.Status='R' AND " _
    & "Units.RentalStatus<>'RE' " _


    dim con
    dim rs

    set con = createobject("ADODB.Connection")
    set rs = createobject("ADODB.recordset")

    con.open = "Provider = SQLOLEDB.1; data source = (local); initial catalog
    = EIS; user id = 'sa'; password = ;"
    rs.open sqltext, con

    i = 1

    xlBook.Worksheets(1).Range("A5..m500") = Null
    xlBook.Save

    'For i = i To rs.recordcount

    xlBook.Worksheets(1).Range("A" & i) = now()
    i = 5
    do while not rs.eof
    xlBook.Worksheets(1).Range("A" & i) = rs.fields("Status")
    xlBook.Worksheets(1).Range("B" & i) = rs.fields("qrc#")
    xlBook.Worksheets(1).Range("C" & i) = rs.fields("Location")
    xlBook.Worksheets(1).Range("D" & i) = rs.fields("LastName")
    xlBook.Worksheets(1).Range("E" & i) = rs.fields("ha")
    xlBook.Worksheets(1).Range("F" & i) = rs.fields("da")
    xlBook.Worksheets(1).Range("G" & i) = rs.fields("unit#")
    xlBook.Worksheets(1).Range("H" & i) = rs.fields("OnRentDate")
    xlBook.Worksheets(1).Range("I" & i) = rs.fields("us")
    xlBook.Worksheets(1).Range("J" & i) = rs.fields("RentalStatus")

    rs.movenext
    i = i +1
    'Next
    loop
    xlBook.Save

    rs.close
    set rs = nothing

    xlBook.Close
    Set xlBook = Nothing

    xlApp.Quit
    Set xlApp = Nothing

    Main = DTSTaskExecResult_Success
    End Function



    "shank" <shanktampabay.rr.com> wrote in message
    news:#fo2ePWQDHA.1988TK2MSFTNGP12.phx.gbl...
    > How can I use SQL to create an excel spreadsheet? We have lists online and
    > depending on what a user selects, I'd like the date dumped into an *.XLS
    > file that they can download.
    >
    > What's the process on something like that?
    > thnaks
    >
    >

    BobMcClellan Guest

Similar Threads

  1. Excel create object error
    By Jeroen de Wolf in forum ASP Components
    Replies: 1
    Last Post: October 4th, 12:57 PM
  2. Web service in .net not able to create Excel com object
    By Nalina Vemparala via .NET 247 in forum ASP.NET Web Services
    Replies: 3
    Last Post: May 21st, 12:56 PM
  3. Read/write Excel and create pdf??
    By flashPipe in forum Macromedia Director Basics
    Replies: 0
    Last Post: April 6th, 05:23 PM
  4. Create Excel worksheet by using web service
    By Tabo in forum ASP.NET Web Services
    Replies: 1
    Last Post: October 16th, 06:10 PM
  5. Create an Excel File from and ASP page
    By Cathy Bro in forum ASP
    Replies: 0
    Last Post: September 25th, 02:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139