title: Export csv based on specific column
body: <p>Export column data as CSV when a column in HTML is selected. This HTML is  generated by the following <code>sms.asp</code> page.</p>
  
  <pre><code> &lt;%Option Explicit%&gt;
  &lt;%
  Dim strConn, strScriptName
  
  strScriptName = Request.ServerVariables("Script_Name")
  ' RESPONSE.WRITE strScriptName
  strConn = Application("eDSNSMS")
  
  Dim strSQL, boolQuery
  strSQL=""
  
  strSQL = Request.QueryString("SQL")
  boolQuery = Request.QueryString("boolQuery") 'apa ini
  
  Dim objRS, objConn
  set objConn = server.CreateObject("ADODB.Connection")
  set objRS = server.CreateObject("ADODB.RecordSet")
  %&gt;
  &lt;html&gt;
  &lt;head&gt;
      &lt;title&gt;DB Admin&lt;/title&gt;
      &lt;style type="text/css"&gt;
          .button{
              width:70px;
              }
      &lt;/style&gt;
      &lt;script language="Javascript"&gt;
      function Submit_Query(){
          var SQL = SQL_Form.SQL.value;
          if (SQL=="") return false;
          window.location="&lt;%=strScriptName%&gt;?boolQuery=True&amp;SQL=" + URLEncoding(SQL);
      }
      function Submit_Execute(){
          var SQL = SQL_Form.SQL.value;
          if (SQL=="") return false;
          if (confirm("Are you sure export to csv file?")){
              window.location="csv.asp?SQL=" + SQL;
          }
      }
  
      function URLEncoding(inputValue)
      {
      inputValue=escape(inputValue);
      re = /\+/gi;
      return inputValue.replace(re, "%2B");
      }
  
      var selectedIndex=0;
      function selectedValue(i){
          if (selectedIndex==i){
              document.getElementById("Row_" + selectedIndex).bgColor="#e0e0e0";
              document.getElementById("Row_" + selectedIndex).style.color="black";
              selectedIndex=0;
              return;
          }
          else{
              if (selectedIndex!=0){
                  document.getElementById("Row_" + selectedIndex).bgColor="#e0e0e0";
                  document.getElementById("Row_" + selectedIndex).style.color="black";
              }
              selectedIndex=i;
              document.getElementById("Row_" + selectedIndex).bgColor="darkblue";
              document.getElementById("Row_" + selectedIndex).style.color="white";
  
          }
      }
      &lt;/script&gt;
  &lt;/head&gt;
  &lt;body topmargin=0 leftmargin=0 rightmargin=0&gt;
  &lt;table width=100% border=0 cellpadding=2 cellspacing=0 bgColor="silver"&gt;
  &lt;tr style="font-weight:bold;font-size:16pt"&gt;&lt;td&gt;Database Administration&lt;/td&gt;&lt;/tr&gt;
  &lt;/table&gt;
  &lt;%
  On Error Resume Next
  
  objConn.Open strConn
  
  objConn.BeginTrans 'apa ini
  MainProgram
  
  
  If Err.Number = 0 Then  
      objConn.CommitTrans 
  Else
      objConn.RollbackTrans 
      Response.Write "&amp;nbsp;&amp;nbsp;" &amp; Err.Description
  End If
  
  %&gt;
  &lt;/body&gt;
  &lt;/html&gt;
  &lt;%
  
  
  If Not objRS Is Nothing Then
      If objRS.State=1 Then 
          objRS.close
      End If
      Set objRS=Nothing
  End If
  
  
  If Not objConn Is Nothing Then
      If objConn.State=1 Then
          objConn.close
      End If
      Set objConn=Nothing
  End If
  
  
   Private Sub MainProgram ()
      &lt;form name="SQL_Form" onSubmit="return false;"&gt;
          &lt;table&gt;
              &lt;tr&gt;
                  &lt;td style="font-weight:bold"&gt;SQL Statement: &lt;/td&gt;
              &lt;/tr&gt;
              &lt;tr &gt;
                  &lt;td&gt;&lt;textarea rows=5 cols=70 name="SQL"&gt;&lt;%=Server.HTMLEncode(strSQL)%&gt;&lt;/textarea&gt;&lt;br&gt;
                  &lt;input class="button" type=button value="Query" onClick="Submit_Query();"&gt;&amp;nbsp;
                  &lt;input class="button" type=button value="Export"  onClick="Submit_Execute();"&gt;&amp;nbsp;
                  &lt;input type=button style="cursor:hand;width:75px" value="Cancel" onClick="window.location='ConsultantMenu.asp'"&gt;&lt;/td&gt;
              &lt;tr&gt;
          &lt;/table&gt;
      &lt;/form&gt;
      &lt;%
          If strSQL="" Then
              Exit Sub
          End If
  
  
          If boolQuery = "True" Then
              Dim intTotalRecord, intTotalFields, intRow
              Dim x,i
              objRS.CursorLocation = 3  'adUseClient 
              objRS.CursorType = 3      'adOpenStatic
              objRS.LockType = 1         'adLockReadOnly 
              objRS.Open strSQL, objConn
              Set objRS.ActiveConnection = Nothing
  
  
              intTotalRecord=objRS.RecordCount
  
              If intTotalRecord=0 Then
                  Response.Write "&amp;nbsp;&amp;nbsp;No Record Found."
              Else
  
                  Response.Write "&amp;nbsp;&amp;nbsp;" &amp; intTotalRecord &amp; " Record(s) Found."
                  Response.Write "&lt;br&gt;&lt;table border=0 cellpadding=1 cellspacing=1 style=""font-size:10pt""&gt;"
  
                  intTotalFields = objRS.Fields.Count - 1 
  
                  Response.Write "&lt;tr&gt;"
                  For Each x in objRS.Fields
                     Response.Write "&lt;td class=""outrim""&gt;" &amp; x.Name &amp; "&lt;/td&gt;"
                  Next
                  Response.Write "&lt;/tr&gt;"
  
                  objRS.MoveFirst
                  intRow=1
                  Do Until objRS.EOF
  
                      Response.Write "&lt;tr class=""Row_Class"" id=""Row_" &amp; intRow &amp; """ bgColor=""#e0e0e0"" onclick=""selectedValue(" &amp; intRow &amp; ")""&gt;"
                      For i=0 to intTotalFields
                          Response.Write "&lt;td nowrap&gt;" &amp; objRS(i) &amp; "&amp;nbsp;&amp;nbsp;&lt;/td&gt;"
                      Next
                      Response.Write "&lt;/tr&gt;"
  
                      intRow=intRow+1
  
                      objRS.MoveNext
                  Loop
  
                  Response.Write "&lt;/table&gt;"
              End If
  
          Else
              Dim intTotalAffectRecord
              objConn.Execute strSQL, intTotalAffectRecord
              Response.Write "&amp;nbsp;&amp;nbsp;" &amp; intTotalAffectRecord &amp; " Record(s) affected."
          End If
  
  
  End Sub
  
  %&gt;
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/ZFNZj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZFNZj.png" alt="enter image description here"></a></p>
  
  <p>I wanted to let user export their select query to csv files when they click export button, so i create a page call <code>csv.asp</code>. But now i just wanted if the user select to mtel column then only the if else statement (mark as 'start here' as comment) work. How can i do this?</p>
  
  <pre><code>Dim strConn, strScriptName,strSQL
  
  strConn = Application("eDSNSMS")
  
  strSQL = Request.querystring("SQL")
  
  sub Write_CSV_From_Recordset(RS)
    if RS.EOF then
          exit sub
      end if
  
      dim RX
      set RX = new RegExp
          RX.Pattern = "\r|\n|,|"""
  
      dim i
      dim Field
      dim Separator,strRow
  
      do until RS.EOF
          Separator = ""
          for i = 0 to RS.Fields.Count - 1
              Field = RS.Fields(i).Value &amp; ""
              if RX.Test(Field) then
                  Field = """" &amp; Replace(Field, """", """""") &amp; """"
              end if
  
              'start here
              If Left(Field, 2) = "01" and  InStr(Field, "-") &lt;&gt; 0 Then
                  if Len(Field) = 11 Then
                   Field = "6" &amp; Field
                   Field = """" &amp; Replace(Field, "-", "") &amp; """"
                  else
                   Field = ""
                  end if
              elseif Left(Field, 2) = "01"  and  InStr(Field, "-") = 0 then
                  if Len(Field) = 10 Then
                   Field = "6" &amp; Field
                  else
                   Field = ""
                  end if
              elseif Left(Field, 3) = "011"  and  InStr(Field, "-") &lt;&gt; 0 then
                  if Len(Field) = 12 Then
                   Field = "6" &amp; Field
                   Field = """" &amp; Replace(Field, "-", "") &amp; """"
                  else
                   Field = ""
                  end if
              elseif Left(Field, 3) = "011"  and  InStr(Field, "-") = 0 then
                  if Len(Field) = 11 Then
                   Field = "6" &amp; Field
                  else
                   Field = ""
                  end if
              elseif Left(Field, 2) &lt;&gt; "01" and IsNumeric(Field) = true then
                   Field = ""
              elseif Left(Field, 2) &lt;&gt; "01" and InStr(Field, "-") &lt;&gt; 0 then
                   Field = ""
              end if
              Response.Write Separator &amp; Field
              Separator = ","
          next
          if (Field &lt;&gt; "") then
          Response.Write vbNewLine
          end if
          RS.MoveNext
      loop
  end sub
  
  Dim objRS, objConn
  
  set objConn = server.CreateObject("ADODB.Connection")
  objConn.ConnectionString = strConn
  objConn.Open
  set objRS = server.CreateObject("ADODB.RecordSet")
  objRS.Open strSQL, strConn, 0, 1
  
  Write_CSV_From_Recordset objRS
  Response.ContentType = "text/csv"
  Response.AddHeader "Content-Disposition", "attachment;filename=export.csv"
  %&gt;
  </code></pre>