4

I've got a series of CSV files whose second field is dates:

R.OUSE BOURTON MILL,2000-03-22T14:50:00,Ammoniacal Nitrogen as N,0.03,mg/l

How can I change the date format into DD/MM/YYYY for all files? The measurement time is not needed.

Thank you so much

  • Do you need to use bash, cut or a script as you wrote/tagged? – dessert Oct 19 at 22:34
  • I can only use the Bash command line (Mac), a script would be better because I can loop through all the CSV files. Don't necessarily need to use "cut", just thought it could be useful – Nicola Gambaro Oct 19 at 22:37
  • I removed the cut command tag since it has little to do with your requirements except perhaps cutting the time out of the date field. – WinEunuuchs2Unix Oct 19 at 23:00
6

With miller, using its builtin strptime and strftime functions :

$ mlr --fs ',' --ocsvlite --headerless-csv-output put '
    $2 = strftime(strptime($2,"%Y-%m-%dT%H:%M:%S"),"%d/%m/%Y")
  ' file.csv
R.OUSE BOURTON MILL,22/03/2000,Ammoniacal Nitrogen as N,0.03,mg/l
2

Here's a reasonable solution using bash and date:

#!/bin/bash

IFS=,
while read -r line; do
    fields=($line) # Split string into fields
    fields[1]=$(date --date="${fields[1]}" +%d/%m/%Y) # Transform the second date
    echo "${fields[*]}" # Reassemble fields
done <file.csv >>output.csv
  • It's a nice solution but shouldn't echo be going to a new output filename? – WinEunuuchs2Unix Oct 20 at 15:22
  • I would put it on the loop instead < file.csv >> output.csv – bac0n Oct 20 at 17:48
  • @bac0n Sounds good yeah – Silvan Mosberger Oct 21 at 1:28
1

Why bash?

From command line:

sed -E 's,([0-9]{4})-([0-9]{2})-([0-9]{2}),\3/\2/\1,g' test

Result:

R.OUSE BOURTON MILL,22/03/2000T14:50:00,Ammoniacal Nitrogen as N,0.03,mg/l
1

Match and rearrange time components with awk gensub.

$ awk 'BEGIN {FS=OFS=","} {$2=gensub(/(.+)-(.+)-(.+)T.*/,"\\3/\\2/\\1","g",$2)} 1' file.csv # >> output.csv

(nawk version):

$ awk 'BEGIN {FS=OFS=","} {split(substr($2,0,10),a,"-"); $2=sprintf("%s/%s/%s",a[3],a[2],a[1])} 1' file.csv # >> output.csv

if the intention is to only convert the time format, then epoch is probably a better choice.

#!/usr/bin/awk -f
#
# example.awk
#
BEGIN {
    FS=OFS=","
} {
    split(substr($2,0,10),a,"-")
    $2=mktime(sprintf("%s %s %s %s %s %s",a[1],a[2],a[3],00,00,00))
} 1
$ awk -f example.awk file.csv # >> output.csv

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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