Replace comma with space in just one field - from a .CSV file

Question

I have happened upon a problem with a program that parses through a CSV file with a few million records: two fields in each line has comments that users have put in, and sometimes they use commas within their comments. If there are commas input, that field will be contained in double quotes. I need to replace any commas found in those fields with a space. Here is one such line from the file to give you an idea -

1925,47365,2,650187016,1,1,"MADE FOR DRAWDOWNS, NEVER P/U",16,IFC 8112NP,Standalone-6,,,44,10/22/2015,91607,,B24W02651,,"PA-3, PURE",4/28/2015,1,0,,1,MAN,,CUST,,CUSTOM MATCH,0,TRUE,TRUE,O,C48A0D001EF449E3AB97F0B98C811B1B,POS.MISTINT.V0000.UP.Q,PROD_SMISA_BK,414D512050524F445F504F5331393235906F28561D2F0020,10/22/2015 9:29,10/22/2015 9:30

NOTE - I do not have the Text::CSV module available to me, nor will it be made available in the server I am using.

Here is part of my code in parsing this file. The first thing I do is concatenate the very first three fields and prepend that concatenated field to each line. Then I want to clear out the commas in @fields[7,19], then format the DATE in three fields and the DATETIME in two fields. The only line I can't figure out is clearing out those commas -

my @data;

# Read the lines one by one.

while ( $line = <$FH> ) {

    # split the fields, concatenate the first three fields,
    # and add it to the beginning of each line in the file

    chomp($line);
    my @fields = split(/,/, $line);

    unshift @fields, join '_', @fields[0..2];

    # remove user input commas in fields[7,19]

    $_ =                 for fields[7,19]; 

    # format DATE and DATETIME fields for MySQL/sqlbatch60

    $_ = join '-', (split /\//)[2,0,1] for @fields[14,20,23];
    $_ = Time::Piece->strptime($_,'%m/%d/%Y %H:%M')->strftime('%Y-%m-%d %H:%M') for @fields[38,39];

    # write the parsed record back to the file

    push @data, \@fields;
}

Answer 1

If it is ONLY the eighth field that is troubling AND you know exactly how many fields there should be, you can do it this way

Suppose the total number of fields is always N

• Split the line on commas ,
• Separate and store the first six fields
• Separate and store the last n fields, where n is N-8
• Rejoin what remains with commas ,. This now forms field 8

and then do what ever you like to do with it. For example, write it to a proper CSV file

Answer 2

Text::CSV_XS handles quoted commas just fine:

#!/usr/bin/perl
use warnings;
use strict;

use Text::CSV_XS qw{ csv };

my $aoa = csv(in => 'file.csv');  # The file contains the sample line.
print $aoa->[0][6];

Answer 3

Note   The two main versions below clean up one field. The most recent change in the question states that there are, in fact, two such fields. The third version, at the end, works with any number of bad fields.

All code has been tested with the supplied example and its variations.

---

Following clarifications, this deals with the case when the file need be processed by hand. A module is easily recommended for parsing .csv, but there is a problem here: reliance on the user to enter double quotes. If they end up not being there we have a malformed file.

I take it that the number of fields in the file is known with certainty and ahead of time.

The two independent solutions below use either array or string processing.

(1) The file is being processed line by line anyway, the line being split already. If there are more fields than expected, join the extra array elements by space and then overwrite the array with correct fields. This is similar to what is outlined in the answer by vanHoesel.

use strict;
use warnings;

my $num_fields = 39;  # what should be, using the example
my $ibad = 6;         # index of the malformed field-to-be
my @last = (-($num_fields-$ibad-1)..-1);  # index-range, rest of fields

my $file = "file.csv";
open my $fh, '<', $file;

while (my $line = <$fh>) {   # chomp it if needed
   my @fields = split ',', $line;
   if (@fields != $num_fields) {
       # join extra elements by space
       my $fixed = join ' ', @fields[$ibad..$ibad+@fields-$num_fields];
       # overwrite array by good fields
       @fields = (@fields[0..$ibad-1], $fixed, @fields[@last]);
   }   
   # Process @fields normally
   print "@fields";
}   
close $fh;

(2) Preprocess the file, only checking for malformed lines and fixing them as needed. Uses string manipulations. (Or, the method above can be used.) The $num_fields and $ibad are the same.

while (my $line = <$fh>) {
    # Number of fields: commas + 1  (tr|,|| counts number of ",")
    my $have_fields = $line =~ tr|,|| + 1;
    if ($have_fields != $num_fields) {   
        # Get indices of commas delimiting the bad field
        my ($beg, $end) = map {
            my $p = '[^,]*,' x $_; 
            $line =~ /^$p/ and $+[0]-1; 
        } ($ibad, $ibad+$have_fields-$num_fields);

        # Replace extra commas and overwrite that part of the string
        my $bad_field = substr($line, $beg+1, $end-$beg-1);
        (my $fixed = $bad_field) =~ tr/,/ /;
        substr($line, $beg+1, $end-$beg-1) = $fixed;
    }   
    # Perhaps write the line out, for a corrected .csv file
    print $line;
}   

In the last line the bad part of $line is overwritten by assigning to substr, what this function allows. The new substring $fixed is constructed with commas changed (or removed, if desired), and used to overwrite the bad part of the $line. See docs.

---

If quotes are known to be there a regex can be used. This works with any number of bad fields.

while (my $line = <$fh>) {
    $line =~ s/."([^"]+)"/join ' ', split(',', $1)/eg;  # "
    # process the line. note that double quotes are removed
}

If the quotes are to be kept move them inside parenthesis, to be captured as well.

This one line is all that need be done after while (...) { to clean up data.

The /e modifier makes the replacement side be evaluated as code, instead of being used as a double-quoted string. There the matched part of the line (between ") is split by comma and then joined by space, thus fixing the field. See the last item under "Search and replace" in perlretut.

All code has been tested with multiple lines and multiple commas in the bad field.