Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2006
    Posts
    10

    Unanswered: export excel data to ASP

    Hi All,

    I am working on an ASP application where I need to automate the process of exporting the data from Excel to SQL server.

    Please suggest how to go about it.

    Madhuri

  2. #2
    Join Date
    Mar 2006
    Location
    south jersey, usa
    Posts
    53
    use a ado connection to allow access to excel and sql.

    http://www.devguru.com/Technologies/...ado_intro.html

    Code:
    dim sqlserver, sqluser, sqlpwd, sqldb
    dim sconn, srs
    
    dim excelfile
    dim econn, ers
    
    dim query
    
    sub sqlconn ' open a connection to access db'
      set sconn = CreateObject("ADODB.Connection") 'prep connection'
      set srs = CreateObject("ADODB.Recordset") ' prep recordset'
      sconn.open "PROVIDER=SQLOLEDB;DATA SOURCE=" & sqlserver & ";UID=" & sqluser &_
                 ";PWD=" & sqlpwd & ";DATABASE=" & sqldb
    end sub
    
    sub closesql ' close the connection to the db
      srs.close
      set srs = nothing
      sconn.close
      set sconn = nothing
    end sub
    
    sub excelconn ' open a connection to access db'
      set econn = CreateObject("ADODB.Connection") 'prep connection'
      set ers = CreateObject("ADODB.Recordset") ' prep recordset'
      eConn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverID=790;" &_
                      "DBQ=" & excelfile & ";DefaultDir=;UID=;PWD=;"
    end sub
    
    sub closeexcel ' close the connection to the db
      ers.close
      set ers = nothing
      econn.close
      set econn = nothing
    end sub
    you'll need to setup named ranges in excel to access its data. The first row of your range will be the column names and the name of the range will be the table name.

    run select queries on excel data. run insert queries to import into sql.





    there is an easier way to import from excel to sql. you can use sql's Data Transformation Services Import/Export Wizard. look in programs | ms sql server | import export
    "They say Moses split the Red Sea
    I split the blunt and rolled the fat one, I'm deadly"
    -- Tupac 'Blasphemy'

Posting Permissions

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