Ask Ben: Parsing CSV Strings With Javascript Exec() Regular Expression Command
Posted February 19, 2009 at 10:03 AM
Just curious if you can help me with this.... I have a user enter the following string: website "content approval" new - into a text box and need to break it up so that it is 3 separate words eg:
1) website
2) content approval
3) new
I have tried using the following RegEx - "([^\\"]|\\.)*" but this only gets whats enclosed in the double quotes, how can I also select the other 2 words but excluding what I have already picked out within the double quotes?
When I see your sample string, I immediately think of Comma Separated Values in which the separating character is the space rather than a comma. That's why the middle two words are qualified (quoted) - because we need to escape the space contained within the value. The good news is that I have done a lot of CSV parsing in ColdFusion. So, to help you solve this problem, I have decided to take my CSVToArray() ColdFusion user defined function and port it to Javascript.
Unfortunately, it looks like Javascript does not support atomic grouping; as such, I had to take out some of Steve Levithan's awesome CSV parsing optimizations. But, optimizations aside, the simplified regular expression pattern seems to work fine. And, since we're not going to be parsing huge strings into data arrays, I don't foresee any real problems.
That said, here is the Javascript port of my CSVToArray() UDF:
Launch code in new window » Download code as text file »
- <script type="text/javascript">
-
- // This will parse a delimited string into an array of
- // arrays. The default delimiter is the comma, but this
- // can be overriden in the second argument.
- function CSVToArray( strData, strDelimiter ){
- // Check to see if the delimiter is defined. If not,
- // then default to comma.
- strDelimiter = (strDelimiter || ",");
-
- // Create a regular expression to parse the CSV values.
- var objPattern = new RegExp(
- (
- // Delimiters.
- "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +
-
- // Quoted fields.
- "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
-
- // Standard fields.
- "([^\"\\" + strDelimiter + "\\r\\n]*))"
- ),
- "gi"
- );
-
-
- // Create an array to hold our data. Give the array
- // a default empty first row.
- var arrData = [[]];
-
- // Create an array to hold our individual pattern
- // matching groups.
- var arrMatches = null;
-
-
- // Keep looping over the regular expression matches
- // until we can no longer find a match.
- while (arrMatches = objPattern.exec( strData )){
-
- // Get the delimiter that was found.
- var strMatchedDelimiter = arrMatches[ 1 ];
-
- // Check to see if the given delimiter has a length
- // (is not the start of string) and if it matches
- // field delimiter. If id does not, then we know
- // that this delimiter is a row delimiter.
- if (
- strMatchedDelimiter.length &&
- (strMatchedDelimiter != strDelimiter)
- ){
-
- // Since we have reached a new row of data,
- // add an empty row to our data array.
- arrData.push( [] );
-
- }
-
-
- // Now that we have our delimiter out of the way,
- // let's check to see which kind of value we
- // captured (quoted or unquoted).
- if (arrMatches[ 2 ]){
-
- // We found a quoted value. When we capture
- // this value, unescape any double quotes.
- var strMatchedValue = arrMatches[ 2 ].replace(
- new RegExp( "\"\"", "g" ),
- "\""
- );
-
- } else {
-
- // We found a non-quoted value.
- var strMatchedValue = arrMatches[ 3 ];
-
- }
-
-
- // Now that we have our value string, let's add
- // it to the data array.
- arrData[ arrData.length - 1 ].push( strMatchedValue );
- }
-
- // Return the parsed data.
- return( arrData );
- }
-
- </script>
I decided to go with the Regular Expression Exec() command rather than the Javascript String Replace method simply because I have done so much with the String Replace method lately. Don't worry too much about the regular expression itself - that stuff is meant to be written, not read.
With this new Javascript user defined function in place, let's set up a little test for text box input:
Launch code in new window » Download code as text file »
- <form>
-
- <p>
- <input type="text" value="" size="40"
- onblur="console.log( CSVToArray( this.value, ' ' ) );"
- />
- </p>
-
- </form>
Here, we are allowing the user to enter a value into the text box and onBlur of that input box, we are going to use FireBug to log the CSV parsed value of the entered text. When we run this on your sample data, we get the following:
| | | | | |
| |  | | ||
| | | |
As you can see from the Console, we get an array of arrays. Since there are no line breaks, the CSV array only contains one child array (one child array per row of data). From this, you can see that the first child array holds the three values you need to get at.
I hope this helps.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Newer Post
Hal Helms - Real World Object Oriented Development, Sarasota - On My Way
Older Post
Data Truncation: Truncated Incorrect DOUBLE Value When Updating Timestamp
Reader Comments
Couldn't you use the javascript .split() function here to break up the string into an array? It seems much simpler than the regex you suggested.
just an idea.
larry
@Larry,
The String.split() takes a regex for the delimiter. Seeing as the delimiter in our case (space) can be escaped by surrounding qutoes, I am not sure how doable it is. I am certainly open to am more simple solution though if you have thoughts.
I think that's pretty much the RegExp you need: 'website "content approval" new'.match(/^(\w+)\s?"(.*)"\s?(\w+)$/i); Outputs: ["website "content approval" new", "website", "content approval", "new"] You could use ".slice(1)" to pop the first element, which is obviously not needed. cheers
@H3, Most excellent post! I am not sure if I have ever used the String.match( regex ) function before. I will definitely have to take some more time to look into it. Much appreciated!
From my reading the split() function can take either a regex or a simple string. given your string website "content approval" new You'd want to to use " as the delimiter. Then your resulting array would be website content approval new The only difficult part would be parsing the delimiter, that's where the regex would come in. btw what the foo are kinky points? regards, larry
it looks like the blog app doesn't like returns, lets try that again<br/><br/> From my reading the split() function can take either a regex or a simple string. given your string <br/><br/>website "content approval" new<br/><br/> You'd want to to use " as the delimiter. Then your resulting array would be <br/><br/> website <br/><br/> content approval <br/><br/> new <br/><br/> The only difficult part would be parsing the delimiter, that's where the regex would come in. <br/><br/> btw what the foo are kinky points? <br/><br/> regards, larry
@Larry,
The Kinky Points are for the shop:
http://www.bennadel.com/shop/kinky-solutions-shop.htm
Hmm, I just made some commenting updates. They might be stripping out the line breaks. I will get to the bottom of this ASAP!
@Larry,
Thanks for helping me debug! I guess when I do an AJAX post vs. a standard FORM post the line breaks come through differently (even though they are both "POST" actions).
@Ben, Thank you heaps for this.
It has now put me on the right track :)
> @Larry,
> Thanks for helping me debug! I guess when I do an AJAX post
> vs. a standard FORM post the line breaks come through
> differently (even though they are both "POST" actions).
Any time Ben. Good to know you got things working again (of course until I post this I really have no clue that it will work properly).
larry
Ben, Very useful code! I had a problem when using it with IE where in the special case that the first character is the delimiter, the code fails to store an empty string representing the empty value preceeding the delimiter. So I addeed the following code right before your while loop and it took care of the problem:
if (strData.charAt(0) == strDelimiter)
arrData [arrData.length - 1].push ("")
@Steve,
Hmm, interesting. The algorithm should capture an empty field in the beginning. Were you getting this in IE only? Or on all browsers?
Haven't tried another browser. But in studying the regular expression, I could see how the regexp engine could match the first delimiter rather than the "^" and then match whatever followed--simply skipping over the empty field. I tried to move the "^" to the beginning of the expression, but then the engine skipped over what immediately followed the first delimiter in an attempt not to get stuck in a loop.
@Steve,
I think you are correct about the ^ being first. I think it was put at the end because it is not the "normal" delimiter. I'll have a play with it, see what I can do.
What if you wanted to parse a string that contained a delimiter, but except for circumstances where it was contained in quotes:
Eg. 'ChartHeight = 400;ChartName = "Test;";ChartWidth = 600';
Here, my delimiter is the colon, but I have quoted values that don't span the distance between actualy delimiters so the function above does not work. I want to get:
array[0] = 'ChartHeight = 400';
array[1] = 'ChartName = "Test;"';
array[2] = 'ChartWidth = 600';
Thanks,
Rob
@Rob,
When you call the function, are you passing in the delimiter you want to use:
CSVToArray( your_data, ";" )
Ben,
Yes, I'm passing in the colon as the delimiter parameter. The problem is that in your regular expression search, quoted values assume the quoted value is surrounded by delimiters. In this case, we have a quoted delimiter "Test;", but this is preceeded by 'ChartName = '. This results in:
[0] = "ChartHeight = 400"
[1] = "ChartName = "
[2] = ""
[3] = "ChartWidth = 600"
Regards,
Rob
How would I use this on an actual .CSV file?
This works:
var classList = loadFile("/KC/data/classes.csv");
var classes = CSVToArray( classList , "," );
function loadFile(url)
{
var req = false;
// branch for native XMLHttpRequest object
if (window.XMLHttpRequest && !(window.ActiveXObject))
{
try { req = new XMLHttpRequest() }
catch(e) { req = false }
}
else // branch for IE/Windows ActiveX version
{
if (window.ActiveXObject)
{
try { req = new ActiveXObject("Msxml2.XMLHTTP") }
catch(e) { try { req = new ActiveXObject("Microsoft.XMLHTTP") }
catch(e) { req = false }
}
}
}
if(req)
{
req.open("GET", url, false);
req.send("");
return req.responseText
}
return ''
}
@Rob,
Ahhh, I see. You are using single quotes as the field qualifier, not double quotes. I am not sure, but I believe that CSV uses double quotes as its standard (but I could be off on that).
I think all you have to do is change the double quotes to single quotes in the regular expression:
// Delimiters.
"(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +
// Quoted fields.
"(?:'([^']*(?:''[^']*)*)'|" +
// Standard fields.
"([^'\\" + strDelimiter + "\\r\\n]*))"
That might work.
Since the delimiter is space, you can replace it with ',' using a litle bit of regex: somestring.replace(/(^\s*|\s*$)/, ",");
And then use String.split() to accomplish the task. KISS.
I'm not actually working with typical CSV data. I'm working with a string where there are a number of name-value pairs separated by semicolons. I want to split the string by these semicolons, except where the semicolon is inside of double quotes. Does this make it more clear?
ok DB, ever heard of character escaping....first off, escape them from you're data, then it wont be such a brain smasher for you. now if you still cant get it, send it to me, i'll fix, and send it back to you, along with my bill ofcourse.
@Rob,
Try this:
var data = "ChartHeight = 400;ChartName = \"Test;\";ChartWidth = 600";
var values = data.match( new RegExp( "[^\";\r\n]+(\"[^\"]*\")*", "g" ) );
for( i in values ){
document.write( values[ i ] + "<br />" );
}
This gives you:
ChartHeight = 400
ChartName = "Test;"
ChartWidth = 600
