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

Posted on 2008-11-04
Last Modified: 2012-05-05
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?
Question by:Nemetona
    LVL 27

    Accepted Solution

    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.
    LVL 8

    Assisted Solution

    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)
    LVL 4

    Expert Comment

    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.
    LVL 8

    Expert Comment

    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.
    LVL 27

    Expert Comment

    for TSV format, use #chr(9)# instead of putting actual literal tabs in the <cfsavecontent>
    LVL 10

    Expert Comment


    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now