+ Start a Discussion
JSpenceJSpence 

Newline Apex problem

I'm trying to construct a csv file from datafields and email it to a person. This isn't a problem and works perfectly.

 

The problem I'm having is when excel reads the csv file and trys to interpret the new lines or carriage returns, it spits out boxes where a carriage return appears: ▖

 

I have tried stripping/trimming out all extra whitespace whilst maintaining the newlines with no luck. 

 

Here are some code samples:

cleanString = '\"'+dirtyString.replaceAll('[\n\r]', '').trim()+'\"'; //works but doesn't maintain one newline

 

cleanString = '\"'+dirtyString.replaceAll('\\r', '')+'\"'; //doesn't work

 

String [] array = dirtyString.split('[\n\r]');
for (Integer i = 0; i < address.size(); i++) {
	cleanString = cleanString+address[i].trim()+'\n';
}//doesn't work, still has boxes after every new line

 

 

The Email attachment object is:

Blob b = blob.valueOf(csv);
Messaging.EmailFileAttachment efa = new Messaging.EmailFileAttachment();
efa.setFileName('Details.csv');
efa.setContentType('text/csv');
efa.setBody(b);
mail.setFileAttachments(new Messaging.EmailFileAttachment[] {efa});

 where 'csv' is the comma separated string.

 

Any ideas anyone? 

Best Answer chosen by Admin (Salesforce Developers) 
JSpenceJSpence

The solution I went for was splitting on newlines and putting them in different cells:

 

String [] streetA = street.split('[\n\r]');
String street1 = streetA[0], street2 = '', street3 = '';
			
for (Integer i = 0; i < streetA.size(); i++) {
	if (streetA.get(i).equals('')) {
		streetA.remove(i);
		i--; //Decrement counter since we removed an empty item from the list
	}
}
if (streetA.size() == 2) {
	street2 = streetA[1];
} else if (streetA.size() == 3) {
	street2 = streetA[1];
	street3 = streetA[2];
}

 

Its not the prettiest of solutions but its better than having new lines in a cell.