Output SQL Table to Excel?

Discussion in 'OT Technology' started by TrevorK, Jan 6, 2003.

  1. TrevorK

    TrevorK New Member

    Joined:
    Mar 12, 2002
    Messages:
    692
    Likes Received:
    0
    Location:
    Alberta
    What I would like to do, is when anyone updates a record in my table (Through the ASP page), I would like to create an Excel file with all of the table's data.

    Now, I developed the query for all the data I want. But I can't seem to get it exported to Excel from SQL.

    Has anyone done this before successfully?

    And, in order to do the export, would I need Excel installed on the web server that is server the ASP page? The SQL server that hosts the SQL? Both?
     
  2. Dommi

    Dommi Guest

    phpmyadmin can do this...
     
  3. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    if you have SQL Server then you should have enterprise manager. Run that, right click the database you want to export from, then you can specify what to export to (excel should be an option), and specify a table/query. You shouldn't have to have Excel installed in order to export. The MDAC library contains that functionality.
     
  4. TrevorK

    TrevorK New Member

    Joined:
    Mar 12, 2002
    Messages:
    692
    Likes Received:
    0
    Location:
    Alberta
    That's the caveat - DTS isn't working on my clients server and they do not want me to spend my time (Which is their money) to fix it.

    Is there a string of SQL Code I could use to do it? I need to do it every time a record is updated, so this would be ideal...
     
  5. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    there is no direct export to excel. If they're running sql server 2000 then you can throw it down to xml natively, and them maybe write your own parser to convert to excel, but nothing native to do what you would like.
     
  6. TrevorK

    TrevorK New Member

    Joined:
    Mar 12, 2002
    Messages:
    692
    Likes Received:
    0
    Location:
    Alberta
    Whatt about in ASP?
     
  7. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    You can do it in Perl.. Have it create to a .csv file.. That's easy enough..
     
  8. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    you'll have to create it server-side and then stream it to the client (if that is the plan). I'm sure there is some code out there in asp. You might have to install Excel on the server at that point though. not sure.
     
  9. TrevorK

    TrevorK New Member

    Joined:
    Mar 12, 2002
    Messages:
    692
    Likes Received:
    0
    Location:
    Alberta
    Perl? I've never used Perl...Do ya have an example?
     
  10. Black Light

    Black Light Guest

    In MS Excel:

    Data-->Get External Data-->New Database Query :slap:

    Geez! This is sooo easy! :o
     
  11. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Re: Re: Output SQL Table to Excel?

    I hope you're joking :squint:
     
  12. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    Re: Re: Output SQL Table to Excel?

    i thought he wanted it automated though :confused:
     
  13. Black Light

    Black Light Guest

    Re: Re: Re: Output SQL Table to Excel?

    Instantiate MS Excel object then use automation to populate it. You want code for that in VB6/VBA? It's very easy. :confused:

    Code:
    Sub DAO2Excel()
    
       'This routine transfers the contents of a recordset into an excel
    spreadsheet
    
        Dim NrCols As Long
        Dim i As Integer
        Dim HomeCell As String
        Dim FileName As String
    
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
    
        Set xlApp = CreateObject("Excel.Application")
        Set xlBook = xlApp.Workbooks.Add
        Set xlSheet = xlBook.Worksheets(1)
    
        FileName = "YOURWORKSHEETNAME"
    
        xlApp.Visible = True        ' Make the spreadsheet visible
        xlApp.DisplayAlerts = False ' Eliminate prompts for user input
    
        xlSheet.Name = FileName
        xlBook.Sheets("Sheet2").Delete
        xlBook.Sheets("Sheet3").Delete
    
    
        ' Setup DAO
        Dim db As Database
        Dim rs As Recordset
        Set db = OpenDatabase("D:\YOURFOLDER\YOURDBHERE.mdb", , True)
        Set rs = db.OpenRecordset(FileName, dbOpenTable)
        rs.MoveLast
        rs.MoveFirst
    
        HomeCell = "A2"
        NrCols = rs.Fields.Count
    
        For i = 0 To NrCols - 1
            Sheets(FileName).Range("A1").Offset(, i) = rs.Fields(i).Name
        Next i
    
        xlSheet.Range(HomeCell).CopyFromRecordset rs
    
        Columns("BF:BT").Select
        Selection.NumberFormat = "0.0"
    
        xlSheet.Range("A1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Columns.AutoFit
    
    
        FileName = FileName & ".DBF"
        xlBook.SaveAs FileName:=cPath & FileName, FileFormat:=xlDBF4,
    CreateBackup:=False
        xlApp.Quit   ' When you finish, use the Quit method to close
    
        Set xlBook = Nothing
        Set xlSheet = Nothing
        Set xlApp = Nothing   ' the application, then release the reference\
        Set rs = Nothing
        Set db = Nothing
    
    End Sub
    
     
    Last edited by a moderator: Jan 7, 2003
  14. Black Light

    Black Light Guest

    This is even better:
    Code:
    <HTML>
    <BODY>
    
    <INPUT id=button1 name=button1 type=button value=Button>
    
    <SCRIPT LANGUAGE="VBScript">
    
           sub button1_onclick()
    
              ' Launch Excel
              dim app
              set app = createobject("Excel.Application")
        
              ' Make it visible
              app.Visible = true
        
              ' Add a new workbook
              dim wb
              set wb = app.workbooks.add
        
              ' Fill array of values first...
              dim arr(19,9) ' Note: VBScript is zero-based
              for i = 1 to 20
                 for j = 1 to 10
                    arr(i-1,j-1) = i*j
                 next
              next
        
              ' Declare a range object to hold our data
              dim rng
              set rng = wb.Activesheet.Range("A1").Resize(20,10)
        
              ' Now assign them all in one shot...
              rng.value = arr
        
              ' Add a new chart based on the data
              wb.Charts.Add
              wb.ActiveChart.ChartType = 70 'xl3dPieExploded
              wb.ActiveChart.SetSourceData rng, 2 ' xlColumns
              wb.ActiveChart.Location 2, "Sheet1" 'xlLocationAsObject
        
              ' Rotate it around...
              for i = 1 to 360 step 30
                 wb.activechart.rotation = i
              next
    
              ' Give the user control of Excel
              app.UserControl = true
    
           end sub
    </SCRIPT>
    
    </BODY>
    </HTML>
    http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q257757

    :dunno:
     
    Last edited by a moderator: Jan 7, 2003
  15. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    It's really basic.. It doesn't have all your error handling and what not. All you need to worry about his having DBI installed and the correct DB file DBI.

    Here it is..

    Code:
    #!/usr/bin/perl
    #
    #   db_get.pl
    #     Grabs information from a database and writes it to file.
    #
    #   Written By: Joseph Bajin  ([email protected])
    #
    #
    use DBI;
    
    
    ### Initalize some Variables ####
    my $file="excel.csv";
    my $CODELIMITER = ',';
    my $EDELIMITER = "\n";
    
    
    ### Open the output file 
    open OUTPUT, ">$file" || die "Unable to open output file";
    
    ### Make database connection ##
    my $dbh= DBI->connect("dbi:<TYPE OF DB>:<HOSTNAME>","username","password") || die "Database Connection not made $DBI::errstr";
    my $sql = "<SELECT STATEMENT";
    my $sth = $dbh->prepare( $sql ) || die "Unable to prepare SQL statement";
    $sth->excecute();
    
    while ($array_ref= $sth->fetch() )
    {
    print OUTPUT join($CODELIMITER,@$array_ref),"$EDELIMITER";
    }
    close OUTPUT;
    
    
    
     

Share This Page