Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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

share|improve this question
2  
If you use a proper CSV parser e.g. in python or perl it'll be easier and more robust. –  Marco Oct 16 '13 at 18:41
    
Do you want YYYY/MM/DD as in your question or MM/DD/YYYY as in your code? –  terdon Oct 16 '13 at 18:50
    
@Marco I am new to Linux, so do not know many options, please suggest how can I work on this using a perl. my perl version is "perl, v5.8.8 built for x86_64-linux-thread-multi" –  Dhruuv Oct 16 '13 at 19:02
    
@terdon I am sorry, I need the output of the field as YYYY/MM/DD –  Dhruuv Oct 16 '13 at 19:03

4 Answers 4

up vote 4 down vote accepted

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"
share|improve this answer
    
any reason for printing the first line with additional fields? like this ","","","","","","","",""00/00/2000"","",? also, I am getting additional quotes for the field that was processed and also at the end of the record as in **,""10/11/2011"","12"",**... Please let me know how to fix this? –  Dhruuv Oct 16 '13 at 19:25
    
@Dhruuv - I'm attempting to cleanup your example code 8-). Those are because you're applying the substr to all the lines, and not just the 2nd line on, BTW. –  slm Oct 16 '13 at 19:50
    
@Dhruuv - see final update, works now. –  slm Oct 16 '13 at 19:52
    
It works great now... thank you so much for the help and sorry for the trouble :) –  Dhruuv Oct 16 '13 at 20:44

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.

share|improve this answer
    
I am new to Linux, dont mind me asking... Your code is printing the first line with additional fields? like this ","","","","","","","",""00/00/2000"","",? also, the month in the output is not the correct one. It is actually subtracting 1 month from the input month... please suggest what fix to be done for the code... –  Dhruuv Oct 16 '13 at 19:44
    
@Dhruuv sorry, my bad, I was doing the substitution on the first line which has no ". Try the updated code, it should work. –  terdon Oct 16 '13 at 19:56

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"
share|improve this answer
    
Thank you for the quick response... I tried to use apply your code, it gives the following error... **column1,column2,column3,column4,column5,column6,column7,Column8,Column9,Column‌​10 -e:4: uninitialized constant Date (NameError)**. Any suggestions? –  Dhruuv Oct 16 '13 at 19:29
    
You may need to add -rdate –  glenn jackman Oct 16 '13 at 19:32

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');
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.