使用 VBScript 创建 CSV 文件.一个处理值的函数，让 Excel 开心 [英] Using VBScript to create CSV files. A function to deal with values to make Excel happy
问题描述
我想对以下功能提出改进意见/建议等.它将包裹在经典 ASP 页面上传递到 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....
我想知道为什么 If Len(txt) = 0 Then 在创建 CSV 文件的页面中运行时会失败，但在常规 ASP 页面中运行良好.我必须使用 If "" &txt = "" 然后让它在两个页面上都工作
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
推荐答案
更新(基于最新的 评论)
如果数据库已经有像 ’ 这样存储的值，那么您需要考虑 Web 应用程序使用错误的编码插入数据，并且您最终会在数据库.
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.
您不需要任何问题，当您将 CSV 发送为 UTF-8 时，Excel 会将其解释为 ASCII.有一个有用的技巧可以欺骗 Excel 使用正确的编码而不是假设 ASCII，我一直使用这种技术并且效果很好.它涉及在显示数据之前将 BOM(字节顺序标记)写入流，告诉 Excel 数据是 UTF-8 而不是 ASCII 编码的.
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))
这应该在您使用 Response.Write() 从 insertCSV() 函数输出您的文本之前指定.
This should be specified before you use Response.Write() to output your text from the insertCSV() function.
使用这种代码
if instr(tmp, "Â") then
当输出 (Â) 试图告诉您编码有问题时，这是错误的.
is just wrong when what the output (Â) is trying to do is tell you something is wrong with the encoding.
有用的链接
- Microsoft Excel 会破坏 .csv 文件中的变音符号?
- 如何在 PHP 中输出 UTF-8 CSV，Excel 可以正确读取?
- 哪种编码可以在 Mac 和 Windows 上使用 Excel 正确打开 CSV 文件?(在这种情况下仍然适用)李>
- 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屋！
