asked on

CSV to Recordset, Classic ASP

I have a CSV file, that I want to turn into a recordset that I can sort, manipulate, and reference individual fields. I found some code online that I can use to simply display the contents of the CSV, but I really need a way to store it in a recordset so I can tweak the data... Any suggestions?

I've done my googling and only found Recordset to CSV - but I need it the other way around, and the ability to reference individual records.

<%@language="vbscript"%>
<table border="1">
<%
'dim csv_to_read, fso, act, imported_text,split_text, total_imported_text,total_split_text,total_num_imported
dim csv_to_read,counter,line,fso,objFile

csv_to_read="Data.csv"
counter=0
set fso = createobject("scripting.filesystemobject")
set objFile = fso.opentextfile(server.mappath(csv_to_read))

Do Until objFile.AtEndOfStream
    line = split(objFile.ReadLine,""",""")
    counter=counter + 1
    
    Response.Write "<tr>"
    
    Response.Write "<td valign=top>"&counter-1&"</td>"
    
    if counter > 1 Then
		for i=0 to ubound(line)
			Response.Write REPLACE(("<td valign=top>"&line(i)&"&nbsp;</td>"),"""","")
		next
    else
		for i=0 to ubound(line)
			Response.Write REPLACE(("<th>"&line(i)&"</th>"),"""","")
		next
    end if
    Response.Write "</tr>" & chr(13)
Loop
objFile.Close
%><caption>Total Number of Records: <%=counter-1%></caption>
</table>

Open in new window

SOLUTION

jerrypd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sknight

ASKER

I had planned on probably doing somethig to read the first line of the CSV to get the field column name, and use memo type for all fields. The file could change, so the fields could change as well, so it would need to be dynamic based off row 1 of the CSV.

jerrypd

ahhh then i would recommend using text/varchar for all your fields I think. I am not sure what would happen , but I think that would work!

R_Harrison

The code below will read the CSV as a recordset. You could then drop this into temporary database table if required. The arrays FieldNames() and FieldTypes() hold the information from the first line of the csv file and could be used to create the database table.

<%
strPathtoCSVFile="D:\shared\Websites\www_marques_org\TESTZONE\CSV\"

Set Conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPathtoCSVFile & ";Extended Properties=""text;HDR=YES;FMT=Delimited"""
RS.Open "SELECT * FROM test.csv", Conn, 1, 3, &H0001
redim FieldNames(rs.fields.count)
redim FieldTypes(rs.fields.count)
For i = 0 To (rs.Fields.Count - 1)
    FieldNames(i) = cstr(trim(rs.Fields.Item(i).Name))
    FieldTypes(i) = cstr(trim(rs.Fields.Item(i).Type))
Next

rs.Close
RS.Open "SELECT * FROM test.csv ORDER BY surname ASC", Conn, 3, 3, &H0001
Do Until RS.EOF
	for i=0 to ubound(FieldNames)-1
		response.write(FieldNames(i) & " = " & RS.Fields.Item(FieldNames(i)) & "<br>")
	next
    RS.MoveNext
Loop
RS.Close
Conn.Close
%>

Open in new window

ASKER CERTIFIED SOLUTION

R_Harrison

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

R_Harrison

Sorry EE is messing about with my posts. In the code on line 7 & 11 where it says "nameofyourcsvfile" they should of course both end with .csv

R_Harrison

Damn! Make that lines 7 & 15!