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