INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

export asp table to csv and xml file

export asp table to csv and xml file

(OP)
Greetings everyone!

I have been banging my head against the wall for a while now trying to figure out how to solve this particular problem.
I have an asp page that displays a record set in a table. I want to export that table to either a csv and xml format. I have benn reading post and blogs but can for the life of me not get it right. The table shows a list of routes flown by a pilot. Here is the original page with the table:

CODE -->

<% Option Explicit %>
<!DOCTYPE html 
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<!-- #BeginTemplate "../westwind.dwt" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />
<meta name="keywords" content="" />
<!-- #BeginEditable "doctitle" -->
<title>View Hub Slam</title>
<!-- #EndEditable -->
<link rel="stylesheet" type="text/css" href="../westwind.css" />
<!-- #BeginEditable "style" -->
<style type="text/css">
.even_row {
	background: lightgrey;
}
.alert1 {
	color: black;
	background: yellow;
}
.alert2 {
	color: white;
	background: red;
}
</style>
<script language="javascript" type="text/javascript"><!--//--><![CDATA[//><!--
sfHover = function() {
	var sfEls = document.getElementById("nav4").getElementsByTagName("LI");
	for (var i=0; i<sfEls.length; i++) {
		sfEls[i].onmouseover=function() {
			this.className+=" sfhover";
		}
		sfEls[i].onmouseout=function() {
			this.className=this.className.replace(new RegExp(" sfhover\\b"), "");
		}
	}
}
if (window.attachEvent) window.attachEvent("onload", sfHover);
//--><!]]></script>
<!-- #EndEditable -->
</head>

<body>
<!--#include virtual="/page_header.asp"-->
<!-- #BeginEditable "content" -->
<!--#include virtual="/Roster/check_roles.asp"-->
<!--#include virtual="/pagelinks.asp"-->
<%

Dim SlamIncomplete

SlamIncomplete = FALSE

Function DateString(vDateTime)
	DateString=Year(vDateTime)& "-" & _
		Month(vDateTime) & "-" & _
		Day(vDateTime) & " " & _
		Trim(FormatDateTime(vDateTime,vbShortTime)) & ":00"
End Function

Function AirportName(AirportICAO)

		Dim RSAirportName, cmdAirportName
    Set cmdAirportName=Server.CreateObject("ADODB.Command")
    Set RSAirportName=Server.CreateObject("ADODB.Recordset")

    cmdAirportName.ActiveConnection=objConn

    cmdAirportName.CommandType=1
    cmdAirportName.CommandText="SELECT * FROM tbl_AirportCodes WHERE AirportCode=?"
    cmdAirportName.Parameters.Append cmdAirportName.CreateParameter("AirportCode", 200, 1, 4, AirportICAO)
    cmdAirportName.ActiveConnection=objConn
    RSAirportName.Open cmdAirportName,,3

    AirportName=""

  	IF not (isnull(RSAirportName("Name")) OR LEN(RSAirportName("Name")) = 0) Then
     AirportName = AirportName & RSAirportName("Name") & ", "
  	END IF

    IF not (isnull(RSAirportName("City")) OR LEN(RSAirportName("City")) = 0) Then
  	  AirportName = AirportName & RSAirportName("City") & ", "
  	END IF

    IF not (isnull(RSAirportName("State")) OR LEN(RSAirportName("State")) = 0) Then
  	  AirportName = AirportName & RSAirportName("State") & ", "
  	END IF

    IF not (isnull(RSAirportName("Country")) OR LEN(RSAirportName("Country")) = 0) Then
  	  AirportName = AirportName & RSAirportName("Country") & " "
  	END IF
		
		AirportName=AirportName & "(" & RSAirportName("AirportCode") & ")"

    RSAirportName.Close
    Set cmdAirportName=Nothing

End Function

Function CheckRoute(From_ICAO, To_ICAO, Pilot, CircularRoute)
		Dim RSCheckRoute, cmdCheckRoute, strSQL

		IF CircularRoute = FALSE THEN
		
      Set cmdCheckRoute=Server.CreateObject("ADODB.Command")
      Set RSCheckRoute=Server.CreateObject("ADODB.Recordset")
  
      cmdCheckRoute.ActiveConnection=objConn
  
      cmdCheckRoute.CommandType=1
   
      cmdCheckRoute.CommandText="SELECT COUNT(*) as N FROM FlightLog WHERE PilotNumber=? AND Completed=1 AND Origin=? AND Destination=?"		
      cmdCheckRoute.Parameters.Append cmdCheckRoute.CreateParameter("PilotID", 200, 1, 7, PilotID)
      cmdCheckRoute.Parameters.Append cmdCheckRoute.CreateParameter("Origin", 200, 1, 4, From_ICAO)
      cmdCheckRoute.Parameters.Append cmdCheckRoute.CreateParameter("Destination", 200, 1, 4, To_ICAO)
  
      cmdCheckRoute.ActiveConnection=objConn
      RSCheckRoute.Open cmdCheckRoute,,3
  		
  		IF RSCheckRoute("N") > "0" THEN
  		  CheckRoute = "Flown"
  		ELSE
  		  CheckRoute = "Not Flown"
        SlamIncomplete = TRUE			
        LegsNotFlown = LegsNotFlown + 1
  		END IF
			
			Legs = Legs + 1

		ELSE
		
  		  CheckRoute = "N/A"

		END IF
				
END Function

' This page requires pilot login
IF Session("PilotAuthenticated") = False Then
	Set RS=Nothing
	objConn.Close
	Set objConn=Nothing
	Response.Redirect "/LogIn/pilot_login.asp?LOGIN=REQUIRED&URL=" & Request.ServerVariables("URL")
End If

Dim PilotID, strPage, strLeg, Rows
Dim strPilotName, strPilotHub, Even_row, LegsNotFlown, Legs

If Request.QueryString("PilotID")="" Then
	PilotID=Session("PilotNumber")
Else
	PilotID=UCase(Left(Trim(Request.Querystring("PilotID")),7))
End If

If Left(PilotID,3) <> "WWA" Or Not IsNumeric(Mid(PilotID,4)) Then
	Set RS=Nothing
	objConn.Close
	Set objConn=Nothing
	Response.Write "Invalid PilotID"
	Response.End
End If

' Get pilot name, rating, hub

Set cmd=Server.CreateObject("ADODB.Command")
cmd.ActiveConnection=objConn

cmd.CommandText="SELECT Name,Hub_ICAO AS Hub FROM Roster WHERE Number=?"
cmd.CommandType=1
	
cmd.Parameters.Append cmd.CreateParameter("PilotID", 200, 1, 7, PilotID)

RS.Open cmd
	
If RS.BOF And RS.EOF Then
	' Invalid pilot number
	Response.Write "<p>Invalid Pilot ID</p>"
	RS.Close
	Set RS=Nothing
	Set cmd=Nothing
	objConn.Close
	Set objConn=Nothing
End If
	
strPilotName=RS("Name")
strPilotHub=RS("Hub")

RS.Close


' Get all of the routes in the hub
	Set cmd=Server.CreateObject("ADODB.Command")
	cmd.ActiveConnection=objConn
  strSQL="SELECT * FROM RoutesPax WHERE Origin_ICAO = '" & strPilotHub & "' ORDER BY RouteID"
	cmd.CommandText=strSQL
	cmd.CommandType=1
	
	RS.Open cmd
	
	' Display All routes
%>
<div id="container">
<h1>Hub Routes</h1>
<h5><%=strPilotName%> - <%=strPilotHub%></h5>

<p><a href="download_hub_slam_csv.asp?PilotID=<%=PilotID%>">Download hub slam (CSV format)</a></p>

<table>
	<thead>
		<tr>
			<td>Route #</td>
			<td># of Legs</td>
			<td>Leg Details</td>
			<td>Outbound Flown</td>
			<td>Inbound Flown</td>
		</tr>
	</thead>
	<tbody>
	
  <%
	If Not (RS.BOF And RS.EOF) Then
	  Do While Not RS.EOF
    	%>
	
     	<tr <% If even_row=1 Then
  	           Response.Write "class=""even_row"""
  			  	 End If
						 
						 Rows = RS("Stops") + 1
    			%>
     	>
 			<td rowspan=<%=Rows%>><%=RS("RouteID")%></td>		
 			<td rowspan=<%=Rows%>><%=RS("Stops") + 1 %></td>		

	 		<%
			
      dim Code1, Code2
			
	 		IF RS("Stops") = 0 THEN			
				
				Code1 = RS("Origin_ICAO")
				Code2 = RS("Destination_ICAO")
			  strLeg = AirportName(Code1) & " to " & AirportName(Code2)
				
   		  %><td><%=strLeg%></td>
    		<td><%=CheckRoute(Code1, Code2, PilotID, FALSE)%></td>
    		<td><%=CheckRoute(Code2, Code1, PilotID, FALSE)%></td>
				<%
	 		ELSE
			
				dim iCount, CircularRoute
				
				CircularRoute = (RS("origin_ICAO") = RS("Destination_ICAO"))
				
				Code1 = RS("Origin_ICAO")
				Code2 = RS("Stop1_ICAO")
  		  strLeg = AirportName(Code1) & " to " & AirportName(Code2)
		
   		  %><td><%=strLeg%></td>  	
	  		<td><%=CheckRoute(Code1, Code2, PilotID, FALSE)%></td>
				
   	  	<td><%=CheckRoute(Code2, Code1, PilotID,CircularRoute)
				
				%></td>

				</tr>
        <%		    
				For iCount = 1 to RS("Stops") - 1	
	    		Code1 = RS("Stop" & iCount & "_ICAO")
	  			Code2 = RS("Stop" & iCount + 1 & "_ICAO")
      
				  strLeg = AirportName(Code1) & " to " & AirportName(Code2)

        	%>
					<tr 
					<%
					If even_row=1 Then
  	         Response.Write "class=""even_row"""
  			  End If
					%>
		      >
     		  <td><%=strLeg%></td>  	
  	  		<td><%=CheckRoute(Code1, Code2, PilotID, FALSE)%></td>
    	  	<td><%=CheckRoute(Code2, Code1, PilotID, CircularRoute)%></td>
  				</tr>
			  	<%
				Next 

				Code1 = RS("Stop" & RS("Stops") & "_ICAO")
				Code2 = RS("Destination_ICAO")
  		  strLeg = AirportName(Code1) & " to " & AirportName(Code2)
		
       	%><tr <%
				
				If even_row=1 Then
          Response.Write "class=""even_row"""
  		  End If
				
   		  %>
				>
				<td><%=strLeg%></td>  	
	  		<td><%=CheckRoute(Code1, Code2, PilotID, FALSE)%></td>
   	  	<td><%=CheckRoute(Code2, Code1, PilotID, CircularRoute)%></td>

				</tr>

				<%
				
			END IF
							
  		If even_row=0 Then
  			even_row=1
  		Else
  			even_row=0
  		End If
  		RS.MoveNext
  	Loop
  	RS.Close
  	Set cmd=Nothing
  END IF
%>		
	</tbody>
</table>

</div>

<%
IF SlamIncomplete THEN
%>

<p>Slam Incomplete (<%=LegsNotFlown %> of  <%=Legs %> legs not flown)</p>

<%
ELSE
%>
<p>Slam Complete</p>
<%
END IF
%>

</div>
<!-- #EndEditable -->
<!--#include virtual="/page_footer.asp"-->
</body>

<!-- #EndTemplate -->

</html> 

I know it's quite a mouthfull. So I have tried to export this to a csv file, and below is what I came up with. Problem is, that it shows everything in the csv file, meaning it shows all the page code as well as all the routes for the hub, not just the ones flown by a particular pilot.

CODE -->

<% Option Explicit %>
<!DOCTYPE html 
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<!-- #BeginTemplate "../westwind.dwt" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />
<meta name="keywords" content="" />
<!-- #BeginEditable "doctitle" -->
<title>Download Hub Slam CSV</title>
<!-- #EndEditable -->
<link rel="stylesheet" type="text/css" href="../westwind.css" />
<!-- #BeginEditable "style" -->
<script language="javascript" type="text/javascript"><!--    //--><![CDATA[//><!--
    sfHover = function () {
        var sfEls = document.getElementById("nav4").getElementsByTagName("LI");
        for (var i = 0; i < sfEls.length; i++) {
            sfEls[i].onmouseover = function () {
                this.className += " sfhover";
            }
            sfEls[i].onmouseout = function () {
                this.className = this.className.replace(new RegExp(" sfhover\\b"), "");
            }
        }
    }
    if (window.attachEvent) window.attachEvent("onload", sfHover);
    //--><!]]></script>
<!-- #EndEditable -->
</head>

<body>
<!--#include virtual="/page_header.asp"-->
<!-- #BeginEditable "content" -->

 <%
Function DateString(vDateTime)
	DateString=Year(vDateTime)& "-" & _
		Month(vDateTime) & "-" & _
		Day(vDateTime) & " " & _
		Trim(FormatDateTime(vDateTime,vbShortTime)) & ":00"
End Function

Function AirportName(AirportICAO)

		Dim RSAirportName, cmdAirportName
    Set cmdAirportName=Server.CreateObject("ADODB.Command")
    Set RSAirportName=Server.CreateObject("ADODB.Recordset")

    cmdAirportName.ActiveConnection=objConn

    cmdAirportName.CommandType=1
    cmdAirportName.CommandText="SELECT * FROM tbl_AirportCodes WHERE AirportCode=?"
    cmdAirportName.Parameters.Append cmdAirportName.CreateParameter("AirportCode", 200, 1, 4, AirportICAO)
    cmdAirportName.ActiveConnection=objConn
    RSAirportName.Open cmdAirportName,,3

    AirportName=""

  	IF not (isnull(RSAirportName("Name")) OR LEN(RSAirportName("Name")) = 0) Then
     AirportName = AirportName & RSAirportName("Name") & ", "
  	END IF

    IF not (isnull(RSAirportName("City")) OR LEN(RSAirportName("City")) = 0) Then
  	  AirportName = AirportName & RSAirportName("City") & ", "
  	END IF

    IF not (isnull(RSAirportName("State")) OR LEN(RSAirportName("State")) = 0) Then
  	  AirportName = AirportName & RSAirportName("State") & ", "
  	END IF

    IF not (isnull(RSAirportName("Country")) OR LEN(RSAirportName("Country")) = 0) Then
  	  AirportName = AirportName & RSAirportName("Country") & " "
  	END IF
		
		AirportName=AirportName & "(" & RSAirportName("AirportCode") & ")"

    RSAirportName.Close
    Set cmdAirportName=Nothing

End Function

Function CheckRoute(From_ICAO, To_ICAO, Pilot, CircularRoute)
		Dim RSCheckRoute, cmdCheckRoute, strSQL

		IF CircularRoute = FALSE THEN
		
      Set cmdCheckRoute=Server.CreateObject("ADODB.Command")
      Set RSCheckRoute=Server.CreateObject("ADODB.Recordset")
  
      cmdCheckRoute.ActiveConnection=objConn
  
      cmdCheckRoute.CommandType=1
   
      cmdCheckRoute.CommandText="SELECT COUNT(*) as N FROM FlightLog WHERE PilotNumber=? AND Completed=1 AND Origin=? AND Destination=?"		
      cmdCheckRoute.Parameters.Append cmdCheckRoute.CreateParameter("PilotID", 200, 1, 7, PilotID)
      cmdCheckRoute.Parameters.Append cmdCheckRoute.CreateParameter("Origin", 200, 1, 4, From_ICAO)
      cmdCheckRoute.Parameters.Append cmdCheckRoute.CreateParameter("Destination", 200, 1, 4, To_ICAO)
  
      cmdCheckRoute.ActiveConnection=objConn
      RSCheckRoute.Open cmdCheckRoute,,3
  		
  		IF RSCheckRoute("N") > "0" THEN
  		  CheckRoute = "Flown"
  		ELSE
  		  CheckRoute = "Not Flown"
        SlamIncomplete = TRUE			
        LegsNotFlown = LegsNotFlown + 1
  		END IF
			
			Legs = Legs + 1

		ELSE
		
  		  CheckRoute = "N/A"

		END IF
				
END Function

' This page requires pilot login
IF Session("PilotAuthenticated") = False Then
	Set RS=Nothing
	objConn.Close
	Set objConn=Nothing
	Response.Redirect "/LogIn/pilot_login.asp?LOGIN=REQUIRED&URL=" & Request.ServerVariables("URL")
End If

Dim PilotID, strPage, strLeg, Rows
Dim strPilotName, strPilotHub, Even_row, LegsNotFlown, Legs

If Request.QueryString("PilotID")="" Then
	PilotID=Session("PilotNumber")
Else
	PilotID=UCase(Left(Trim(Request.Querystring("PilotID")),7))
End If

If Left(PilotID,3) <> "WWA" Or Not IsNumeric(Mid(PilotID,4)) Then
	Set RS=Nothing
	objConn.Close
	Set objConn=Nothing
	Response.Write "Invalid PilotID"
	Response.End
End If

' Get pilot name, rating, hub

Set cmd=Server.CreateObject("ADODB.Command")
cmd.ActiveConnection=objConn

cmd.CommandText="SELECT Name,Hub_ICAO AS Hub FROM Roster WHERE Number=?"
cmd.CommandType=1
	
cmd.Parameters.Append cmd.CreateParameter("PilotID", 200, 1, 7, PilotID)

RS.Open cmd
	
If RS.BOF And RS.EOF Then
	' Invalid pilot number
	Response.Write "<p>Invalid Pilot ID</p>"
	RS.Close
	Set RS=Nothing
	Set cmd=Nothing
	objConn.Close
	Set objConn=Nothing
End If
	
strPilotName=RS("Name")
strPilotHub=RS("Hub")

RS.Close

' Path to file DSN
sDSNDir=Server.MapPath("\_dsn")

DIM RS1
Set RS1=Server.CreateObject("ADODB.RECORDSET")
RS1.Open "SELECT * FROM RoutesPax WHERE Origin_ICAO = '" & strPilotHub & "' ORDER BY RouteID", "filedsn=" & sDSNDir & "\" & "ww_mysql.dsn", 0, 1

Dim strFileName
strFileName=Trim(PilotID) & "_Hub_Slam.csv"

  Response.ContentType = "text/csv"

  Response.AddHeader "Content-Disposition", "attachment;filename=" & strFileName

  Write_CSV_From_Recordset RS1


sub Write_CSV_From_Recordset( RS )

    if RS.EOF then

      '
      ' There is no data to be written
      '
      exit sub

    end if

    dim RX
    set RX = new RegExp
        RX.Pattern = "\r|\n|,|"""

    dim i
    dim Field
    dim Separator

    '
    ' Writing the header row (header row contains field names)
    '

    Separator = ""
    for i = 0 to RS.Fields.Count - 1
      Field = RS.Fields( i ).Name
      if RX.Test( Field ) then
        '
        ' According to recommendations:
        ' - Fields that contain CR/LF, Comma or Double-quote should be enclosed in double-quotes
        ' - Double-quote itself must be escaped by preceeding with another double-quote
        '
        Field = """" & Replace( Field, """", """""" ) & """"
      end if
      Response.Write Separator & Field
      Separator = ","
    next
    Response.Write vbNewLine

    '
    ' Writing the data rows
    '

    do until RS.EOF
      Separator = ""
      for i = 0 to RS.Fields.Count - 1
        '
        ' Note the concatenation with empty string below
        ' This assures that NULL values are converted to empty string
        '
        Field = RS.Fields( i ).Value & ""
        if RX.Test( Field ) then
          Field = """" & Replace( Field, """", """""" ) & """"
        end if
        Response.Write Separator & Field
        Separator = ","
      next
      Response.Write vbNewLine
      RS.MoveNext
    loop

  end sub

%>

</body>
</html> 

Any and every assistance in helping me solve this would be greatly appreciated!

Cheers,

Chris

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close