Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Reading a Excel File From ASP

HI
I have some data in a excel sheet.
I want to read it from ASP Script.
How should I Go about.
Any help with code will be appretiated.
Thanks in Advance.
Sri

Comments

  • JoanneJoanne Posts: 3Member
    : HI
    : I have some data in a excel sheet.
    : I want to read it from ASP Script.
    : How should I Go about.
    : Any help with code will be appretiated.
    : Thanks in Advance.
    : Sri
    :

    Hi.

    The following is an example of an ASP script reading an Excel file (it gets the file name and range from a form on another page) - you should be able to use this as a basis for whatever you wish to do:

    Const adOpenStatic = 3
    Const adLockPessimistic = 2

    Dim cnnExcel, rstExcel, I, icols
    Dim file, range

    file= Request.Form("file")
    range=Request.form("range")

    Set cnnExcel = Server.CreateObject("ADODB.Connection")
    cnnExcel.Open "DBQ=" & Server.MapPath(file&".xls") & ";" &_
    "DRIVER={Microsoft Excel Driver (*.xls)};"

    Set rstExcel = Server.CreateObject("ADODB.Recordset")
    rstExcel.Open "SELECT * FROM ["& range & "] ;", cnnExcel, adOpenStatic, adLockPessimistic

    icols= rstExcel.Fields.Count 'count the number of columns
    k=1 'count the number of rows using k
    do while not rstExcel.EOF
    Response.write rstExcel("F1")
    rstExcel.movenext
    loop
    rstExcel.movefirst
    rstExcel.close
    set rstExcel=nothing
    cnnExcel.close
    set cnnExcel= nothing
  • sriramsriram Posts: 14Member
    : : HI
    : : I have some data in a excel sheet.
    : : I want to read it from ASP Script.
    : : How should I Go about.
    : : Any help with code will be appretiated.
    : : Thanks in Advance.
    : : Sri
    : :
    :
    : Hi.
    :
    : The following is an example of an ASP script reading an Excel file (it gets the file name and range from a form on another page) - you should be able to use this as a basis for whatever you wish to do:
    :
    : Const adOpenStatic = 3
    : Const adLockPessimistic = 2
    :
    : Dim cnnExcel, rstExcel, I, icols
    : Dim file, range
    :
    : file= Request.Form("file")
    : range=Request.form("range")
    :
    : Set cnnExcel = Server.CreateObject("ADODB.Connection")
    : cnnExcel.Open "DBQ=" & Server.MapPath(file&".xls") & ";" &_
    : "DRIVER={Microsoft Excel Driver (*.xls)};"
    :
    : Set rstExcel = Server.CreateObject("ADODB.Recordset")
    : rstExcel.Open "SELECT * FROM ["& range & "] ;", cnnExcel, adOpenStatic, adLockPessimistic
    :
    : icols= rstExcel.Fields.Count 'count the number of columns
    : k=1 'count the number of rows using k
    : do while not rstExcel.EOF
    : Response.write rstExcel("F1")
    : rstExcel.movenext
    : loop
    : rstExcel.movefirst
    : rstExcel.close
    : set rstExcel=nothing
    : cnnExcel.close
    : set cnnExcel= nothing
    :


    Hi
    Thanks for the sample code sent by you.
    But I am getting some error while I execute the code.
    Instead of request.form method I have hard coded the file name and the range.
    Rest is same as you have given.
    Kindly go through the error msg and the code given below.
    Let me know Where I am going wrong.
    I will be greatful to you if you could help me on this.
    Fly
    sri

    Error Type
    Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
    /hdk-krishna/samples/exceltest.asp, line 14





    <%
    Const adOpenStatic=3
    Const adLockPessimistic=2

    Dim cnnExcel,rstExcel,I,icols
    Dim file,range

    file="Book1"
    range="a1:c2"

    Set cnnExcel=Server.CreateObject("ADODB.Connection")
    cnnExcel.Open "DBQ=" & Server.MapPath(file & ".xls") & ";" & "DRIVER={Microsoft Excel Driver(*.xls)};"

    Set rstExcel=Server.CreateObject("ADODB.Recordset")
    rstExcel.Open "SELECT * FROM ["& range &"];",cnnExcel,adOpenStatic,adLockPessimistic

    icols=rstExcel.Fields.Count ' count the number of columns
    k=1
    do while not rstExcel.EOF
    Response.write rstExcel("F1")
    rstExcel.movenext
    loop
    rstExcel.movefirst
    rstExcel.close
    set rstExcel=nothing
    cnnExcel.close
    set cnnExcel=nothing



    %>





    New Page 2










  • JoanneJoanne Posts: 3Member
    : : : HI
    : : : I have some data in a excel sheet.
    : : : I want to read it from ASP Script.
    : : : How should I Go about.
    : : : Any help with code will be appretiated.
    : : : Thanks in Advance.
    : : : Sri
    : : :
    : :
    : : Hi.
    : :
    : : The following is an example of an ASP script reading an Excel file (it gets the file name and range from a form on another page) - you should be able to use this as a basis for whatever you wish to do:
    : :
    : : Const adOpenStatic = 3
    : : Const adLockPessimistic = 2
    : :
    : : Dim cnnExcel, rstExcel, I, icols
    : : Dim file, range
    : :
    : : file= Request.Form("file")
    : : range=Request.form("range")
    : :
    : : Set cnnExcel = Server.CreateObject("ADODB.Connection")
    : : cnnExcel.Open "DBQ=" & Server.MapPath(file&".xls") & ";" &_
    : : "DRIVER={Microsoft Excel Driver (*.xls)};"
    : :
    : : Set rstExcel = Server.CreateObject("ADODB.Recordset")
    : : rstExcel.Open "SELECT * FROM ["& range & "] ;", cnnExcel, adOpenStatic, adLockPessimistic
    : :
    : : icols= rstExcel.Fields.Count 'count the number of columns
    : : k=1 'count the number of rows using k
    : : do while not rstExcel.EOF
    : : Response.write rstExcel("F1")
    : : rstExcel.movenext
    : : loop
    : : rstExcel.movefirst
    : : rstExcel.close
    : : set rstExcel=nothing
    : : cnnExcel.close
    : : set cnnExcel= nothing
    : :
    :
    :
    : Hi
    : Thanks for the sample code sent by you.
    : But I am getting some error while I execute the code.
    : Instead of request.form method I have hard coded the file name and the range.
    : Rest is same as you have given.
    : Kindly go through the error msg and the code given below.
    : Let me know Where I am going wrong.
    : I will be greatful to you if you could help me on this.
    : Fly
    : sri
    :
    : Error Type
    : Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
    : [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
    : /hdk-krishna/samples/exceltest.asp, line 14
    :
    :
    :
    :
    :
    : <%
    : Const adOpenStatic=3
    : Const adLockPessimistic=2
    :
    : Dim cnnExcel,rstExcel,I,icols
    : Dim file,range
    :
    : file="Book1"
    : range="a1:c2"
    :
    : Set cnnExcel=Server.CreateObject("ADODB.Connection")
    : cnnExcel.Open "DBQ=" & Server.MapPath(file & ".xls") & ";" & "DRIVER={Microsoft Excel Driver(*.xls)};"
    :
    : Set rstExcel=Server.CreateObject("ADODB.Recordset")
    : rstExcel.Open "SELECT * FROM ["& range &"];",cnnExcel,adOpenStatic,adLockPessimistic
    :
    : icols=rstExcel.Fields.Count ' count the number of columns
    : k=1
    : do while not rstExcel.EOF
    : Response.write rstExcel("F1")
    : rstExcel.movenext
    : loop
    : rstExcel.movefirst
    : rstExcel.close
    : set rstExcel=nothing
    : cnnExcel.close
    : set cnnExcel=nothing
    :
    :
    :
    : %>
    :
    :
    :
    :
    :
    : New Page 2
    :
    :
    :
    :
    :
    :
    :
    :
    :
    :
    :
    Hi.

    Have a quick look at:
    http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306345
    which gives possible reasons for the error you are getting.

    Where are you trying to test this? I am wondering if the server you are using doesn't have Excel installed on it - If this is on your PC it's easy to check - if you look in:
    My Computer
    Control Panel
    ODBC Data Sources
    You should see under the Drivers tab:
    Microsoft Excel Driver (*.xls)

    If the web server you are using doesn't have the Excel drivers installed then it won't be able to open Excel files.
    Try this route first and let me know how you get on.
    :~)
Sign In or Register to comment.