Excel export - getting rid of line breaks

Forum|Forum|19 years ago
May 23, 2007
6 replies
1873 views

I'm exporting data to an Excel Spreadsheet but when the Excel spreadsheet is generated, if the user has used a carriage return during data entry, it creates a a new row in Excel.

I've tried using a replace in the export field
#replace(Comments,Chr(13),"", "all")#

and if I use:
#replace(Comments,Chr(13),"xx", "all")#
I can actually see the xx, but the extra row is still being created before the rest of the entry starts.

Any ideas on getting rid of the line breaks during export?

Full Code is below (edited for clarity).

<CFSET tab=chr(9)>
<CFSET carriage=chr(13)>
<cfif action IS "export">
<CFHEADER name="Content-Disposition" value="inline; filename=MNC_Data.xls">
<cfcontent type="application/vnd.ms-excel">
<cfquery name="get_Data" datasource="#appDSN#">
SELECT ml.DateSubmitted, ml.FirstName,
ml.LastName, ml.Address1,
ml.Address2, ml.City, ml.State, ml.comments,
FROM #diff#mailing ml
</cfquery>

<cfoutput>Location#tab#Date Submitted#
FirstName#tab#
LastName#tab#
Address1#tab#
Address2#tab#
City#tab#
State#tab#
Postal#tab#
Comments#carriage#</cfoutput>

<cfoutput query="get_Data">
#LSDateFormat(DateSubmitted,'MM/DD/YYYY')##tab##TimeFormat(timestamp,'hh:mm:ss tt') ##tab#
#FirstName##tab#
#LastName##tab#
#Address1##tab#
#Address2##tab#
#City##tab#
#State##tab#
#Postal##tab#
#replace(get_Data.Comments,Chr(13),"", "all")#
#carriage#</cfoutput>

This topic has been closed for replies.

A

Anonymous

Thats perfect! I did not know you could use pipes to separate, nor did I know you could use the actual characters you want to replace instead of the asci chr() tag.

It worked great. Thanks.

C

cf_dev2

Inspiring

Is it possible you're misunderstanding replace()?

C

chrome88Author

Inspiring

I have it working with MX7, but I have found that when I put in more than two characters to replace it seemed to stop working.

C

cf_dev2

Inspiring

That would only replace occurrences of all three characters in that order. If you want to replace any] of those three characters, try a reReplace

You must escape "." with a \ because it has a special meaning in regular expressions
reReplace(showname, "[ |-|\.]","","ALL")

C

chrome88Author

Inspiring

I believe you're correct, because if I do a replace Chr(10) with "x" I can see the x being output.

However, I'm not sure how to replace multiple characters,

I tried putting:

<cfset Comments = "#replace(get_data.Comments,Chr(10),"", "all")#">

before the output, but it does not affect the output.

thx,