Results 1 to 4 of 4

Thread: ASP Classic xlsx file connect and retrieve columns

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2024
    Posts
    2

    ASP Classic xlsx file connect and retrieve columns

    Hi there,

    Is there any asp classic code to retrieve all the columns from a csv file, some columns are not required therefore I need to select only those columns I need to process the data; currently i have to delete the columns from csv manually.

    the code below works for showing the data from csv.

    thanks.

    ===========================
    data_filenam = request("csvfilename")
    x = server.MapPath("csv")

    Set ConnCSV = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    ConnCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & x & ";Extended Properties=""text;HDR=YES;"""

    strDirInfoX="SELECT * FROM " & data_filenam

    set rs = ConnCSV.Execute(strDirInfoX)

    if not rs.eof then
    response.write rs(0) &"<br>"
    response.write rs(1) &"<br>"
    response.write rs(2) &"<br>"
    end if
    =========================


    This code works when executed directly in the MySQL Admin interface but does not work when replaced with strDirInfoX sql code above

    strDirInfoX="SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'importDataFile' "
    Last edited by himali; Feb 24th, 2024 at 10:58 PM. Reason: adding an update

  2. #2
    Addicted Member
    Join Date
    Jul 2022
    Posts
    203

    Re: ASP Classic xlsx file connect and retrieve columns

    Your strDirInfoX query is a MySQL query, in particular using INFORMATION_SCHEMA.COLUMNS so the Jet OLEDB engine doesn't recognize that.

    I don't have a VBScript page to test with but try this:
    Code:
    data_filenam = request("csvfilename")
    x = server.MapPath("csv")
    
    Set ConnCSV = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    ConnCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & x & ";Extended Properties='text;HDR=YES;FMT=Delimited';"
    
    strDirInfoX="SELECT * FROM " & data_filenam
    
    set rs = ConnCSV.Execute(strDirInfoX)
    
    Do While not rs.eof 
        response.Write rs(0) &"<br>"
        response.Write rs(1) &"<br>"
        response.Write rs(2) &"<br>"
        rs.movenext
    loop
    As long as the csv where the first row is a header then you can use those as the columns you want instead of using * remember to surround any field names with spaces in them with [] .. like [First Name] (edit): and a required FMT=Delimited was missing from the connection string. This might depend on the version of ADO you're using though. I was looking at version 2.8

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2024
    Posts
    2

    Resolved Re: ASP Classic xlsx file connect and retrieve columns

    thanks for your input and clarification on strDirInfoX query, it lead me to use HDR as NO and it works.

    What I have done now is, instead of HDR=YES, I used HDR=NO, so the query shows the column names as a piece of data

    Here's what I'm using in the code, which works for me.

    ====================================
    data_filenam = request("csvfilename")
    x = server.MapPath("csv")

    Set ConnCSV = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    ConnCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & x & ";Extended Properties=""text;HDR=NO;"""
    strDirInfoX = "SELECT * FROM " & data_filenam
    set rs = ConnCSV.Execute(strDirInfoX)

    if not rs.eof then
    %>
    <form action="import_process.asp" method="post">
    <%
    For i = 0 To 115
    %>
    <pre>
    <input name="csv_column" type="checkbox" value="<%=i%>"> <%= Server.HTMLEncode(rs(i))%>
    </pre>
    <%
    Next
    %>
    <button type="submit" class="btn btn-primary">IMPORT</button>
    </form>
    <%end if%>
    ====================================

  4. #4
    Addicted Member
    Join Date
    Jul 2022
    Posts
    203

    Re: ASP Classic xlsx file connect and retrieve columns

    Excellent news. Glad you were able to get it sorted out.

Posting Permissions

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



Click Here to Expand Forum to Full Width