如何使用 Javascript 解析数据中包含逗号的 CSV 字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8493195/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I parse a CSV string with Javascript, which contains comma in data?
提问by Hans
I have the following type of string
我有以下类型的字符串
var string = "'string, duppi, du', 23, lala"
I want to split the string into an array on each comma, but only the commas outside the single quotation marks.
我想将字符串拆分为每个逗号上的数组，但只有单引号外的逗号。
I cant figure out the right regex for the split...
我无法找出拆分的正确正则表达式...
string.split(/,/)
will give me
会给我
["'string", " duppi", " du'", " 23", " lala"]
but the result should be:
但结果应该是：
["string, duppi, du", "23", "lala"]
is there any cross browser solution?
有没有跨浏览器的解决方案？
回答by ridgerunner
Disclaimer
免责声明
2014-12-01 Update: The answer below works only for one very specific format of CSV. As correctly pointed out by DG in the comments, this solution does NOT fit the RFC 4180 definition of CSV and it also does NOT fit MS Excel format. This solution simply demonstrates how one can parse one (non-standard) CSV line of input which contains a mix of string types, where the strings may contain escaped quotes and commas.
2014-12-01 更新：以下答案仅适用于一种非常特定的 CSV 格式。正如 DG 在评论中正确指出的那样，该解决方案不符合 CSV 的 RFC 4180 定义，也不符合 MS Excel 格式。该解决方案简单地演示了如何解析包含混合字符串类型的一个（非标准）CSV 输入行，其中字符串可能包含转义引号和逗号。
A non-standard CSV solution
一个非标准的CSV解决方案
As austincheney correctly points out, you really need to parse the string from start to finish if you wish to properly handle quoted strings that may contain escaped characters. Also, the OP does not clearly define what a "CSV string" really is. First we must define what constitutes a valid CSV string and its individual values.
正如 austincheney 正确指出的那样，如果您希望正确处理可能包含转义字符的带引号的字符串，您确实需要从头到尾解析字符串。此外，OP 没有明确定义“CSV 字符串”到底是什么。首先，我们必须定义什么构成有效的 CSV 字符串及其各个值。
Given: "CSV String" Definition
给定：“CSV 字符串”定义
For the purpose of this discussion, a "CSV string" consists of zero or more values, where multiple values are separated by a comma. Each value may consist of:
出于本次讨论的目的，“CSV 字符串”由零个或多个值组成，其中多个值用逗号分隔。每个值可能包括：
- A double quoted string. (may contain unescaped single quotes.)
- A single quoted string. (may contain unescaped double quotes.)
- A non-quoted string. (may NOT contain quotes, commas or backslashes.)
- An empty value. (An all whitespace value is considered empty.)
- 双引号字符串。（可能包含未转义的单引号。）
- 单引号字符串。（可能包含未转义的双引号。）
- 一个不带引号的字符串。（不能包含引号、逗号或反斜杠。）
- 一个空值。（全空白值被认为是空的。）
Rules/Notes:
规则/注意事项：
- Quoted values may contain commas.
- Quoted values may contain escaped-anything, e.g.
'that\'s cool'. - Values containing quotes, commas, or backslashes must be quoted.
- Values containing leading or trailing whitespace must be quoted.
- The backslash is removed from all:
\'in single quoted values. - The backslash is removed from all:
\"in double quoted values. - Non-quoted strings are trimmed of any leading and trailing spaces.
- The comma separator may have adjacent whitespace (which is ignored).
- 引用的值可能包含逗号。
- 引用的值可能包含转义的任何内容，例如
'that\'s cool'. - 必须引用包含引号、逗号或反斜杠的值。
- 必须引用包含前导或尾随空格的值。
- 反斜杠从 all:
\'中删除：在单引号中。 - 反斜杠从所有内容中删除：
\"在双引号中。 - 未加引号的字符串将删除任何前导和尾随空格。
- 逗号分隔符可能有相邻的空格（被忽略）。
Find:
找：
A JavaScript function which converts a valid CSV string (as defined above) into an array of string values.
将有效的 CSV 字符串（如上定义）转换为字符串值数组的 JavaScript 函数。
Solution:
解决方案：
The regular expressions used by this solution are complex. And (IMHO) allnon-trivial regexes should be presented in free-spacing mode with lots of comments and indentation. Unfortunately, JavaScript does not allow free-spacing mode. Thus, the regular expressions implemented by this solution are first presented in native regex syntax (expressed using Python's handy: r'''...'''raw-multi-line-string syntax).
此解决方案使用的正则表达式很复杂。并且（恕我直言）所有重要的正则表达式都应该以自由间距模式呈现，并带有大量注释和缩进。不幸的是，JavaScript 不允许自由间距模式。因此，此解决方案实现的正则表达式首先以原生正则表达式语法呈现（使用 Python 的方便：r'''...'''原始多行字符串语法表示）。
First here is a regular expression which validates that a CVS string meets the above requirements:
首先是一个正则表达式，用于验证 CVS 字符串是否满足上述要求：
Regex to validate a "CSV string":
正则表达式验证“CSV 字符串”：
re_valid = r"""
# Validate a CSV string having single, double or un-quoted values.
^ # Anchor to start of string.
\s* # Allow whitespace before value.
(?: # Group for value alternatives.
'[^'\]*(?:\[\S\s][^'\]*)*' # Either Single quoted string,
| "[^"\]*(?:\[\S\s][^"\]*)*" # or Double quoted string,
| [^,'"\s\]*(?:\s+[^,'"\s\]+)* # or Non-comma, non-quote stuff.
) # End group of value alternatives.
\s* # Allow whitespace after value.
(?: # Zero or more additional values
, # Values separated by a comma.
\s* # Allow whitespace before value.
(?: # Group for value alternatives.
'[^'\]*(?:\[\S\s][^'\]*)*' # Either Single quoted string,
| "[^"\]*(?:\[\S\s][^"\]*)*" # or Double quoted string,
| [^,'"\s\]*(?:\s+[^,'"\s\]+)* # or Non-comma, non-quote stuff.
) # End group of value alternatives.
\s* # Allow whitespace after value.
)* # Zero or more additional values
$ # Anchor to end of string.
"""
If a string matches the above regex, then that string is a valid CSV string (according to the rules previously stated) and may be parsed using the following regex. The following regex is then used to match one value from the CSV string. It is applied repeatedly until no more matches are found (and all values have been parsed).
如果字符串与上述正则表达式匹配，则该字符串是有效的 CSV 字符串（根据前面所述的规则）并且可以使用以下正则表达式进行解析。然后使用以下正则表达式匹配 CSV 字符串中的一个值。它会重复应用，直到找不到更多匹配项（并且所有值都已解析）。
Regex to parse one value from valid CSV string:
正则表达式从有效的 CSV 字符串解析一个值：
re_value = r"""
# Match one value in valid CSV string.
(?!\s*$) # Don't match empty last value.
\s* # Strip whitespace before value.
(?: # Group for value alternatives.
'([^'\]*(?:\[\S\s][^'\]*)*)' # Either : Single quoted string,
| "([^"\]*(?:\[\S\s][^"\]*)*)" # or : Double quoted string,
| ([^,'"\s\]*(?:\s+[^,'"\s\]+)*) # or : Non-comma, non-quote stuff.
) # End group of value alternatives.
\s* # Strip whitespace after value.
(?:,|$) # Field ends on comma or EOS.
"""
Note that there is one special case value that this regex does not match - the very last value when that value is empty. This special "empty last value"case is tested for and handled by the js function which follows.
请注意，此正则表达式不匹配一个特殊情况值 - 该值为空时的最后一个值。这种特殊的“最后一个值为空”的情况由后面的 js 函数测试和处理。
JavaScript function to parse CSV string:
解析 CSV 字符串的 JavaScript 函数：
// Return array of string values, or NULL if CSV string not well formed.
function CSVtoArray(text) {
var re_valid = /^\s*(?:'[^'\]*(?:\[\S\s][^'\]*)*'|"[^"\]*(?:\[\S\s][^"\]*)*"|[^,'"\s\]*(?:\s+[^,'"\s\]+)*)\s*(?:,\s*(?:'[^'\]*(?:\[\S\s][^'\]*)*'|"[^"\]*(?:\[\S\s][^"\]*)*"|[^,'"\s\]*(?:\s+[^,'"\s\]+)*)\s*)*$/;
var re_value = /(?!\s*$)\s*(?:'([^'\]*(?:\[\S\s][^'\]*)*)'|"([^"\]*(?:\[\S\s][^"\]*)*)"|([^,'"\s\]*(?:\s+[^,'"\s\]+)*))\s*(?:,|$)/g;
// Return NULL if input string is not well formed CSV string.
if (!re_valid.test(text)) return null;
var a = []; // Initialize array to receive values.
text.replace(re_value, // "Walk" the string using replace with callback.
function(m0, m1, m2, m3) {
// Remove backslash from \' in single quoted values.
if (m1 !== undefined) a.push(m1.replace(/\'/g, "'"));
// Remove backslash from \" in double quoted values.
else if (m2 !== undefined) a.push(m2.replace(/\"/g, '"'));
else if (m3 !== undefined) a.push(m3);
return ''; // Return empty string.
});
// Handle special case of empty last value.
if (/,\s*$/.test(text)) a.push('');
return a;
};
Example input and output:
示例输入和输出：
In the following examples, curly braces are used to delimit the {result strings}. (This is to help visualize leading/trailing spaces and zero-length strings.)
在以下示例中，大括号用于分隔{result strings}. （这是为了帮助可视化前导/尾随空格和零长度字符串。）
// Test 1: Test string from original question.
var test = "'string, duppi, du', 23, lala";
var a = CSVtoArray(test);
/* Array hes 3 elements:
a[0] = {string, duppi, du}
a[1] = {23}
a[2] = {lala} */
// Test 2: Empty CSV string.
var test = "";
var a = CSVtoArray(test);
/* Array hes 0 elements: */
// Test 3: CSV string with two empty values.
var test = ",";
var a = CSVtoArray(test);
/* Array hes 2 elements:
a[0] = {}
a[1] = {} */
// Test 4: Double quoted CSV string having single quoted values.
var test = "'one','two with escaped \' single quote', 'three, with, commas'";
var a = CSVtoArray(test);
/* Array hes 3 elements:
a[0] = {one}
a[1] = {two with escaped ' single quote}
a[2] = {three, with, commas} */
// Test 5: Single quoted CSV string having double quoted values.
var test = '"one","two with escaped \" double quote", "three, with, commas"';
var a = CSVtoArray(test);
/* Array hes 3 elements:
a[0] = {one}
a[1] = {two with escaped " double quote}
a[2] = {three, with, commas} */
// Test 6: CSV string with whitespace in and around empty and non-empty values.
var test = " one , 'two' , , ' four' ,, 'six ', ' seven ' , ";
var a = CSVtoArray(test);
/* Array hes 8 elements:
a[0] = {one}
a[1] = {two}
a[2] = {}
a[3] = { four}
a[4] = {}
a[5] = {six }
a[6] = { seven }
a[7] = {} */
Additional notes:
补充说明：
This solution requires that the CSV string be "valid". For example, unquoted values may not contain backslashes or quotes, e.g. the following CSV string is NOT valid:
此解决方案要求 CSV 字符串是“有效的”。例如，未加引号的值可能不包含反斜杠或引号，例如以下 CSV 字符串无效：
var invalid1 = "one, that's me!, escaped \, comma"
This is not really a limitation because any sub-string may be represented as either a single or double quoted value. Note also that this solution represents only one possible definition for: "Comma Separated Values".
这并不是真正的限制，因为任何子字符串都可以表示为单引号或双引号值。另请注意，此解决方案仅代表一种可能的定义：“逗号分隔值”。
Edit: 2014-05-19:Added disclaimer. Edit: 2014-12-01:Moved disclaimer to top.
编辑：2014-05-19：添加免责声明。 编辑：2014-12-01：将免责声明移至顶部。
回答by niry
RFC 4180 solution
RFC 4180 解决方案
This does not solve the string in the question since its format is not conforming with RFC 4180; the acceptable encoding is escaping double quote with double quote. The solution below works correctly with CSV files d/l from google spreadsheets.
这不能解决问题中的字符串，因为其格式不符合 RFC 4180；可接受的编码是用双引号转义双引号。下面的解决方案适用于来自谷歌电子表格的 CSV 文件 d/l。
UPDATE (3/2017)
更新 (3/2017)
Parsing single line would be wrong. According to RFC 4180 fields may contain CRLF which will cause any line reader to break the CSV file. Here is an updated version that parses CSV string:
解析单行是错误的。根据 RFC 4180 字段可能包含 CRLF，这将导致任何行阅读器破坏 CSV 文件。这是解析 CSV 字符串的更新版本：
'use strict';
function csvToArray(text) {
let p = '', row = [''], ret = [row], i = 0, r = 0, s = !0, l;
for (l of text) {
if ('"' === l) {
if (s && l === p) row[i] += l;
s = !s;
} else if (',' === l && s) l = row[++i] = '';
else if ('\n' === l && s) {
if ('\r' === p) row[i] = row[i].slice(0, -1);
row = ret[++r] = [l = '']; i = 0;
} else row[i] += l;
p = l;
}
return ret;
};
let test = '"one","two with escaped """" double quotes""","three, with, commas",four with no quotes,"five with CRLF\r\n"\r\n"2nd line one","two with escaped """" double quotes""","three, with, commas",four with no quotes,"five with CRLF\r\n"';
console.log(csvToArray(test));OLD ANSWER
旧答案
(Single line solution)
（单线解决方案）
function CSVtoArray(text) {
let ret = [''], i = 0, p = '', s = true;
for (let l in text) {
l = text[l];
if ('"' === l) {
s = !s;
if ('"' === p) {
ret[i] += '"';
l = '-';
} else if ('' === p)
l = '-';
} else if (s && ',' === l)
l = ret[++i] = '';
else
ret[i] += l;
p = l;
}
return ret;
}
let test = '"one","two with escaped """" double quotes""","three, with, commas",four with no quotes,five for fun';
console.log(CSVtoArray(test));And for the fun, here is how you create CSV from the array:
有趣的是，以下是从数组创建 CSV 的方法：
function arrayToCSV(row) {
for (let i in row) {
row[i] = row[i].replace(/"/g, '""');
}
return '"' + row.join('","') + '"';
}
let row = [
"one",
"two with escaped \" double quote",
"three, with, commas",
"four with no quotes (now has)",
"five for fun"
];
let text = arrayToCSV(row);
console.log(text);回答by HammerNL
I liked FakeRainBrigand's answer, however it contains a few problems: It can not handle whitespace between a quote and a comma, and does not support 2 consecutive commas. I tried editing his answer but my edit got rejected by reviewers that apparently did not understand my code. Here is my version of FakeRainBrigand's code. There is also a fiddle: http://jsfiddle.net/xTezm/46/
我喜欢 FakeRainBrigand 的回答，但是它包含一些问题：它不能处理引号和逗号之间的空格，并且不支持 2 个连续的逗号。我尝试编辑他的答案，但我的编辑被显然不理解我的代码的审阅者拒绝了。这是我的 FakeRainBrigand 代码版本。还有一个小提琴：http: //jsfiddle.net/xTezm/46/
String.prototype.splitCSV = function() {
var matches = this.match(/(\s*"[^"]+"\s*|\s*[^,]+|,)(?=,|$)/g);
for (var n = 0; n < matches.length; ++n) {
matches[n] = matches[n].trim();
if (matches[n] == ',') matches[n] = '';
}
if (this[0] == ',') matches.unshift("");
return matches;
}
var string = ',"string, duppi, du" , 23 ,,, "string, duppi, du",dup,"", , lala';
var parsed = string.splitCSV();
alert(parsed.join('|'));
回答by Trevor Dixon
PEG(.js) grammar that handles RFC 4180 examples at http://en.wikipedia.org/wiki/Comma-separated_values:
在http://en.wikipedia.org/wiki/Comma-separated_values处理 RFC 4180 示例的 PEG(.js) 语法：
start
= [\n\r]* first:line rest:([\n\r]+ data:line { return data; })* [\n\r]* { rest.unshift(first); return rest; }
line
= first:field rest:("," text:field { return text; })*
& { return !!first || rest.length; } // ignore blank lines
{ rest.unshift(first); return rest; }
field
= '"' text:char* '"' { return text.join(''); }
/ text:[^\n\r,]* { return text.join(''); }
char
= '"' '"' { return '"'; }
/ [^"]
Test at http://jsfiddle.net/knvzk/10or https://pegjs.org/online.
在http://jsfiddle.net/knvzk/10或https://pegjs.org/online测试。
Download the generated parser at https://gist.github.com/3362830.
在https://gist.github.com/3362830下载生成的解析器。
回答by bjcullinan
I had a very specific use case where I wanted to copy cells from Google Sheets into my web app. Cells could include double-quotes and new-line characters. Using copy and paste, the cells are delimited by a tab characters, and cells with odd data are double quoted. I tried this main solution, the linked article using regexp, and Jquery-CSV, and CSVToArray. http://papaparse.com/Is the only one that worked out of the box. Copy and paste is seamless with Google Sheets with default auto-detect options.
我有一个非常具体的用例，我想将 Google 表格中的单元格复制到我的网络应用程序中。单元格可以包含双引号和换行符。使用复制和粘贴，单元格由制表符分隔，奇数数据的单元格用双引号引起来。我尝试了这个主要的解决方案，链接文章使用正则表达式、Jquery-CSV 和 CSVToArray。 http://papaparse.com/是唯一一个开箱即用的。复制和粘贴与带有默认自动检测选项的 Google 表格无缝连接。
回答by Seph Reed
Adding one more to the list, because I find all of the above not quite "KISS" enough.
在列表中再添加一个，因为我发现以上所有内容都不够“KISS”。
This one uses regex to find either commas or newlines while skipping over quoted items. Hopefully this is something noobies can read through on their own. The splitFinderregexp has three things it does (split by a |):
这个使用正则表达式来查找逗号或换行符，同时跳过引用的项目。希望这是菜鸟可以自己阅读的东西。正则splitFinder表达式有它做的三件事（被 a 分割|）：
,- finds commas\r?\n- finds new lines, (potentially with carriage return if the exporter was nice)"(\\"|[^"])*?"- skips anynthing surrounded in quotes, because commas and newlines don't matter in there. If there is an escaped quote\\"in the quoted item, it will get captured before an end quote can be found.
,- 找到逗号\r?\n- 找到新行，（如果出口商很好，可能会回车）"(\\"|[^"])*?"- 跳过引号中的任何内容，因为逗号和换行符在那里无关紧要。如果\\"引用的项目中有转义的引用，它将在找到结束引用之前被捕获。
const splitFinder = /,|\r?\n|"(\"|[^"])*?"/g;
function csvTo2dArray(parseMe) {
let currentRow = [];
const rowsOut = [currentRow];
let lastIndex = splitFinder.lastIndex = 0;
// add text from lastIndex to before a found newline or comma
const pushCell = (endIndex) => {
endIndex = endIndex || parseMe.length;
const addMe = parseMe.substring(lastIndex, endIndex);
// remove quotes around the item
currentRow.push(addMe.replace(/^"|"$/g, ""));
lastIndex = splitFinder.lastIndex;
}
let regexResp;
// for each regexp match (either comma, newline, or quoted item)
while (regexResp = splitFinder.exec(parseMe)) {
const split = regexResp[0];
// if it's not a quote capture, add an item to the current row
// (quote captures will be pushed by the newline or comma following)
if (split.startsWith(`"`) === false) {
const splitStartIndex = splitFinder.lastIndex - split.length;
pushCell(splitStartIndex);
// then start a new row if newline
const isNewLine = /^\r?\n$/.test(split);
if (isNewLine) { rowsOut.push(currentRow = []); }
}
}
// make sure to add the trailing text (no commas or newlines after)
pushCell();
return rowsOut;
}
const rawCsv = `a,b,c\n"test\r\n","comma, test","\r\n",",",\nsecond,row,ends,with,empty\n"quote\"test"`
const rows = csvTo2dArray(rawCsv);
console.log(rows);回答by Phrogz
If you can have your quote delimiter be double-quotes, then this is a duplicate of JavaScript Code to Parse CSV Data.
如果您可以将引号分隔符设为双引号，那么这是JavaScript Code to Parse CSV Data的副本。
You can either translate all single-quotes to double-quotes first:
您可以先将所有单引号转换为双引号：
string = string.replace( /'/g, '"' );
...or you can edit the regex in that question to recognize single-quotes instead of double-quotes:
...或者您可以编辑该问题中的正则表达式以识别单引号而不是双引号：
// Quoted fields.
"(?:'([^']*(?:''[^']*)*)'|" +
However, this assumes certain markup that is not clear from your question. Please clarify what all the various possibilities of markup can be, per my comment on your question.
但是，这假设了您的问题中不清楚的某些标记。根据我对您的问题的评论，请阐明标记的所有各种可能性。
回答by Brigand
People seemed to be against RegEx for this. Why?
人们似乎为此反对 RegEx。为什么？
(\s*'[^']+'|\s*[^,]+)(?=,|$)
Here's the code. I also made a fiddle.
这是代码。我也做了一个小提琴。
String.prototype.splitCSV = function(sep) {
var regex = /(\s*'[^']+'|\s*[^,]+)(?=,|$)/g;
return matches = this.match(regex);
}
var string = "'string, duppi, du', 23, 'string, duppi, du', lala";
var parsed = string.splitCSV();
alert(parsed.join('|'));
回答by austincheney
My answer presumes your input is a reflection of code/content from web sources where single and double quote characters are fully interchangeable provided they occur as an non-escaped matching set.
我的回答假定您的输入是来自网络资源的代码/内容的反映，其中单引号和双引号字符完全可以互换，前提是它们作为非转义匹配集出现。
You cannot use regex for this. You actually have to write a micro parser to analyze the string you wish to split. I will, for the sake of this answer, call the quoted parts of your strings as sub-strings. You need to specifically walk across the string. Consider the following case:
您不能为此使用正则表达式。您实际上必须编写一个微解析器来分析您希望拆分的字符串。为了这个答案，我将把你的字符串的引用部分称为子字符串。您需要专门穿过字符串。考虑以下情况：
var a = "some sample string with \"double quotes\" and 'single quotes' and some craziness like this: \\" or \'",
b = "sample of code from JavaScript with a regex containing a comma /\,/ that should probably be ignored.";
In this case you have absolutely no idea where a sub-string starts or ends by simply analyzing the input for a character pattern. Instead you have to write logic to make decisions on whether a quote character is used a quote character, is itself unquoted, and that the quote character is not following an escape.
在这种情况下，通过简单地分析字符模式的输入，您完全不知道子字符串在哪里开始或结束。相反，您必须编写逻辑来决定引号字符是否被用作引号字符，本身是否不带引号，以及引号字符是否跟在转义符之后。
I am not going to write that level of complexity of code for you, but you can look at something I recently wrote that has the pattern you need. This code has nothing to do with commas, but is otherwise a valid enough micro-parser for you to follow in writing your own code. Look into the asifix function of the following application:
我不会为您编写那种复杂程度的代码，但是您可以查看我最近编写的具有您需要的模式的内容。这段代码与逗号无关，但在其他方面是一个足够有效的微解析器，供您在编写自己的代码时遵循。查看以下应用程序的 asifix 函数：
https://github.com/austincheney/Pretty-Diff/blob/master/fulljsmin.js
https://github.com/austincheney/Pretty-Diff/blob/master/fulljsmin.js
回答by Sharathi RB
While reading csv to string it contain null value in between string so try it \0Line by line it works me.
在读取 csv 到字符串时，它在字符串之间包含空值，所以尝试\0逐行它对我有用。
stringLine = stringLine.replace( /##代码##/g, "" );
