OK, you can stop pulling your hair out... at least for now. Depending on where that
.csv file is located, you may pull out a few more hairs. If it's located in a relative path to the web site, on the web server, OR if it is located anywhere on the webserver and you know the physical path, your problems are over !!
I've provided content for two files. The first is an ASP file that I want you to save as
test.asp. The second is the content for the .csv file. I want you to copy that into Notepad and save that file as
test.csv. For the purpose of this test, you need to place both of those files in the same folder on your web server.
test.asp
Code:
<%@ Language=VBScript %>
<% Option Explicit %>
<% Response.Buffer = True %>
<%
dim i
dim objFileSys
dim theFile
dim lineTxt
set objFileSys = Server.CreateObject("Scripting.FileSystemObject")
set theFile = objFileSys.OpenTextFile(Server.MapPath("./") & "/test.csv",1)
%>
<html>
<head></head>
<body>
<table border="1" cellpadding="3" cellspacing="0">
<%
IF NOT theFile.AtEndOfStream THEN
%>
<tr>
<%
lineTxt = Split(theFile.ReadLine, ",")
FOR EACH i IN lineTxt
%>
<td bgcolor="#9999FF"><strong><%=i%></strong></td>
<%
NEXT
%>
</tr>
<%
WHILE NOT theFile.AtEndOfStream
lineTxt = Split(theFile.ReadLine, ",")
%>
<tr>
<%
FOR EACH i IN lineTxt
%>
<td><%=i%></td>
<%
NEXT
%>
</tr>
<%
WEND
END IF
theFile.Close
%>
</table>
</body>
</html>
test.csv
Code:
firstname,lastname,age,occupation,sex
todd,jones,31,programmer,male
jim,jackson,45,teacher,male
jane,smith,18,student,female
Notes about this test.
#1: The test.asp document is designed so that it will parse any
Comma Seperated Value file, but it will only parse that type of file.
#2: The
test.asp file assumes that the first line of the
.csv file contains the column headings.
#3: There is no validation to make sure that the file exists, so if it does not exist, ASP will return an error.
#4: Conventions used in this solution.
The ASP is using the
FileSystemObject to open and read the
.csv file line by line. The file is opened with this line of code.
set theFile = objFileSys.OpenTextFile(Server.MapPath("./"
& "/test.csv",1)
The text highlighted in red specifies the location and name of the file. If you know the physical location of the file you could replace this with
"C:/SomeFolder/test.csv". The
1 parameter tells FSO to open the file as ready only. You must do this or FSO may overwrite and erase the current file.
When the file is opened, the pointer is initially placed on line #1. This line of code makes sure that there is at least one line in the file to continue processing.
IF NOT theFile.AtEndOfStream THEN
This line of code creates an array for the first line in the text file. It is this convention that is being used that mandates that a .csv file will only work here. The
ReadLine method reads the line and automatically moves the pointer to the next line. So there is no nead to move the pointer with another line of code.
lineTxt = Split(theFile.ReadLine, ","
This block of code loops through the array and creates a table cell for each item in the array. Note that we've assigned a background color and bold text because this is the heading of the table.
FOR EACH i IN lineTxt
%>
<td bgcolor="#9999FF"><strong><%=i%></strong></td>
<%
NEXT
This next block of code has two loops in it. The
WEND loop executes as long as the file pointer is not at the end of the file, thus creating a table row for each line in the file. The
FOR loop does exactly what was explained above. It loops through the array and creates a table cell for each item in the line.
<%
WHILE NOT theFile.AtEndOfStream
lineTxt = Split(theFile.ReadLine, ","
%>
<tr>
<%
FOR EACH i IN lineTxt
%>
<td><%=i%></td>
<%
NEXT
%>
</tr>
<%
WEND
The rest is pretty self explanatory. Go ahead and copy / create these two test files and run them on your server.
#5: The ASP code will parse any
.csv document of any length or width. You might need to do some of your own formatting on the table width, borders, font color and size, etc.. to get your desired results.
#6: View the source of the rendered page to see how ASP dynamically created the HTML.
Please let me know if you have any questions.
ToddWW
