[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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

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.
2 Solutions

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,

Kanwaljit DhunnaCACommented:
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.

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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