CSV File Date Formatting

Question

Here is the short version of my question: how do you convert comma-separated lists of dates from one format to another? More specifically, I'd like a single one-line command which converts strings of this form:

YYYY/MM/DD,YYYY/MM/DD

to strings of the following form:

DD/MM/YYYY,DD/MM/YYYY

Now I'll describe the context for my question.

I have a CSV file whose rows contain pairs of adjacent dates in the following format:

YYYY/MM/DD

I run the following grep command to extract the pair of dates:

grep -Po '[1-2][0-1][0-9][0-9]/[0-1][0-9]/[0-1][0-9]','[1-2][0-1][0-9][0-9]/[0-1][0-9]/[0-1][0-9]' file.csv`

This results, for example, in strings such as the following:

2016/05/16,2017/06/15

I am able to convert a single date-string using the date command as follows:

date -d '2016/05/16' '+%d/%m/%Y'

This produces the desired result:

16/05/2016

I tried applying this command to multiple input strings, e.g.:

date -d"2016/05/16","2017/06/15" "+%d-%m-%Y"

But that didn't work. I received the following error message:

Error :- Invalid date - 2016/05/16,2017/06/15'

What I want is a single command which will convert 2016/05/16,2017/06/15 to 16/05/2016,15/06/2017.

Answer 1

Using sed ...

echo "2016/05/16,2017/06/15" | sed 's/\([0-9]\{4\}\)\/\([0-9]\{2\}\)\/\([0-9]\{2\}\),\([0-9]\{4\}\)\/\([0-9]\{2\}\)\/\([0-9]\{2\}\)/\3\/\2\/\1,\6\/\5\/\4/g'
16/05/2016,15/06/2017

Answer 2

Here is a solution which extends your use of the date -d command. It only uses shell-scripting and the standard GNU command-line tools tr and paste:

tr ',' '\n' \
| while read line; do date -d "${line}" "+%d/%m/%Y"; done \
| paste -d, -s -

We use tr to convert the comma to a newline, splitting the one line of input into two. Then we pipe these two lines to a while loop and apply the date formatting command to each line. Finally, we pipe the converted date-strings to paste in order to recombine them into a single comma-separated list.

Here is what it looks like with your example input:

echo 2016/05/16,2017/06/15 \
| tr ',' '\n' \
| while read line; do date -d "${line}" "+%d/%m/%Y"; done \
| paste -d, -s -

And here is the output it produces:

16/05/2016,15/06/2017

Combining this with the grep command in your post, we get the following:

grep -Po '[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-1][0-9]','[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-1][0-9]' file.csv \
| tr ',' '\n' \
| while read line; do date -d "${line}" "+%d/%m/%Y"; done \
| paste -d, -s -

You might also want to shorten the regular expression that you're using with grep. Maybe something like this will also work (note that it matches a trailing comma):

grep -Po '([12][0-9]{3}(/[01][0-9]){2},?){2}' file.csv

Finally, we can use output redirection to write the results to a file:

grep -Po '[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-1][0-9]','[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-1][0-9]' file.csv \
| tr ',' '\n' \
| while read line; do date -d "${line}" "+%d/%m/%Y"; done \
| paste -d, -s - \
> new_file.csv