How do I change the output file from xls to CSV?

Steynsk
Steynsk used Ask the Experts™
on
Hi Experts,

We've got this well working script (see below) that produces an XLS file. But our results  exeed the limitations of the XLS format. That's wy I'm trying to write my output to CSV.

Can this be done by changing our current code or do I need complete other methode to make this work? And what sould be changed to get this working?

All help welcome

-----current code-----------------
<!--#INCLUDE FILE="connect.asp"-->
<%  
Server.ScriptTimeout = 600

Response.Buffer=true

'Declare some variables
dim RS, pid
Set RS = Server.CreateObject("ADODB.Recordset")
pid = Request.QueryString("pid")
sql = "SELECT * FROM mytable WHERE id= " & pid & " order by datum"
RS.Open sql, CS  
'This is the the code which tells the page to open Excel and give it the data to display
Response.ContentType = "application/vnd.ms-excel"
'You can give the spreadsheet a name at the point its produced
Response.AddHeader "Content-Disposition", "attachment; filename="& pid&".xls"  

loopCounter = 0

%>
 
<!--  
Note that I have formatted the output header here to a dark blue background and white text
this will be reflected in the spreadsheet when its produced and you could extend this to your own tastes of course.
-->
 
<table border="1" width="100%">
<tr>
    <th  bgcolor="#FFFFFF"><font size="2">patientnummer</font></th>
    <th  bgcolor="#FFFFFF"><font size="2">datum/tijd</font></th>
    <th  bgcolor="#FFFFFF"><font size="2">bed</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">HF</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">RESP</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">SpO2</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">SpO2r</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">SpO2l</font></th>
    <th  bgcolor="#FFFFFF"><font size="2">tcpCO2</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">tcpO2</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">etCO2</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">NIBDs</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">NIBDm</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">NIBDd</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">ABPs</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">ABPm</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">ABPd</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">rSO2_1</font></th>
    <th  bgcolor="#FFFFFF"><font size="2">rSO2_2</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">rSO2_3</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">rSO2_4</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">Pols</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">dSPO2</font></th>
      <th  bgcolor="#FFFFFF"><font size="2">awRF</font></th>      
</tr>
<%Do While Not RS.eof%>
<tr>
            <td><font size="2"><%= RS("PID") %></font></td>
            <td><font size="2"><%= RS("datum") %></font></td>
            <td><font size="2"><%= RS("bed") %></font></td>
            <td><font size="2"><%= RS("HF") %></font></td>
            <td><font size="2"><%= RS("RESP") %></font></td>
            <td><font size="2"><%= RS("SpO2") %></font></td>
            <td><font size="2"><%= RS("SpO2r") %></font></td>
            <td><font size="2"><%= RS("SpO2l") %></font></td>
            <td><font size="2"><%= RS("tcpCO2") %></font></td>
            <td><font size="2"><%= RS("tcpO2") %></font></td>
            <td><font size="2"><%= RS("etCO2") %></font></td>
            <td><font size="2"><%= RS("NIBDs") %></font></td>
            <td><font size="2"><%= RS("NIBDm") %></font></td>
            <td><font size="2"><%= RS("NIBDd") %></font></td>
            <td><font size="2"><%= RS("ABPs") %></font></td>
            <td><font size="2"><%= RS("ABPm") %></font></td>
            <td><font size="2"><%= RS("ABPd") %></font></td>
            <td><font size="2"><%= RS("rSO2_1") %></font></td>
            <td><font size="2"><%= RS("rSO2_2") %></font></td>
            <td><font size="2"><%= RS("rSO2_3") %></font></td>
            <td><font size="2"><%= RS("rSO2_4") %></font></td>
            <td><font size="2"><%= RS("Pols") %></font></td>
            <td><font size="2"><%= RS("dSPO2") %></font></td>
            <td><font size="2"><%= RS("awRF") %></font></td>
</tr>
<%

loopCounter = loopCounter + 1

if loopCounter = 1000 then
      Response.Flush
      loopCounter = 0      

end if

RS.Movenext
Loop
%>
</table>
<%
RS.Close
CS.Close
 
Set CS = Nothing
Set RS = Nothing
%>

---------------------end of code--------------------
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Your CSV file is automatically open by excel ? please try this :

Response.ContentType = "application/csv"
Response.AddHeader("Content-Disposition", "filename="& pid&".csv;")
You should use Response.ContentType = "text/csv" and you shouldn't use <th>/<td> tags, as a csv file uses just data and a separator character.

Modify your code to throw your raw data split by a external character (",", ";", ":", "|", and tabulation are some of the most used on csv files).
Some considerations about contentype:

Examples for a 436M CSV file

Varying performance depending on the Content-Type header:

Upload the CSV as text/csv it takes ~36s,
application/octet-stream it takes ~13s.  

when uploaded as text/csv the process uses 100% CPU time on one of my cores, but as application/octet-stream it maxes out around 70%.
Top Expert 2011
Commented:
I would handle this differently.

I would create another file that has the option to export to excel, .csv file, .txt file, even xml file.

Then on the file you posted above, I would add a few links like:

Download Report:
[&nbsp;
<a href="export.asp?fm=1">Excel</a>&nbsp;|&nbsp;
<a href="export.asp?fm=2">CSV</a>&nbsp;|&nbsp;
<a href="export.asp?fm=3">XML</a>&nbsp;|&nbsp;
<a href="export.asp?fm=4">Text</a>&nbsp;

Open in new window

                             ]

Then on export.asp file, you would do something like this:

	strSQL = "SELECT * FROM mytable WHERE id= " & pid & " order by datum"
	Dim objRS, i,j
	Set objRS = objConn.Execute(strSQL)
	If objRS.EOF Then
		Response.Write "No records returned"
		Response.End
	End if
	Dim aryData, aryHeaders
	ReDim aryHeaders(objRS.Fields.Count)
	Dim intRecFirst, intRecLast
	Dim intFieldFirst, intFieldLast, intRecordCount

	For i=0 To objRS.Fields.Count - 1
	    aryHeaders(i) = objRS.Fields(i).Name
	Next
	aryData = objRS.GetRows

	'empty recordset
	objRS.Close
	Set objRS = Nothing

	'Find the Upper & Lower ends of the Array
	intRecFirst   = LBound(aryData, 2)
	intRecLast    = UBound(aryData, 2)
	intFieldFirst = LBound(aryData, 1)
	intFieldLast  = UBound(aryData, 1)
	intRecordCount = UBound(aryData, 2) + 1

    Dim fileName, format, contentType
    format = Cint(request("fm"))

    'set your variables and write data
    select case format
		case 1:PrintExcel'EXCEL
		case 2:PrintCSV 'CSV
		case 3:PrintXML'XML
		case else: PrintText 'text
    end select

	Response.End()

	Sub SetHeaders(fileName, contentType)
		'add your headers
		Response.ContentType = contentType
		Response.AddHeader "Content-Disposition", "attachment; filename=" & fileName
	End Sub

	Sub PrintExcel

		SetHeaders strReportName & ".xls", "Application/vnd.excel"

		Response.Write "<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">"
		Response.Write "<head>"
		Response.Write "<!--[if gte mso 9]><xml>"
		Response.Write "<x:ExcelWorkbook>"
		Response.Write "<x:ExcelWorksheets>"
		Response.Write "<x:ExcelWorksheet>"
		Response.Write "<x:Name>"& strTable &" Report</x:Name>"
		Response.Write "<x:WorksheetOptions>"
		Response.Write "<x:Print>"
		Response.Write "<x:ValidPrinterInfo/>"
		Response.Write "</x:Print>"
		Response.Write "</x:WorksheetOptions>"
		Response.Write "</x:ExcelWorksheet>"
		Response.Write "</x:ExcelWorksheets>"
		Response.Write "</x:ExcelWorkbook>"
		Response.Write "</xml>"
		Response.Write "<![endif]--> "
		Response.Write "</head>"
		Response.Write "<body>"

		PrintTable "<td>","</td>","<tr>","</tr>","<table>","</table>"

		Response.Write "</body>"
		Response.Write "</html>"
	End Sub

	Sub PrintCSV
		SetHeaders strReportName & ".csv","text/csv"
		PrintTable Chr(34),Chr(34) & Chr(44),"",Chr(10),"",""
	End Sub

	Sub PrintText
		SetHeaders strReportName & ".txt","text/txt"
		PrintTable "",vbTab,"",vbCrlf,"",""
	End Sub

	Sub PrintXML
		SetHeaders strReportName & ".xml","text/xml"

		'Declare local variables.
		Dim objDom
		Dim objRoot
		Dim objField
		Dim objFieldValue
		Dim objcolName
		Dim objattTabOrder
		Dim objPI
		Dim x
		Dim objRow

		'Instantiate the Microsoft XMLDOM.
		Set objDom = Server.CreateObject("Microsoft.XMLDOM")
		objDom.preserveWhiteSpace = True

		'Create your root element and append it to the XML document.
		 Set objRoot = objDom.createElement("root")
			objDom.appendChild objRoot

			'Iterate through each row in the Recordset
			For i = intRecFirst To intRecLast

				'Create a row-level node
				Set objRow = objDom.CreateElement("record")

				'loop thru all fields and display their values
				For j = intFieldFirst To intFieldLast

					'*** Create an element, "field". ***
					Set objField = objDom.createElement("field")

					'*** Append the name attribute to the field node ***
					Set objcolName = objDom.createAttribute("name")
					objcolName.Text = aryHeaders(j)
					objField.SetAttributeNode(objColName)
					'***************************************************

					'*** Create a new node, "value". ***
					Set objFieldValue = objDom.createElement("value")

				'Set the value of the value node equal to the value of the
				'current field object
				objFieldValue.Text = aryData(j,i)
				'************************************

				'*** Append the value node as a child of the field node. ***
				objField.appendChild objFieldValue
				'***********************************************************

				'*** Append the field node as a child of the row-level node. ***
				objRow.appendChild objField
				'***************************************************************
			Next

			'*** Append the row-level node to the root node ***
			objRoot.appendChild objRow
			'**************************************************

		Next

		'*** Add the <?xml version="1.0" ?> tag ***
		Set objPI = objDom.createProcessingInstruction("xml", "version='1.0'")

		'Append the processing instruction to the XML document.
		objDom.insertBefore objPI, objDom.childNodes(0)
		'************************************************

		'Write the XML contents as a string
		Response.Write(objDom.xml)

		'Clean up...
		Set objDom = Nothing
		Set objRoot = Nothing
		Set objField = Nothing
		Set objFieldValue = Nothing
		Set objcolName = Nothing
		Set objattTabOrder = Nothing
		Set objPI = Nothing
	End Sub

	Sub PrintTable(f1,f2,r1,r2,t1,t2)
		Response.Write t1

		'add column headers
		Response.Write r1
		For i=0 To UBound(aryHeaders)
		    Response.Write f1 & aryHeaders(i) & f2
		Next
		Response.Write r2

		'add rows
		For i = intRecFirst To intRecLast
		    Response.Write r1

		    'loop thru all fields and display their values
		    For j = intFieldFirst To intFieldLast
		        Response.Write f1 & aryData(j, i) & f2
		    Next

		    Response.Write r2
		Next

		'close
		Response.Write t2
	End Sub


%>

Open in new window


What this does is give you the options to export to any format of your choice.

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial