#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2007
    Posts
    62
    Rep Power
    7

    Question ASP Export to .csv


    Hi all,

    I have this code which uses an SQL query to extract fields from a table into a .csv file and it works. The issue is there are two date fields in the query and they are be converted to US date format when they should be UK. The date format on the 2008 server is set to united kingdom and the machine has been restarted.

    This code was running on a 2003 server and it worked fine on there. The two date fields are last_hs and next_hs. Does anyone have any ideas how i can code them to print out in UK format?


    Code:
    <%@ Language=VBScript %>
    <!--#include file="../../includes/connection.asp"-->
    <%
    
      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
    
      '
      ' EXAMPLE USAGE
      '
      ' - Open a RECORDSET object (forward-only, read-only recommended)
      ' - Send appropriate response headers
      ' - Call the function
      '
    
      dim RS1
     
      
      set RS1 = Server.CreateObject( "ADODB.RECORDSET" )
      
       
          qry = ";with cteDelegates(edrs_no, company, postalcode, last_hs, next_hs, risk_level, sct1, sct2, training_provider, rown) as(Select wce_course_delegate_link.edrs_no, wce_contact.company, postalcode, wce_contact.last_hs, wce_contact.next_hs, wce_contact.risk_level, wce_course_delegate_link.sct1, wce_course_delegate_link.sct2, wce_course_delegate_link.training_provider, ROW_NUMBER() OVER (PARTITION BY wce_course_delegate_link.edrs_no ORDER BY wce_course_delegate_link.edrs_no, wce_course_delegate_link.sct1, wce_course_delegate_link.training_provider) from wce_course_delegate_link left join wce_contact on wce_course_delegate_link.edrs_no =wce_contact.edrs where wce_course_delegate_link.end_date is null and wce_contact.record_type= 'company') Select edrs_no, company, postalcode, last_hs, next_hs, risk_level, sct1, sct2, training_provider from cteDelegates where rown = 1  order by edrs_no"
    	Set RS1 = connStr.Execute(qry)
      Response.ContentType = "text/csv"
    
      Response.AddHeader "Content-Disposition", "attachment;filename=export.csv"
    
      Write_CSV_From_Recordset RS1
    %>
    Thanks for looking!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2007
    Posts
    62
    Rep Power
    7
    The issue has been resolved by someone and the answer was to convert the field in the SQL. See below.

    Code:
    Select edrs_no, company, postalcode, last_hs=CONVERT(varchar, last_hs, 103), next_hs=CONVERT(varchar, next_hs, 103) , risk_level, sct1, sct2, training_provider from cteDelegates where rown = 1  order by edrs_no

IMN logo majestic logo threadwatch logo seochat tools logo