Member Avatar for doraima29

How to import CSV data line by line with ASP?
I wanted this code to import data line by line after a form has been submitted

I'm using a Classic ASP to code all of the dynamic stuff

I have this code--

<title>Import CSV to HTML</title>
<body bgcolor="#FFFFFF">
<%
csv_to_read="states.csv"
set fso = createobject("scripting.filesystemobject")
set act = fso.opentextfile(server.mappath(csv_to_read))

imported_text = act.readline
'Read the first line of the csv, typically these are colum headings

imported_text = replace(imported_text,chr(13),",")
'Change the line breaks to commas to delimit through-out

imported_text = replace(imported_text,chr(34),"")
'Remove al quotes (If your csv has quotes other than to seperate text
'You may want to remove this modifier to the imported text

split_text=split(imported_text,",")
'Split the top line by comma

num_imported=ubound(split_text)+1
'Count the number of splits and add one for the last element

total_imported_text = act.readall
'Read the rest of the csv

total_imported_text = replace(total_imported_text,chr(13),",")
'Change the line breaks to commas to delimit through-out

total_imported_text = replace(total_imported_text,chr(34),"")
'Remove al quotes (If your csv has quotes other than to seperate text
'You may want to remove this modifier to the imported text

total_split_text=split(total_imported_text,",")
'Split the file up by comma

total_num_imported=ubound(total_split_text)
'Count the number of splits
'This will be the numer of cells in the table
%>
<table width="100%">
<tr>
<%
for table = 0 to num_imported -1
'This will create a table cell for each column in the csv
' (-1 is used because arrays begin with 0)
%>
<td width="<% response.write 100/(num_imported) 'make the cell widths even %>%">
<b><%= split_text(count) %></b>
</td>
<%
count=count+1
next
%>
</tr>
<tr>
<%
'Reset the counter
count=0
' This wil determine how many rows are in the csv
for tablea = 0 to (total_num_imported/ (num_imported)-1)
%>
<%
for table = 0 to num_imported -1
'This will create a table cell for each column in the csv
' (-1 is used because arrays begin with 0)
%><td width="<%= 100/(num_imported) %>%">
<%= total_split_text(count)
%>
</td>
<%
count=count+1
next ' end of the observation
%></tr>
<% next 'end of the csv %>
</table>


<%@ Language=VBScript %>

<%
Option Explicit
%>

<%
' -----------------------------------------------------
' CDONTS Email send script
' © http://www.designplace.org/
' Comments must remain intact for re-use of this code
' -----------------------------------------------------

dim strName, strEmailOne, strEmailTwo, strMessage, optSuggestions, strEmailTo

strName = Request.Form("name") ' holds inputted name
'strEmailOne = Request.Form("emailOne") ' holds inputted email address
strEmailTwo = Request.Form("emailTwo") ' holds inputted email address
strMessage = Request.Form("message") ' holds inputted message
optSuggestions = Request.Form("suggestions").Item ' drop down list selection

' -- check all fields for empty values --
' -- remove and add new as required --

if strMessage = "" then
Response.Redirect "suggestion_box.asp?action=err4"
end if

' if strName = "" then
' Response.Redirect "suggestion_box.asp?action=err1"
' else if strEmailOne = "" then
' Response.Redirect "suggestion_box.asp?action=err2"
' else if strEmailTwo = "" then
'Response.Redirect "suggestion_box.asp?action=err3"
'else if strMessage = "" then
'Response.Redirect "suggestion_box.asp?action=err4"
'else if optSuggestions = "" then
'Response.Redirect "suggestion_box.asp?action=err5"
'end if
'end if
'end if
'end if
'end if

' -- start determine correct send to address based on suggestion type --

Select Case optSuggestions

Case "Web Enhancements"
strEmailTo = "person5@optIT.com"

Case "Application Enhancements"
strEmailTo = "person1@optIT.com;person2@optIT.com"

Case "New Product Suggestions"
strEmailTo = "person1@optIT.com;person2@optIT.com"

Case "Workplace Suggestions"
strEmailTo = "person1@optIT.com;person2@optIT.com"

Case "Employee Recognition"
strEmailTo = "person3@optIT.com;person4@optIT.com"

Case "Cultural Events"
strEmailTo = "person3@optIT.com;person4@optIT.com"

Case "Other"
strEmailTo = "person3@optIT;person4@optIT.com"

Case Else
strEmailTo = "feedback@optIT.com"

End Select

' -- end determine correct send to address based on suggestion type --

' -- begin email send process --

dim objMail

Set objMail = Server.CreateObject("CDO.Message")
'Set objMail = CreateObject("CDONTS.NewMail")

' -- email variables --
objMail.From = Trim(""person3@optIT.com;person4@optIT.com")
objMail.To = Trim(strEmailTo)
objMail.Subject = "Educators Program"
'objMail.BodyFormat = "0" ' HTML format
objMail.TextBody = "Your Discount Code from Educators Program:" & Trim(dsctCodes) & vbCrLf _
& vbCrLf _
& "Question1: " & Trim(optSuggestions) & vbCrLf _
& vbCrLf _
& "Message: " & Trim(strMessage)

' -- send the email --
objMail.Send()

' -- clean up object
Set objMail = Nothing

' -- execute confirmation page
Response.Redirect "thankyou.asp"
%>
AddThis Social Bookmarking Widget
Reply With Quote

  • 3 Contributors
  • 3 Replies
  • 2K Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by chunder
Member Avatar for hielo

What exactly are you trying to do. The email code does not make sense. The only bug on your code that imports the csv information is that it never writes the last line of the csv file to the browser. You need to change:

total_num_imported=ubound(total_split_text)
to:
total_num_imported=ubound(total_split_text)+1

Here is how I would do it:

<%@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="states.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>"
    if counter > 1 Then
		for i=0 to ubound(line)
			Response.Write "<td>"&line(i)&"</td>"
		next
    else
		for i=0 to ubound(line)
			Response.Write "<th>"&line(i)&"</th>"
		next
    end if
    Response.Write "</tr>" & chr(13)
Loop
objFile.Close
%><caption>Total Number of Records: <%=counter-1%></caption>
</table>
Member Avatar for hielo

Here is shorter alternative:

<%@language="vbscript"%>
<table border="1">
<%
dim csv_to_read, fso, act

csv_to_read="states.csv"
set fso = createobject("scripting.filesystemobject")
set act = fso.opentextfile(server.mappath(csv_to_read))

'Read the first line of the csv, typically these are colum headings
Response.Write "<tr><th>" & replace(act.readline,",","</th><th>") & "</th></tr>" & vbCrLf

'Read the rest of the csv
Response.Write "<tr><td>" & replace(replace(act.readall,vbCrLf,"</td></tr>"&vbCrLf&"<tr><td>"),",","</td><td>") & "</td></tr>"
%>
<caption>Total Number of Records: <%=act.Line-1%></caption>
</table>

BTW, my states.csv looks as follows:

name,abbreviation
Illinois,IL
Indiana,IN
Ohio,OH
Member Avatar for chunder

I am using the last peice of code and it is working great but was wondering if there was a way to have it use a comma or quotes as the seperator. Some of the line items in my csv have a comma but it is not the seperator.

Any ideas?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.