Display information from CSV file on website

[Richo1980]

Hi everyone,

I am trying to display information from a csv file on a website. This is what I have so far...(I know this is wrong, but I've only extracted info from Access databases before using SQL )

<%
set conn=Server.CreateObject("ADODB.Connection")
Conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="& strPath &";Extensions=asc,csv,tab,txt;Persist Security Info=False"
strFile = "HD_Closed.csv"
strPath = "\\server\c$\inetpub\[URL unfurl="true"]wwwroot\Stats\Stats\db2"[/URL]

'set rs = Server.CreateObject("ADODB.recordset")
rs.Open "select HD_Name, Total FROM "& strFile &""
%>

<table align="center" border="1" size="100%">
    <tr>
        <td class="hnav">
            <b>Name</b>
        </td>
        <td class="hnav">
            <b>Total</b>
        
    </tr>    
<%for each x in rs.Fields
    next%>
</tr>
<%do until rs.EOF%>
    <tr>
    <%for each x in rs.Fields%>
       <td><%Response.Write(x.value)%></td>
    <%next
    rs.MoveNext%>
    </tr>
<%loop
rs.close
conn.close
%>
[code]

Another member suggested that I google "scripting.filesystemobject" which I have and am not too sure about. Would anyone be able to give me an example using the above code of how I use this object?

Thanks
Samantha

 

[Richo1980]

I've found that I need to an array and am using the following code

<%
csv_to_read="HD_Closed.csv"
set fso = createobject("scripting.filesystemobject")
set act = fso.opentextfile("C:\inetpub\[URL unfurl="true"]wwwroot\Stats\Stats\db2\HD_Closed.csv"(HD_Closed))[/URL]

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 will 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>

I know that i have made a mistake in the set act = fso.opentextfile line as I keep getting the error Microsoft VBScript runtime error '800a000d'
Type mismatch: 'fso'

Anyone able to advise?

Thanks

 

[xwb]

Use Server.MapPath("HD_Closed.csv") instead of specifying the full path name. You never know where the system actually looks for the file. There is also a spurious (HD_closed) at the end of line.

 

[xwb]

You can also do it with ADODB but you need something called a csv DSN for ODBC. I don't have one to hand but from what I remember, you include it in the ASP instead of setting it up in ODBC and thereafter you can use it.

Have a look at

http://www.4guysfromrolla.com/webtech/030899-1.shtml

It is a very simple example that works.

 

[damber]

assuming that is all the code you have on that page, then type this at the top:

<% Option Explicit %>

you might then see the problem with the initial code you posted.




A smile is worth a thousand kind words. So smile, it's easy!

 

[Tarwn]

Actually, since none of the variables are declared Option Explicit might land him in the same boat after he adds all his variable declarations to the page. I would still add it and the variable declarations because it will help catch any variable mispellings, etc as well as make execution of the page more efficient.

After you add that, take a closer look at the contents of what are currently lines 2 + 4. More specifically look at the fact that your using a variable on line 2 that you don't assign a value to until line 4.

 

[damber]

Tarwn,

because it will help catch any variable mispellings, etc as well as make execution of the page more efficient .... More specifically look at the fact that your using a variable on line 2 that you don't assign a value to until line 4.

that's my point - turn it on and it will throw an error with that line - that will identify what to look at and hopefully the poster would READ their code...

they may also notice that the Dbq would only contain the path, and also omit the file... but it's nice for them to find these things themselves... ;-)



A smile is worth a thousand kind words. So smile, it's easy!

 

[Tarwn]

Yeah, I knew what you meant, but it would have been too easy to turn it on and Dim everything on line 1 to clear up all the declaration errors, and then he would still be in the same boat

And actually, when connecting to a text file, XLS file, etc I am pretty sure you only put the directory path in the connection string, then in the SQL statement you use the filename. Works the same way as when trying to manipulate Foxpro files.

So there. ;-)

 

[Tarwn]

Whoops, my bad. change the "XLS" to "CSV" in my previous post, I was thinking about two things at once. Connecting to an XLS file is differant than a delimited text file.

 

[damber]

So there.

oooh.. you wait.. your insolence has been noted...!!

You are right of course... it's been a long while since I used the text driver, so my memory didn't serve me very well this time.. (well, that's my excuse anyway.. ;-))



A smile is worth a thousand kind words. So smile, it's easy!