Format a Date field of a .CSV file with multiple commas in a string field

Question

I have a .CSV file (file.csv) whose data are all enclosed in double quotes. Sample format of the file is as below:

column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10
"12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in between","4432","author1, name","890","88","11-OCT-11","12"
"4432","B000QRIGJ4","890","another, string with quotes, and with more than, two commas: in between","455","author2, name","12","455","12-OCT-11","55"
"11","B000QRIGJ4","77","string with, commas and (paranthesis) and : colans, in between","12","author3, name","333","22","13-OCT-11","232"

The 9th field is the date field in the format "DD-MMM-YY". I have to convert it to the format YYYY/MM/DD. I am trying to use the below code, but of no use.

awk -F, '
 BEGIN {
 split("JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC", month, " ")
 for (i=1; i<=12; i++) mdigit[month[i]]=i
 }
 { m=substr($9,4,3)
 $9 = sprintf("%02d/%02d/"20"%02d",mdigit[m],substr($9,1,2),substr($9,8,20))
 print
 }' OFS="," file.csv > temp_file.csv

The out put of the file temp_file.csv after executing the above code is as shown below.

column1,column2,column3,column4,column5,column6,column7,Column8,00/00/2000,Column10
"12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in between","4432","author1,00/00/2000,"890","88","11-OCT-11","12"
"4432","B000QRIGJ4","890","another, string with quotes, and with more than, two commas: in between","455",00/00/2002, name","12","455","12-OCT-11","55"
"11","B000QRIGJ4","77","string with, commas and (paranthesis) and : colans, in between","12","author3,00/00/2000,"333","22","13-OCT-11","232"

As far as I am understand, the issue is with the commas in the double quote as my code is taking them into consideration too... Please suggest on the below questions:

1) Does the double quoting all the values in all the fields make any difference? If they make any difference, how do I get rid of them from all the values except the strings with commas in them? 2) Any modifications to my code so I could format the 9th field which in the format "DD-MMM-YYYY" to YYYY/MM/DD

Answer 1

You're splitting on commas, but then have strings with commas. Don't think you're getting the 9th column as the date. Inserting a print m after this line shows as much:

m=substr($9,4,3)
print m

Example

MY M: lum
column1,column2,column3,column4,column5,column6, column7, Column8,00/00/2009, Column10
MY M: me"
"12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in between","4432","author1,00/00/2000,"890","88","11-OCT-11","12"
MY M: tho
"4432","B000QRIGJ4","890","another, string with quotes, and with more than, two commas: in between","455",00/00/2002, name","12","455","12-OCT-11","55"
MY M: me"
"11","B000QRIGJ4","77","string with, commas and (paranthesis) and : colans, in between","12","author3,00/00/2000,"333","22","13-OCT-11","232"

I think you need to re-think your approach a bit, or escape any commas that are included in strings.

A fix

awk has a strange but useful ability to split on groups of characters. One approach would be to split on "," instead of just the commas.

Example (refinement #1)

$ awk -F'","' '
 BEGIN {
 split("JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC", month, " ")
 for (i=1; i<=12; i++) mdigit[month[i]]=i
 }
 {
  if(NR==1){print}
  else{ m=substr($9,4,3); print "MY M: " m;
   $9 = sprintf("%02d/%02d/20%02d",mdigit[m],substr($9,1,2),substr($9,8,20))
  print
 } }' OFS="," file.csv

Output

MY M: 
column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10,,,,,,,,00/00/2000
MY M: OCT
"12,B000QRIGJ4,4432,string with quotes, and with a comma, and colon: in between,4432,author1, name,890,88,10/11/2011,12"
MY M: OCT
"4432,B000QRIGJ4,890,another, string with quotes, and with more than, two commas: in between,455,author2, name,12,455,10/12/2011,55"
MY M: OCT
"11,B000QRIGJ4,77,string with, commas and (paranthesis) and : colans, in between,12,author3, name,333,22,10/13/2011,232"

Even this isn't quite right. You'll need to do additional clean up on it to get the quotes back in and then remove the duplicate quotes at the beginning and the end of your strings.

Example (refinement #2)

$ awk -F'","' '
 BEGIN {
 split("JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC", month, " ")
 for (i=1; i<=12; i++) mdigit[month[i]]=i
 }
 { m=substr($9,4,3); print "MY M: " m;
 $9 = sprintf("\"%02d/%02d/20%02d\"",mdigit[m],substr($9,1,2),substr($9,8,20))
 for (i=1; i<=10; i++) printf("\"%s\",",$i); printf("%s\n","")
 /\"\"/ }' OFS="," file.csv 

Output

MY M: 
"column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10","","","","","","","",""00/00/2000"","",
MY M: OCT
""12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in between","4432","author1, name","890","88",""10/11/2011"","12"",
MY M: OCT
""4432","B000QRIGJ4","890","another, string with quotes, and with more than, two commas: in between","455","author2, name","12","455",""10/12/2011"","55"",
MY M: OCT
""11","B000QRIGJ4","77","string with, commas and (paranthesis) and : colans, in between","12","author3, name","333","22",""10/13/2011"","232"",

I'm not going to continue with this approach, hopefully you see that it's not a very good way to solve the problem and is wrought with maintenance issues and is very fragile if any of the inputs change over time.

Example (refinement #3)

OK so I couldn't just leave this, so here's a working example.

awk -F'","' '
 BEGIN {
 split("JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC", month, " ")
 for (i=1; i<=12; i++) mdigit[month[i]]=i
 }

 { if (NR==1){print; next} }
 { m=substr($9,4,3)
 $9 = sprintf("%02d/%02d/20%02d",mdigit[m],substr($9,1,2),substr($9,8,20))
 for (i=1; i<=10; i++) printf("\"%s\",",$i); printf("%s\n","")
 }' OFS="," file.csv | sed -e 's/""/"/g' -e 's/,$//'

Output

column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10
"12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in between","4432","author1, name","890","88","10/11/2011","12"
"4432","B000QRIGJ4","890","another, string with quotes, and with more than, two commas: in between","455","author2, name","12","455","10/12/2011","55"
"11","B000QRIGJ4","77","string with, commas and (paranthesis) and : colans, in between","12","author3, name","333","22","10/13/2011","232"

Answer 2

Use a tool with a proper CSV parser. For example, with ruby:

ruby -rcsv -pe '
  if $. > 1
    row = CSV.parse_line($_)
    row[8] = Date.parse(row[8]).strftime("%Y/%m/%d")
    $_ = row.to_csv(:force_quotes=>true)
  end
' file.csv

column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10
"12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in between","4432","author1, name","890","88","2011/10/11","12"
"4432","B000QRIGJ4","890","another, string with quotes, and with more than, two commas: in between","455","author2, name","12","455","2011/10/12","55"
"11","B000QRIGJ4","77","string with, commas and (paranthesis) and : colans, in between","12","author3, name","333","22","2011/10/13","232"

Answer 3

The simple way

Change all occurrences of DD-MMM-YYYY to YYYY/MM/DD irrespective of where they're found:

$ perl -pe 'BEGIN{ @month=qw(JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC); 
                for ($i=1; $i<=12; $i++) {$mdigit{$month[$i]}=$i;}
               } 
          s#(\d{1,2})-(\w{3})-(\d{2,4})#20$3/$mdigit{$2}/$1#;' foo.csv

column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10
"12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in between","4432","author1, name","890","88","2011/9/11","12"
"4432","B000QRIGJ4","890","another, string with quotes, and with more than, two commas: in between","455","author2, name","12","455","2011/9/12","55"
"11","B000QRIGJ4","77","string with, commas and (paranthesis) and : colans, in between","12","author3, name","333","22","2011/9/13","232"

The precise way

Only change the format if in the 9th field. Using perl's -a flag which splits each line into fields (like awk, but the fields are $F[0],$F[1]...$F[N-1]) combined with the -F which sets the field delimiter to "," you can do:

perl -F'\",\"' -lane 'BEGIN{
               @month=qw(JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC); 
               for ($i=1; $i<=12; $i++) {$mdigit{$month[$i]}=$i;}
              } 
              $F[8]=~s#(\d{1,2})-(\w{3})-(\d{2,4})#20$3/$mdigit{$2}/$1# if $.>1; 
              print join("\",\"",@F)' foo.csv

This will print YYYY/MM/DD and makes the assumption (as you do in your question) that all years start with 20.

Answer 4

Ah, I didn't know that non bash/awk/shell answers were allowed. I'll echo the recommendations not to use shell hackery to deal with CSV. Here's my perl solution. This one only uses core modules :

#!/usr/bin/perl
# The 9th field
# convert DD-MMM-YY to  YYYY/MM/DD.
# using only perl core modules

use warnings;
use strict;
use diagnostics;

use Text::ParseWords;
use Time::Piece;

my $csvfile = "file.csv";
my $csvfilenew = "file_new.csv";
my $line   = ();
my @fields = ();

open( FILE, "<$csvfile" )
  or die("Couldn't open CSV file $csvfile:$!\\n");
open( OUTFILE, ">>$csvfilenew" )
  or die("Couldn't open new CSV file $csvfilenew:$!\\n");

while ( $line = <FILE> ) {
    my @fields = quotewords( ',', 1, $line );

    if (index($line, "column1") != -1) {
    print "skipping first line - doesn't contain dates to parse!\n";
    next;
    }
# DD-MMM-YY to YYYY/MM/MM
# The strftime man page describes all of the date string variables
    my $date = Time::Piece->strptime($fields[8], '"%e-%b-%y"');
    $fields[8] = $date->strftime('"%Y/%m/%d"');

    print OUTFILE join( ',', @fields );

}
close (FILE);
close (OUTFILE);

If you remove quotes from fields that don't contain commas, then you'll need to make the following change :

    my $date = Time::Piece->strptime($fields[8], '%e-%b-%y');
    $fields[8] = $date->strftime('%Y/%m/%d');

Answer 5

Using Miller (mlr) to clean up the whitespace (some headers seems to have spaces in them), and then to convert the dates in the Column9 field to the correct format. The date conversion is done by first converting the given dates to Unix time with strptime() and then immediately reformatting them to the wanted format using strftime().

mlr --csv \
    clean-whitespace then \
    put '$Column9 = strftime(strptime($Column9, "%d-%b-%y"), "%Y/%m/%d")' file

Result given the data in the question:

column1,column2,column3,column4,column5,column6,column7,Column8,Column9,Column10
12,B000QRIGJ4,4432,"string with quotes, and with a comma, and colon: in between",4432,"author1, name",890,88,2011/10/11,12
4432,B000QRIGJ4,890,"another, string with quotes, and with more than, two commas: in between",455,"author2, name",12,455,2011/10/12,55
11,B000QRIGJ4,77,"string with, commas and (paranthesis) and : colans, in between",12,"author3, name",333,22,2011/10/13,232

Would you want to quote all fields, add the option --quote-all immediately after --csv on the command line. By default, Miller only quotes the fields that actually require quoting.

---

More nicely formatted:

+---------+------------+---------+-------------------------------------------------------------------------+---------+---------------+---------+---------+------------+----------+
| column1 | column2    | column3 | column4                                                                 | column5 | column6       | column7 | Column8 | Column9    | Column10 |
+---------+------------+---------+-------------------------------------------------------------------------+---------+---------------+---------+---------+------------+----------+
| 12      | B000QRIGJ4 | 4432    | string with quotes, and with a comma, and colon: in between             | 4432    | author1, name | 890     | 88      | 2011/10/11 | 12       |
| 4432    | B000QRIGJ4 | 890     | another, string with quotes, and with more than, two commas: in between | 455     | author2, name | 12      | 455     | 2011/10/12 | 55       |
| 11      | B000QRIGJ4 | 77      | string with, commas and (paranthesis) and : colans, in between          | 12      | author3, name | 333     | 22      | 2011/10/13 | 232      |
+---------+------------+---------+-------------------------------------------------------------------------+---------+---------------+---------+---------+------------+----------+