How to replace special characters with the newline from CSV file?

Posted on 2007-09-28
Last Modified: 2011-09-20
I have a field called MessageBody in my CSV file and I would like to keep the format of that field.
I use to perl to parse data file into a CSV file and because of the CSV file, I had to replace the newline with the special character, `_` in my case.

What I want to do is import the CSV file into the SQL database.
I open the CSV in EXCEL to see I parse the data correctly in PERL.

From Excel, I try to use find/replace function to find `_` but it didn't go.

Basically I want to restore the newline in my CSV file to keep the format when I import into the SQL sever.

Any idea if I can do this.
Thanks much.
Question by:dkim18
    LVL 17

    Accepted Solution


    You can do this with Excel VBA.  I'm not sure if you need just a Carriage Return or Carriage Return and Line Feed, but here are three examples offering each.

    'Replace _ with Carriage Return and Line Feed
        ActiveSheet.Cells.Replace What:="_", Replacement:=vbCrLf, LookAt:=xlPart

    'Replace _ with Carriage Return
        ActiveSheet.Cells.Replace What:="_", Replacement:=vbCr, LookAt:=xlPart

    'Replace _ with Line Feed
        ActiveSheet.Cells.Replace What:="_", Replacement:=vbLF, LookAt:=xlPart

    Please let us know if you have any further questions.

    Kindest Regards,

    LVL 9

    Assisted Solution

    Couldn't understand fully what do you want, but I think there is something which can be of help to you.
    Download it and install it. It will add a new menu Asap-Utilities to the menu bar.
    Now use
    Asap_Utilities-->Text-->Advance Character Removal / Replace.

    That should do the job.

    LVL 9

    Expert Comment

    If i get you correct and depending on how your perl script is written, i think it would be better to tackle the problem at the perl script, instead of doing extra just to re-instate that new line.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    728 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

    19 Experts available now in Live!

    Get 1:1 Help Now