Regular expression to replace certain characters between 2 other characters?

[sirzooass]

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.

csv = csv.replace(/\s*((\r\n){2}|\n{2}|\r{2})/g, "<br><br>");

Works fine for

"image1.jpg

image2.jpg

image3jpg"

but not for

"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?

[sirzooass]

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:

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.

[fruitbeard]

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.

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

"image1.jpg

image2.jpg
image3jpg"

[sirzooass]

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.

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]);
}