• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1716
  • Last Modified:

How can I exclude unwanted line breaks from a .csv file?

I want to export data from my MySQL database into a .csv file and then import the .csv file into Excel.
One of the fields in my database is a comments field where the users have included line breaks.  When I currently export this into Excel using a .csv format I find that the comments field is split over many cells because of these linebreaks.

I have tried the REPLACE command on the query which calls up the data to be exported looking for CHR(10) & CHR(13)  and replacing them with CHR(34).  This results in an error message telling me that the database does not recognise the function CHR.

I have also tried replacing the linebreak with another character (a $), opening up the .csv file in Word, replacing the unwanted linebreaks in the comments field then replaceing my $ sign with a linebreak and then saving the .csv file and importing the new .csv into Excel.  This works but it is too clumsy to expect users to do this themselves.

What I need is either a method of removing the unwanted linebreaks before I export the file or else a method by which I can use Excel to import the .csv in the correct format.

Can anyone help?
2 Solutions
how exactly are you creating your csv file?

if you query your db first, then output the query into a <cfsavecontent> to create you csv content and then write a csv file using cffile, use the REPLACE() function on the comments column in the cfoutput, not in the query sql.

and, on a Window$ server, the linebreak is chr(13)&chr(10), NOT the other way around.
If you have some free formatted text data in your MySQL database fields (and it seems that you do), I would import into .tsv format, not .csv (can be imported to Excel just as easily) . But first, I would clean up all the carriage returns and tabs and such - just replace them all with single white-space character. So, output for each row, that you add to your .tsv file would be:

<cfoutput query="yourQuery">
#db_column_1#      #REReplaceNoCase(db_column_text_type, "[[:space:]]", " ", "all")#      #db_column_3#

(spaces between db fields are tab characters)
You can wrap the results in double quotes and it will not break them out like that.   So basicly the CSV will be quote comma delimited.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

I thought of using double quotes
BUT if it is free-form text input from the users (some sort of editorial contents) -
where is guarantee that there are no quotes inside the fields? I just think that stripping off all the extra white spaces and using .tsv format is the least painful solution in terms of putting work into preventing possible errors.
for TSV format, use #chr(9)# instead of putting actual literal tabs in the <cfsavecontent>

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now