CSV file

[emuye]

Is it possible to generate a CSV file using ASP code.I was asked to transfer my out put to CSV file from ASP code.Is it possible to do this if so please help me.thank you for the help

 

[DreXor]

how or what is your input based off of to generate the CSV?

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[Tarwn]

You can generate a CSV file the same way you can generate a text file, CSV is just a formatted version of a text file. Basically open a TextStream object and write out comma-delimited columns, one set of columns per row. Or you can use the ADO objects to read/write a CSV file (Look for Microsoft Text Driver in sample connection strings).

-T

 

[korata]

here is my question: for example i have two field
SS# F_Name
11 fa
12 ki
14 la
this how it look like when i get the report form the oracle database with asp code.what I want here is that i would like to have the option of getting a CSV file by clicking a button.I am fairly new to asp and i am not sure this can be done in a very simple programming knowledge.I really appreciate if someone advise me how i can approach this problem. examples woudl be so helpful
thank you

 

[vbkris]

e.g:

strToWrite="11,fa"&vbcrlf
strToWrite=strToWrite&"12,ki"&vbcrlf
strToWrite=strToWrite&""14,la"&vbcrlf

set fso=server.createobject("scripting.filesystemobject")
set txt=fso.createtextfile(server.mappath("TheCsvFile"))

'write the text, forgot the command

set txt=nothing
set fso=nothing

Known is handfull, Unknown is worldfull

 

[korata]

If i do undersand your question well, Here is the whole story:I have an asp page that is connected to the database and i do get the result i want by doning that.However, my boss suggesed if i cold create some kind View CSV value button on the form and if the user want to get the CSV file that makes it easier for the user to transfer the output to the CSV file and also much easier to print. I do not know if there is a relationship b/n asp and CSV.As you can see i am fairly new to ASP and your detailed help will be appreciated

 

[vbkris]

sure, u get the string values from a query right? now lets have a CSV version link:
<a href="CSVversion.asp">CSV Version</a>

in CSVVersion.asp:
run the SAME query as above.
when u browse through the recordset build up the string, write the string to a CSV file (Comma Seperated Value file). send it to the use for download, then delete the file...

Known is handfull, Unknown is worldfull

 

[korata]

This how my code looks like and please help me where to make a modification inorder for the CSV file link to work.
I really appreciate your help




<%@ Language="VBScript" %>
<%
Dim oConn, testSQL, testRS
 
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open "DSN=ora02;UID=db2000genuse;PWD=db2"
 
if Request("Submit") = "Search" then
  testSQL = "SELECT * FROM db2000genuse.course " & _
            "WHERE cname LIKE '%" & Request("courselike") & "%' " & _
            "ORDER BY cname"
  Set testRS = oConn.execute(testSQL)
%>
<table border="1" align="center">
  <tr>
    <% for each oField in testRS.Fields %>
      <td><b><%= oField.Name %></b></td>
    <%next%>
  </tr>
  <%do while not testRS.EOF %>
  <tr>
    <% for each oField in testRS.Fields %>
      <td>
        <% If IsNull(oField) then
         response.write "&nbsp;"
           else
             response.write oField.Value
           end if%>
      </td>
    <%next
      testRS.movenext%>
   </tr>
   <%loop%>
</table>
<%    
 
else
%>
<form method="post" action="ASP-Oracle-fall2001-course-form-partialMatchList.asp">
<table>
 
    <td>Search for course names like</td>
    <td><input type="text" name="courselike"></td>
    <td><input type="Submit" value="Search" name="Submit"></td>
  </tr>
  <p> Enter any partial course name (example: Chem) </p>
</table>
</form>
<%
end if
 
oConn.Close
Set oConn = Nothing
set testRS = Nothing
%>

 

[korata]

Can someone walk me through how i could relate CSV file and ASP. I did post my code

 

[pkailas]

you could add this to you asp page

Response.ContentType = "application/vnd.ms-excel"

it will convert it to Excel for you.

 

[korata]

How could i create a link so that people can access it as a CSV file.you know how difficult it is to print an ASP out put.

 

[Turkbear]

Hi,
Why not have a view in Oracle that IS a CSV formatted dataset..

Use as you Sql statement

Create or replace view NewView
as Select field1||','||field2||','||etc 
FROM db2000genuse.course " & _
            "WHERE cname LIKE '%" & Request("courselike") & "%' " & _
            "ORDER BY cname"

Then have 1 button that executes this version and 1 that uses the original one.

 

[pkailas]

duplicate the page that displays it and add
Response.ContentType = "application/vnd.ms-excel" as the first line of your ASP code.

Then make a link on the page that leads you to this page. As long as the person has excel installed on their pc, they will be either prompted to download it or it will be displayed in Excel imbedded in their browser. Either way it will be simple for them to download the xls file which can be converted to csv very simply.

 

[Turkbear]

Hi,
OOPS...
Yo do not need to make it a view, the select statement is all you need..

 

[korata]

Can i do the same thing if i want the report to be displayed in pdf format.

 

[Genimuse]

No. Excel knows how to interpret html tables and/or csv data. Acrobat has no idea, it wants a portable document format (pdf) file.

 

[Tarwn]

If you really want to write a CSV file, you could simply:

Response.ContentType = "text/plain"
Response.AddHeader "Content-Disposition", "attachment;filename=test.csv"

Response.Write "a,b,c,d,e"
Response.Write "f,g,h,i,j"
Response.Write "k,l,m,n,o"
Response.Write "0,1,2,3,4"
Response.Write "etc,etc,etc,etc,etc"

Tada, instant CSV file...

-T

 

[dpav29]

I use MS Frontpage for publishing, so this is from that perspective. . .

1. Create a new page and go to HTML view.

2. Delete ALL code, even the <html> tags.

3. Paste this code on the page. This should be the ONLY code on the page:

<%
Dim DSN, RS, SQL, Conn

'This next line is your database connection line
DSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("/fpdb/db1.mdb")
'This next line is your SQL for the data you want to use
SQL = "SELECT * FROM Item"

set Conn=server.createobject("adodb.connection")
Conn.open DSN
Set RS = Conn.Execute (SQL)

Dim F, Head
For Each F In RS.Fields
Head = Head & ", " & F.Name
Next
Head = Mid(Head,3) & vbCrLf
Response.Addheader "Content-Disposition", "inline; filename=myExportName.csv"
Response.ContentType = "application/download"
Response.Write Head
Response.Write(CHR(034))
Response.Write RS.GetString(,,CHR(034)&","&CHR(034),CHR(034)&vbCrLf&CHR(034),"")
Response.Write(CHR(034))
'Response.Write RS.GetString(,,", ",vbCrLf,"")
Set RS = nothing
Conn.close
%>
4. Edit the applicable areas that are in bold. They will be specific to your web site, but most Windows servers should be able to accomidate the DSN. Try just changing the db filename and the SQL query to see if you have any luck.

5. Save the page as an ASP page.

6. Put a link (to this page) on any other page in your site.

7. Click link in browser to download CSV file.

This will delimit with , even if there are comma's in the data. . . .the flaw (which I haven't figured out) is if there are quotes in the field. . .messes up the file.

Good luck.

Dave