将包含逗号和双引号的字符串写入CSV [英] Write a string containing commas and double quotes to CSV
问题描述
我正在尝试在NetSuite(一种运行服务器端JavaScript(称为Suitescript 2.0)的CRM系统)中制作包含30,000多种商品的Google购物供稿.本质上，它只是具有更多限制的JavaScript.我的任务是将此产品Feed作为CSV输出.
I'm trying to produce a Google Shopping feed of 30,000+ items in NetSuite, a CRM system that runs server-side JavaScript that it calls Suitescript 2.0. Essentially, it's just JavaScript with a few more restrictions. I've been tasked with outputting this product feed as a CSV.
问题在于这些产品的产品描述包含逗号，双引号，单引号和HTML的变量数量.刚开始时，只是逗号引起了我的问题，所以经过一番研究，我将输出的字符串用双引号引起来:
The problem is that the product descriptions of these items contain variables amounts of commas, double quotes, single quotes and HTML. At first, it was just the commas causing me problems, so after a bit of research, I wrapped the strings I was outputting in double quotes:
//This function isn't terribly important, but is referenced below
function sanitizeString (desc) {
var itemDesc;
if (desc) {
itemDesc = desc.replace(/(\r\n|\n|\r|\s+|\t| )/gm,' ');
itemDesc = itemDesc.replace(/,/g, '\,');
itemDesc = itemDesc.replace(/"/g, '\"');
itemDesc = itemDesc.replace(/'/g, '\'');
itemDesc = itemDesc.replace(/ +(?= )/g,'');
} else {
itemDesc = '';
}
return itemDesc;
}
var row = '';
for (var i = 0; i < columns.length; i++) {
var col = columns[i];
row += '"' + sanitizeString(val[col]) + '"';
if (i != columns.length - 1) {
row += ',';
}
}
newFeed.appendLine({value: row});
但是，即使我的sanitizeString()函数应该转义它们，这些双引号与字符串中的双引号似乎也奇怪地相互作用，从而导致一些奇怪的格式.每当描述包含双引号时，下一行都不会得到其自己的行.它被附加到最后一列.
However, it seems that these double quotes are interacting strangely with double quotes within the string causing some weird formatting, even though my sanitizeString() function should be escaping them. Any time that a description contains a double quote, the next row doesn't get it's own line. It gets appended to the last column.
因此，自然地，我逃脱了这样的外部引号:
So, naturally, I escaped the external quotes like this:
row += '\"' + sanitizeString(val[col]) + '\"';
这样做会使事情完全束手无策，很多项目不会被压入新行，而且我会尽我所能允许的列数，因为它会一直继续下去.
Doing that makes things go completely haywire, a lot of items don't get pushed to new lines and I max out the number of columns I'm allowed because it just keeps on going.
另一种自然的解决方案是编辑产品说明，但我并不急于为30,000多种产品这样做...
The other natural solution would be to go edit the product descriptions, but I'm not terribly anxious to do that for 30,000+ items...
有人知道这里会发生什么吗?我觉得我忽略了一些非常简单的事情...
Does anybody know what might be going on here? I feel like there's something really simple I'm overlooking...
推荐答案
根据 CSV规范，要在已加引号的字符串中包含双引号，则需要使用两个双引号(").我改变了:
It turns out that, according to the CSV specs, to include double quotes within a string that is already quoted, you need to use two double quotes (""). I changed:
itemDesc = itemDesc.replace(/"/g, '\"');
到
itemDesc = itemDesc.replace(/"/g, '""');
我也删除了
itemDesc = itemDesc.replace(/,/g, '\,');
itemDesc = itemDesc.replace(/'/g, '\'');
由于CSV中的列已被引用.这些都是不必要的.
Since the column in the CSV is being quoted already. These are unnecessary.
这篇关于将包含逗号和双引号的字符串写入CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
