-
Regular expression to replace certain characters between 2 other characters?
I'm working with some CSV data exported from Excel and need to clean it up so I can correctly convert it to array. The following works fine if there are double carriage returns, but if there are singles it breaks.
Code:
csv = csv.replace(/\s*((\r\n){2}|\n{2}|\r{2})/g, "<br><br>");
Works fine for
Code:
"image1.jpg
image2.jpg
image3jpg"
but not for
Code:
"image1.jpg
image2.jpg
image3jpg"
I can't globally include single carriage returns because those are defining the rows. What I really need is an expression that says:
replace all carriage returns that occur between quotes
In my case, I could include that the first quote is preceded by a comma ( ," ) because the first column of data is never an issue.
Is this possible?
Last edited by sirzooass; 08-06-2014 at 11:03 AM.
-
This actually turned out to be worse than expected because some Excel field have commas within them, but found the answer at the bottom of:
http://help.adobe.com/en_US/ActionSc...0204-7f00.html
To deal with those commas I use the following:
Code:
var str:String = ",\"test,test,test\",\"test,test,test\"";
trace("before",str);
str = str.replace(/(,\")(.*?)(\",*?)/g, replaceCommas);
function replaceCommas(matchedSubstring:String, capturedMatch1:String, capturedMatch2:String, capturedMatch3:String, index:int, str:String){
trace("capturedMatch1",capturedMatch2);
var newString = capturedMatch1 + capturedMatch2.replace(/,/g, "~") + capturedMatch3;
trace("return",newString);
return newString;
}
trace("after",str);
Then I deal with the carriage returns within the quotes, split into array on commas and change the ~s back to commas.
-
.
Hi,
You seemed to have replied to your first post before I could post this, but thius might help for the first, depending on how you got your data,
this example uses text file for testing.
PHP Code:
var myTextLoader:URLLoader = new URLLoader();
var csv:Array = new Array();
myTextLoader.addEventListener(Event.COMPLETE, onLoaded);
function onLoaded(e:Event)
{
csv = e.target.data.replace(/\s*((\r\n){2}|\n{1,4}|\r{1,4})/g, ",").split(",");
trace(csv);
trace(csv.length);
}
myTextLoader.load(new URLRequest("text.txt"));
text.txt
Code:
"image1.jpg
image2.jpg
image3jpg"
-
Thanks for your insight fruitbear!
Here's where I landed (assuming I don't hit another snag). This converts CSV data within quotes (exported this way by Excel because they contain commas and/or line breaks it seems) and makes it so they will remain individual array elements. Hopefully someone else will find this helpful.
Code:
var csv = csvLoader.data;
// remove spaces before commas
csv = csv.replace(/\s+?,/g, ",");
// process cells so they will wind up as individual array elements
csv = csv.replace(/(,\")(.*?)(\",*?)/gs, matchFunction);//s allows .*? to include newlines
function matchFunction(matchedSubstring:String, capturedMatch1:String, capturedMatch2:String, capturedMatch3:String, index:int, str:String){
trace("\r\rIN: ",capturedMatch2);
// convert commas to ~s
var newString = capturedMatch2.replace(/,/g, "~");
// convert lines breaks to html line breaks
newString = newString.replace(/((\r\n)+|\n+|\r+)/g, "<br><br>");
trace("\rOUT: ",newString);
return capturedMatch1 + newString + capturedMatch3;
}
// break csv into rows
csvData = csv.split(/\r\n|\n|\r/);
// process rows
for (var i:int=0; i<csvData.length; i++){
// break rows into array elements
csvData[i] = csvData[i].split(",");
// process cells
for (var j:int=0; j<csvData[i].length; j++){
// add back commas
csvData[i][j] = csvData[i][j].split("~").join(",");
// get rid of surrounding quotes
csvData[i][j] = csvData[i][j].replace(/\A"*(.*?)"*\Z/g, "$1");
}
trace("\rCSV",i,":",csvData[i]);
}
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|