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);

[download]

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

[download]

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

In reply to Complicated search and replace on a string by Doozer

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.