Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

Complicated search and replace on a string

by Doozer (Scribe)
on May 17, 2017 at 14:07 UTC ( #1190466=perlquestion: print w/replies, xml ) Need Help??
Doozer has asked for the wisdom of the Perl Monks concerning the following question:

I have been searching for a solution to my problem for a while now and can't seem to find a suitable answer that does what I need.

I am working with a csv file and parsing it line by line. The file has been generated in MS Excel and I want to ingest the contents in to a Linux hosted Mysql database. It is easy enough for me to create an array from the line string by using split e.g.

my @bits = split(',',$line);

The process trips up when it reaches a line that has a comma within the contents of what would be a cell. I can see via the nano text editor that when this occurs, the contents of the cell is contained within double quotes. An example would be the line below:

Zone1 - Box6,Initial Condition Review,"Box locked on current channel, +No control - Rebooted",,30 November 2015,,"Box locked on current chan +nel, No control - Rebooted",29/11/2015,,,,Auto,,02/08/2015

The above example shows there are 2 cells that would contain a comma and have therefore got their content in double quotes. Although the content in both the cells in the above example are the same, this will not be the case for every line.

Ideally what I want to do is perform a search and replace on the line before putting it through the split. Double quotes can be removed and the comma within those double quotes would be changed to a dash (-). IT WOULD ALSO BE POSSIBLE TO HAVE MORE THAN ONE COMMA BETWEEN THE QUOTES.

I'm not sure how I could do a multiple search and replace on the line. Each match would need to retain the original content apart from the double quotes and the commas

Replies are listed 'Best First'.
Re: Complicated search and replace on a string
by Corion (Pope) on May 17, 2017 at 14:10 UTC

    Simply use Text::CSV_XS, which knows about quoted fields and handles them properly.

      Thanks! I never even thought to look for a csv parser (facepalm).

      That module does the trick and saves me a lot of work. It's clear now I was searching for the wrong thing.

        In the Perl environment, you should always look for a CPAN module first.   Q: “What is all the Perl-language fuss about?”   A: “CPAN!”

        Modules thousands of them to do just about anything imaginable.   All installed on your system, and self-tested on your system, in a matter of seconds.   It is almost never the right thing to do, to “roll your own.”   Leverage CPAN heavily.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1190466]
Approved by toolic
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (3)
As of 2018-11-17 00:27 GMT
Find Nodes?
    Voting Booth?
    My code is most likely broken because:

    Results (200 votes). Check out past polls.

    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!