The regular expressions from Recipe 9.11 can be reused here to iterate over each field in a CSV subject string. With a bit of extra code, you can count the number of fields from left to right in each row, or record, and extract the fields at the position you’re interested in.
The following regular expression (shown with and without the free-spacing option) matches a single CSV field and its preceding delimiter in two separate capturing groups. Since line breaks can appear within double-quoted fields, it would not be accurate to simply search from the beginning of each line in your CSV string. By matching and stepping past fields one by one, you can easily determine which line breaks appear outside of double-quoted fields and therefore start a new record.
Tip
The regular expressions in this recipe are designed to work correctly with valid CSV files only, according to the format rules discussed in Comma-Separated Values (CSV).
(,| ? |^)([^", ]+|"(?:[^"]|"")*")?
| Regex options: None |
| Regex flavors: .NET, Java, JavaScript, PCRE, Perl, Python, Ruby |
( , | ? | ^ ) # Capture the leading field delimiter to backref 1 ( # Capture a single field to backref 2: [^", ]+ # Unquoted field | # Or: " (?:[^"]|"")* " # Quoted field (may contain escaped double quotes) )? # The group is optional because fields may be empty
| Regex options: Free-spacing |
| Regex flavors: .NET, Java, XRegExp, PCRE, Perl, Python, Ruby |
These regular expressions are exactly the same as in Recipe 9.11, and they can be repurposed for plenty of other CSV processing tasks as well. The following example code demonstrates how you can use the version without the free-spacing option to help you extract a CSV column.
The following code is a complete web page that includes two multiline text input fields and a button between them labeled Extract Column 3. Clicking the button takes whatever string you put into the Input text box, extracts the value of the third field in each record with the help of the regular expression just shown, then puts the entire column (with each value separated by a line break) into the Output field. To test it, save this code into a file with the .html extension and open it in your favorite web browser:
<html>
<head>
<title>Extract the third column from a CSV string</title>
</head>
<body>
<p>Input:</p>
<textarea id="input" rows="5" cols="75"></textarea>
<p><input type="button" value="Extract Column 3"
onclick="displayCsvColumn(2)"></p>
<p>Output:</p>
<textarea id="output" rows="5" cols="75"></textarea>
<script>
function displayCsvColumn(index) {
var input = document.getElementById("input"),
output = document.getElementById("output"),
columnFields = getCsvColumn(input.value, index);
if (columnFields.length > 0) {
// Show each record on its own line, separated by a line feed (
)
output.value = columnFields.join("
");
} else {
output.value = "[No data found to extract]";
}
}
// Return an array of CSV fields at the provided, zero-based index
function getCsvColumn(csv, index) {
var regex = /(,|
?
|^)([^",
]+|"(?:[^"]|"")*")?/g,
result = [],
columnIndex = 0,
match;
while (match = regex.exec(csv)) {
// Check the value of backreference 1. If it's a comma,
// increment columnIndex. Otherwise, reset it to zero.
if (match[1] == ",") {
columnIndex++;
} else {
columnIndex = 0;
}
if (columnIndex == index) {
// Add the field (backref 2) at the end of the result array
result.push(match[2]);
}
// If there is an empty match, prevent some browsers from getting
// stuck in an infinite loop
if (match.index == regex.lastIndex) {
regex.lastIndex++;
}
}
return result;
}
</script>
</body>
</html>Since the regular expressions here are repurposed from Recipe 9.11, we won’t repeat the detailed explanation of how they work. However, this recipe includes new JavaScript example code that uses the regex to extract fields at a specific index from each record in the CSV subject string.
In the provided code, the getCsvColumn() function works by iterating over
the subject string one match at a time. After each match, backreference
1 is examined to check whether it contains a comma. If so, you’ve
matched something other than the first field in a row, so the columnIndex variable is
incremented to keep track of which column you’re at. If backreference 1
is anything other than a comma (i.e., an empty string or a line break),
you’ve matched the first field in a new row and columnIndex is reset to zero.
The next step in the code is to check whether the columnIndex counter has reached
the index you’re looking to extract. Every time it does, the value of
backreference 2 (everything after the leading delimiter) is pushed to
the result array. After
you’ve iterated over the entire subject string, the getCsvColumn() function returns an
array containing values for the entire specified column (in this
example, the third column). The list of matches is then dumped into the
second text box on the page, with each value separated by a line feed
character (
).
A simple improvement would be to let the user specify which column
index should be extracted, via a prompt or additional text field. The
getCsvColumn() function
we’ve been discussing is already written with this feature in mind, and
lets you specify the desired column as an integer (counting from zero)
via its second parameter (index).
Although using code to iterate over a string one CSV field at a time allows for extra flexibility, if you’re using a text editor to get the job done, you may be limited to just search-and-replace. In this situation, you can achieve a similar result by matching each complete record and replacing it with the value of the field at the column index you’re searching for (using a backreference). The following regexes illustrate this technique for particular column indexes, replacing each record with the field in a specific column.
With all of these regexes, if any record does not contain at least as many fields as the column index you’re searching for, that record will not be matched and will be left in place.
^([^", ]+|"(?:[^"]|"")*")?(?:,(?:[^", ]+|"(?:[^"]|"")*")?)*
| Regex options: ^ and $ match at line breaks |
| Regex flavors: .NET, Java, JavaScript, PCRE, Perl, Python, Ruby |
^(?:[^", ]+|"(?:[^"]|"")*")?,([^", ]+|"(?:[^"]|"")*")?↵ (?:,(?:[^", ]+|"(?:[^"]|"")*")?)*
| Regex options: ^ and $ match at line breaks |
| Regex flavors: .NET, Java, JavaScript, PCRE, Perl, Python, Ruby |
^(?:[^",
]+|"(?:[^"]|"")*")?(?:,(?:[^",
]+|"(?:[^"]|"")*")?){1},↵
([^",
]+|"(?:[^"]|"")*")?(?:,(?:[^",
]+|"(?:[^"]|"")*")?)*| Regex options: ^ and $ match at line breaks |
| Regex flavors: .NET, Java, JavaScript, PCRE, Perl, Python, Ruby |
Increment the number within the ‹{1}› quantifier to make this last regex work for
anything higher than column 3. For example, change it to ‹{2}› to capture fields from
column 4, ‹{3}› for
column 5, and so on. If you’re working with column 3, you can simply
remove the ‹{1}› if you
prefer, since it has no effect here.
Recipe 9.11 shows how to use the regex in this recipe to change the delimiters in a CSV file from commas to tabs.
Techniques used in the regular expressions and replacement text in this recipe are discussed in Chapter 2. Recipe 2.2 explains how to match nonprinting characters. Recipe 2.3 explains character classes. Recipe 2.5 explains anchors. Recipe 2.8 explains alternation. Recipe 2.9 explains grouping. Recipe 2.12 explains repetition. Recipe 2.21 explains how to insert text matched by capturing groups into the replacement text.