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

LVL 1
sknightAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jerrypdCommented:
you are going to need to create a "disconnected recordset".
In order to do that, you need to know what each of the fields contain - i.e. string, number, date, etc...
Once you have that, we can build the recordset, open it, and then "fill it" in a loop.
If you know the field types, post them and I will help you create the recordset.
0
sknightAuthor Commented:
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.
0
jerrypdCommented:
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!
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

R_HarrisonCommented:
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

0
R_HarrisonCommented:
Sorry slight error in the above code, use the version below...
<%
strPathtoCSVFile="c:\inetpub\csvfiles\"

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 nameofyourcsvfile.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 nameofyourcsvfile", 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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
R_HarrisonCommented:
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
 
0
R_HarrisonCommented:
Damn!   Make that lines 7 & 15!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.