Formata um campo Date de um arquivo .CSV com várias vírgulas em um campo de string

Question

Eu tenho um arquivo .CSV (file.csv) cujos dados estão todos entre aspas duplas. O formato de amostra do arquivo é o seguinte:

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"

O nono campo é o campo de data no formato "DD-MMM-AA" . Eu tenho que convertê-lo para o formato YYYY / MM / DD . Eu estou tentando usar o código abaixo, mas sem uso.

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

A saída do arquivo temp_file.csv após a execução do código acima é mostrada abaixo.

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"

Tanto quanto eu entendo, a questão é com as vírgulas nas aspas duplas como o meu código está levando-os em consideração também ... Por favor, sugira sobre as perguntas abaixo:

1) A duplicação de todos os valores em todos os campos faz alguma diferença? Se eles fazem alguma diferença, como posso me livrar deles de todos os valores, exceto as cordas com vírgulas neles? 2) Qualquer modificação no meu código para poder formatar o 9º campo que no formato "DD-MMM-AAAA" para YYYY / MM / DD

    

Answer 1

Você está se dividindo em vírgulas, mas depois tem strings com vírgulas. Não pense que você está recebendo a 9ª coluna como a data. Inserir um print m depois dessa linha mostra o seguinte:

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

Exemplo

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"

Acho que você precisa repensar sua abordagem um pouco ou escapar de qualquer vírgula incluída em strings.

Uma correção

awk tem uma capacidade estranha mas útil de dividir em grupos de caracteres. Uma abordagem seria dividir em "," em vez de apenas as vírgulas.

Exemplo (refinamento # 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

Saída

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"

Mesmo isso não está certo. Você precisará fazer uma limpeza adicional para obter as cotações de volta e, em seguida, remover as cotações duplicadas no início e no final de suas seqüências de caracteres.

Exemplo (refinamento # 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 

Saída

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"",

Eu não vou continuar com esta abordagem, espero que você veja que não é uma maneira muito boa de resolver o problema e é forjada com problemas de manutenção e é muito frágil se qualquer uma das entradas mudar ao longo do tempo.

Exemplo (refinamento # 3)

OK, então eu não poderia simplesmente deixar isso, então aqui está um exemplo de trabalho.

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/,$//'

Saída

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

A maneira simples

Altere todas as ocorrências de DD-MMM-YYYY para YYYY/MM/DD , independentemente de onde elas foram encontradas:

$ 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"

O caminho preciso

Altere o formato apenas no 9º campo. Usando -a flag do perl que divide cada linha em campos (como awk , mas os campos são $F[0],$F[1]...$F[N-1] ) combinados com o -F que define o delimitador de campo para "," que você pode fazer:

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

Isso imprimirá AAAA / MM / DD e fará a suposição (como você faz na sua pergunta) de que todos os anos começam com 20 .

    

Answer 3

Use uma ferramenta com um analisador de CSV adequado. Por exemplo, com 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 4

Ah, eu não sabia que respostas não bash / awk / shell eram permitidas. Eu irei ecoar as recomendações para não usar o hackery shell para lidar com o CSV. Aqui está a minha solução perl. Este usa apenas módulos principais:

#!/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);

Se você remover citações de campos que não contenham vírgulas, será necessário fazer a seguinte alteração:

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