perl

Howto to convert all fields with 'MM/DD/YYYY HH:MM:SS AM/PM' format to 'YYYY/MM/DDTHH:MM:SS', in a csv flat-file.

Script written to transform many excel-export CSV-files (US locale) before import them into a SQLite database.

By default ';' is the separator fields in the CSV file. To change the behavior, call the script with one argument: the separator field.

cat input-file.csv | ./myscript.pl ',' > output-file.csv

Source:

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

use Time::Piece;

# "3/27/2018 11:22:38 AM"
my $mregex = '^"\d{1,2}\/\d{1,2}\/\d{1,4} \d{1,2}:\d{1,2}:\d{1,2} (AM|PM)"$';
my $separator = ';';
if ( $ARGV[0] ) {
   $separator = shift;
}
while (<>) {
   chomp;
   eval {
      local $SIG{__WARN__} = sub { };
      my @input = split( /${separator}/);
      my @output = map { if ( /${mregex}/ ) { Time::Piece->strptime($_, "\"%m/%d/%Y %T %p\"")->strftime("\"%Y-%m-%dT%H%M%S\"") } else { $_ } } @input;
      my $res = join( ${separator} , @output );
      print "$res\n";
   }
}
0;

Related Posts


Published

Category

software

Tags

Contact