Help with Excel CSV connection

[linuxjr]

I'm pulling my hair out. I have been trying to find some samples of how to use asp to print out the sheet that my csv is. I have tried using the text driver but I get errors going can't do that and so forth. Here is what I am trying to do.

I have a batch file that queries some information and saves it into an excel csv file. Now I have excel 97 so please don't go save as html that didn't come out until 2000. Anyway I want to use asp to just make a table looking like the csv file. Any help or links be greatly appreciated. Thanks and have a nice day.

 

[ToddWW]

Are you basically saying that you want to display a comma seperated value page in the browser window that will look identical to the existing csv file ??

ToddWW

 

[linuxjr]

Yes that is what I'm saying. Just to have a nice pretty table of the csv file that will be updated every day. Its just access for the whole department since there are not enough licenses to have office on each of the machines. So any tips or suggestions be appreciated.

 

[ToddWW]

Let me be more specific, and if I'm telling you anything you already know, I'm not trying to discourage you.

A csv file is a text file with Comma Separated Values. When you open that type of file in Excel, it will render those values in rows and columns for you. But the file itself looks like this.

firstname,lastname,age,occupation
Todd,Jones,35,programmer
Karen,Smith,28,doctor
Joe,Jackson,17,High School Student

If you open that csv file with Notepad or Wordpad, this is what you'll see. Knowing that, this is what ASP is going to have to work with.

Now my question is this, do you want ASP to render the page in the browser like THAT (example above) or do you want asp to render the page in a nice neat table in the browser window ??

If you can, can you send a copy of that csv page to me at sendpage@fuelaccess.com

I'd be happy to put an ASP solution together for you in the next day or so.

ToddWW

 

[linuxjr]

I apologize lack of sleep and tons of projects . I understand that its Comma Separated Values. The answer to your question is to render the page as a nice neat table in the browser window. I can't send the file for it has lots of cofidential information so if you don't mind you can use the example above to help me understand a solution. I would be appreciate the help with this asp. Thank you and have a nice day.

 

[ToddWW]

OK, then can you tell me if the .csv file we're working with shows the column headings (or field names) in the first line of the file.

Also, is this file located on the actual web server, or is it located on another computer on the network.

ToddWW

 

[linuxjr]

Forgive me (drinking coffee) mean like the first line

First Name, Last Name, SSN, Telephone??

Again I thank you for helping me out.

 

[ToddWW]

Yes, some .csv files will not list the column headings (aka field names) on the first line. Is your csv formatted like this.

firstname,lastname,age,occupation
Todd,Jones,35,programmer
Karen,Smith,28,doctor
Joe,Jackson,17,High School Student


Or without the column headings at the top like this.


Todd,Jones,35,programmer
Karen,Smith,28,doctor
Joe,Jackson,17,High School Student


ToddWW

 

[ToddWW]

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

<%@ Language=VBScript %>
<% Option Explicit %>
<% Response.Buffer = True %>
<%
dim i
dim objFileSys
dim theFile
dim lineTxt
set objFileSys = Server.CreateObject(&quot;Scripting.FileSystemObject&quot;)
set theFile = objFileSys.OpenTextFile(Server.MapPath(&quot;./&quot;) & &quot;/test.csv&quot;,1)
%>
<html>
<head></head>
<body>
  <table border=&quot;1&quot; cellpadding=&quot;3&quot; cellspacing=&quot;0&quot;>
<%
IF NOT theFile.AtEndOfStream THEN
%>
    <tr>
<%    
  lineTxt = Split(theFile.ReadLine, &quot;,&quot;)
  FOR EACH i IN lineTxt  
%>
      <td bgcolor=&quot;#9999FF&quot;><strong><%=i%></strong></td>
<%
  NEXT
%>
    </tr>
<%
  WHILE NOT theFile.AtEndOfStream
    lineTxt = Split(theFile.ReadLine, &quot;,&quot;)
%>
    <tr>
<%
    FOR EACH i IN lineTxt
%>
      <td><%=i%></td>
<%
    NEXT
%>
    </tr>
<%
  WEND
END IF
theFile.Close
%>
</table>
</body>
</html>

test.csv

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("./&quot & "/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, ",&quot

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=&quot;#9999FF&quot;><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, &quot;,&quot
%>
<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

 

[ToddWW]

Ummm.. Sorry !! When I suggested that you could specify a physical path, I mean to use backslashes.. Like this.


&quot;C:\SomeFolder\test.csv&quot;


Crazy !! X-) s-)

ToddWW

 

[linuxjr]

Thanks a million!!!!! Just got to tell norton on my machine its ok for this script to run . I appreciate it and it was clear and understandable. I will make the fixes for work and they will be happy. Thanks again and now I can rest easy with this one project out of the way . Have a great night and taking it easy on this strung out fella.