Using VBScript to create CSV files. A function to

Question

I would like opinions/suggestions for improvement etc on the following function. It will be wrapped around every value being passed into a CSV file on a classic ASP page. As is below it is handling all situations we are up against, for now....

I would like to know why If Len(txt) = 0 Then fails when it is run in the page that creates the CSV file althouh runs fine when in a regular ASP page. I ma having to use If "" & txt = "" Then to make it work on both pages

function insertCSV(txt)
  'If Len(txt) = 0 Then    
  If "" & txt = "" Then
      Exit Function
  Else
    Dim tmp
    tmp = txt
    if isNumeric(tmp) then
        if left(tmp, 1) = "0" then
            tmp = """" & tmp & """"
        else
            tmp = tmp
        end if
    else
        tmp = Replace(tmp, """", """""")
        if instr(tmp, ",") or instr(tmp, """") then tmp = """" & tmp & """" 
        if instr(tmp, "®") then tmp = replace(tmp, "®", "®")
        if instr(tmp, "™") then tmp = replace(tmp, "™", "™")
        if instr(tmp, "©") then tmp = replace(tmp, "©", "©")
        if instr(tmp, vbcrlf) then tmp = replace(tmp, vbcrlf, "")
        if instr(tmp, "Â") then tmp = replace(tmp, "Â", "")
    end if
    insertCSV = tmp
  End If
end function

Answer 1

Few things:

1. This section:

   If "" & txt = "" Then
       insertCSV = ""
   Else
   

   If you just want to return an empty string if txt is empty, you can just do this:

   If Len(txt) = 0 Then Exit Function
   

2. You don't need to use End If for single-line If statements.

3. This line:

   if isNumeric(tmp) AND left(tmp, 1) <> "0" then tmp = tmp end if
   

   You're assigning the value back to itself? What purpose does this serve?

4. Don't you want to replace just the symbol © with ©? The way you have it written, you're replacing the entire text with © (same goes for your other tests). I would think you'd want to do this instead:

   If InStr(tmp, "©") Then tmp = Replace(tmp, "©", "©")
   

Try making those changes and then post an updated version of your routine and let's see how it looks.

Answer 2

Update (based on latest comments)

If the database already has values stored like ’ then you need to consider that the web application is inserting the data using the wrong encoding and you are ending up with a mismatch in encoding in the database.

• Answer to Convert UTF-8 String Classic ASP to SQL Database

---

You don't need any of that the issue is Excel interprets the CSV as ASCII when you are sending it as UTF-8. There is a useful trick that fools Excel into using the correct encoding rather then assuming ASCII, I use this technique all the time and works well. It involves writing a BOM (Byte Order Mark) to the stream before displaying your data that tells Excel the data is UTF-8 not ASCII encoded.

'BOM to dup Excel into encoding the CSV correctly instead of using ASCII!!!
Call Response.BinaryWrite(ChrB(239) & ChrB(187) & ChrB(191))

This should be specified before you use Response.Write() to output your text from the insertCSV() function.

---

Using this kind of code

if instr(tmp, "Â") then

is just wrong when what the output (Â) is trying to do is tell you something is wrong with the encoding.

---

Useful Links

• Microsoft Excel mangles Diacritics in .csv files?
• How can I output a UTF-8 CSV in PHP that Excel will read properly?
• Which encoding opens CSV files correctly with Excel on both Mac and Windows? (still applies in this case)