使用VBScript创建CSV文件。对付值的功能，使Excel的快乐 [英] Using VBScript to create CSV files. A function to deal with values to make Excel happy

问题描述

我想对以下功能的改善等意见/建议。这将周围的每一个值被包裹被传递到一个传统的ASP页面上的CSV文件。由于低于其正在处理，我们对抗的所有情况，现在......

我想知道为什么。如果莱恩（TXT）= 0时，它在创建该CSV文件althouh页面运行失败，则运行良好的一个普通的ASP页面时。我是个有使用如与＆amp; TXT =然后，使其两个页面上工作。

 函数insertCSV（TXT）
  如果莱恩（TXT）= 0则
  如果与＆amp; TXT =那
      退出功能
  其他
    昏暗的TMP
    TMP = TXT
    如果ISNUMERIC（TMP），然后
        如果左（TMP，1）=0，那么
            TMP =与＆amp; TMP和放大器; 
        其他
            TMP = TMP
        万一
    其他
        TMP =替换（TMP，，）
        如果INSTR（TMP，，）或INSTR（TMP，），则TMP =与＆amp; TMP和放大器; 
        如果INSTR（TMP，®），则TMP =替换（TMP，®，＆放大器;章;）
        如果INSTR（TMP，™），则TMP =替换（TMP，™，＆放大器;贸易;）
        如果INSTR（TMP©），然后TMP =替换（TMP，©，＆放大器;复制;）
        如果INSTR（TMP，vbcrlf）则TMP =替换（TMP，vbcrlf，）
        如果INSTR（TMP，A），则TMP =替换（TMP，A，）
    万一
    insertCSV TMP =
  万一
最终功能
 

解决方案

  

更新（基于最新的<一个href=\"http://stackoverflow.com/questions/24666723/using-vbscript-to-create-csv-files-a-function-to-deal-with-values-to-make-excel?noredirect=1#comment38281284_24681290\">comments)

  
  

如果已经有存储如值€™数据库，那么你需要考虑到Web应用程序使用了错误的编码插入数据和您使用结束了在编码数据库中的一个不匹配。

  
  

  
• 答案转换UTF-8字符串经典ASP到SQL数据库的

  

---

您不需要任何的这个问题是Excel的跨$ P $点的CSV为 ASCII 当你发送它作为 UTF -8 。
还有就是蠢人的Excel到使用正确的编码而不是假设ASCII一个有用的技巧，我用这个方法所有的时间和行之有效的。它涉及到的数据是 UTF-8 不是 ASCII 连接codeD。

BOM到DUP EXCEL导入编码CSV正确，而不是使用ASCII！
调用Response.BinaryWrite（CHRB（239）及CHRB（187）及CHRB（191））


您使用此之前应指定的Response.Write（）来输出文本从 insertCSV（）功能。

---

使用这种code的

如果INSTR（TMP，A），那么


就是错误的，当什么输出（ A ）正在试图做的是告诉你，什么是错的编码。

---

相关链接

• Microsoft Excel中轧液变音符号以.csv文件？


• 我怎么能输出UTF-8 CSV在PHP Excel将正确读取？<​​/A>


• 哪种编码与Excel Mac和Windows上正常打开CSV文件？（仍适用于这种情况）

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

解决方案

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)

这篇关于使用VBScript创建CSV文件。对付值的功能，使Excel的快乐的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！