Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    Brighton, UK
    Posts
    282
    Thanks
    1
    Thanked 0 Times in 0 Posts

    converting excel to ms access - asp & adding fields

    Hi guys I am trying to upload an excel spread sheet, read the information from this and then convert all this information into an asp database.

    I have the main concept on how to open read, add info in MS Access, but i am a little confused on how to read the excel info (ie how many fields and then create these in ms access).. if you know any links that will be good.. my knowledge on create new fields via SQL in minimal..

    this is how i think i can do it..

    upload my file, take the name and then

    Set objExcel = Server.CreateObject("Excel.Application")
    objExcel.Workbooks.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ= " & Server.MapPath('" & my excel file & "')

    i am not to sure how to determine how many fields are in ?? any ideas on this...

    once i have done this create these in asp ms access

    Set objConnection = Server.CreateObject("ADODB.Connection")
    objConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ= " & Server.MapPath('" & my access bd & "')

    HOW DO I CREATE FIELDS ???

    Once i have created both, i take it i can loop through the excel and then add to the access... ie

    While not objRecords.EOF
    objExcel.ActiveSheet.Range("name" & i).Value = Recordset("ID")
    i = i + 1
    Wend

    after this save
    close connections and update..

    but i am just stuck on filding the excel info and creating new fields in ms access??

    any help would be great

    many thaks chris
    <marquee>thanks</marquee>

  • #2
    Senior Coder BarrMan's Avatar
    Join Date
    Feb 2005
    Location
    Israel.
    Posts
    1,644
    Thanks
    69
    Thanked 83 Times in 82 Posts
    Hey, You'll need to do the followings:
    1. Open your excel file.
    2. Select the fields you want to convert.
    3. Click "Insert" on the menu --> Name --> Define.
    4. Write down the name of the workspace (this name will be used later on the sql query).
    5. Save.

    After you've done this, you can simply access your excel file by using the following query:
    Code:
    SQL = "SELECT * FROM your_workspace_name"
    Hope that helped.

  • #3
    Regular Coder
    Join Date
    Jul 2002
    Location
    Brighton, UK
    Posts
    282
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks for the post.. but i am trying to let a client upload an excel spread sheet, from local machine to the server, and then using asp to take the info from excell and put it into an ms access with maching fields, ?? and also if the ms access needs it extra fields,

    i hope to create new fields using SQL.

    i hope this make things a little more clear..

    cheers chris
    <marquee>thanks</marquee>

  • #4
    SSJ
    SSJ is offline
    Regular Coder
    Join Date
    Mar 2007
    Posts
    230
    Thanks
    0
    Thanked 4 Times in 4 Posts
    visit this url:
    http://www.15seconds.com/howto/pg000950.htm

    Hope this will help you..

  • #5
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    converting excel to ms access - asp

    Hi,

    Just wanted to ask for some help regarding the following piece of code, I'm trying to read from the excel file and update the data into and access database. Reading from excel file works fine and I can display it on the web in HTML but when I try to update the recordset in the access database it gives me an error.
    Following is the error and the code, I would really appreciate if you can help me with this.
    ERROR:
    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
    Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
    /mgma/loadxls.asp, line 58

    CODE:

    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    <!--#include file="Connections/mgma.asp" -->
    <%
    Dim rsAdd
    Dim rsAdd_numRows

    Set rsAdd = Server.CreateObject("ADODB.Recordset")
    rsAdd.ActiveConnection = MM_mgma_STRING
    rsAdd.Source = "SELECT * FROM tRDATA"
    'rsAdd.CursorType = 2
    rsAdd.CursorLocation = 2
    rsAdd.LockType = 3
    rsAdd.Open()
    'rsAdd.AddNew

    rsAdd_numRows = 0
    %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Untitled Document</title>
    </head>
    <body>
    <%
    Dim objConn, objRS, strSQL
    Dim x, curValue, y

    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
    "Excel 8.0; DBQ=" & Server.MapPath("EXCEL_REPORTS\2003_04_05_Phy_Comp.xls") & "; "
    strSQL = "SELECT * FROM A1:Q10000"
    Set objRS=objConn.Execute(strSQL)
    %>
    <table border="0">
    <tr>
    <%
    For x=0 To objRS.Fields.Count-1
    Response.Write("<th>" & objRS.Fields(x).Name & "</th>")
    Next
    Do Until objRS.EOF
    'rsAdd.MoveFirst
    Response.Write("<tr>")
    For x=0 To objRS.Fields.Count-1
    curValue = objRS.Fields(x).Value
    If IsNull(curValue) Then
    curValue=" "
    End If
    curValue = CStr(curValue)
    y = y &","& curValue
    Response.Write("<td>" & curValue & "</td>")
    Next
    Response.Write("</tr>")
    arrMy = split(y, ",")
    '+++++++++++++++++++
    rsAdd.AddNew
    For x=1 to rsAdd.Fields.Count-1
    rsAdd.Fields(x).value=arrMy(x)
    next
    rsAdd.Update
    '+++++++++++++++++++
    objRS.MoveNext
    y = " "
    Loop
    %>
    </tr>
    </table>
    </body>

    </html>
    <%
    rsAdd.Close
    Set rsAdd = Nothing
    %>

  • #6
    SSJ
    SSJ is offline
    Regular Coder
    Join Date
    Mar 2007
    Posts
    230
    Thanks
    0
    Thanked 4 Times in 4 Posts


  •  

    Posting Permissions

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