ASP Classic xlsx file connect and retrieve columns

[himali]

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' "

[jdelano]

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:

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

[himali]

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%>
====================================

[jdelano]

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