2

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.

5
  • post the input fragment of your file.csv Commented Nov 7, 2017 at 12:31
  • I think I understand what you're asking and I posted a solution, but this post is pretty difficult to parse. You might want to read about how to ask a good question.
    – igal
    Commented Nov 7, 2017 at 12:31
  • @igal I found it to be a very clear question.
    – fpmurphy
    Commented Nov 7, 2017 at 16:07
  • @fpmurphy1 Thanks! I rewrote it. ;)
    – igal
    Commented Nov 7, 2017 at 16:10
  • Does the grep actually have anything to do with this question? Please do not respond in comments; edit your question to make it clearer and more complete. Commented Nov 7, 2017 at 16:38

2 Answers 2

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
1

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
8
  • I am not sure this will grep year from previous century (e.g. 1999/09/13) as second digit must be either 0 or 1.
    – Archemar
    Commented Nov 7, 2017 at 15:41
  • @Archemar I thought that I had just translated your regular expression. Maybe there was a typo in your original post? Anyway, that's easy enough to fix - done!
    – igal
    Commented Nov 7, 2017 at 15:51
  • I am not the OP, and those regexp are a bit curious to match date ...
    – Archemar
    Commented Nov 7, 2017 at 15:55
  • @Archemar Whoops! Sorry, my mistake. And yes, I agree that the regular expressions aren't ideal, but that wasn't really the point of the post (and the OP also didn't say anything about needing to validate the dates) so I didn't want to get bogged down in that.
    – igal
    Commented Nov 7, 2017 at 15:59
  • 1
    While date is indeed a standard command, the -d option is not. Also note that standardly, paste needs at least one filename argument (here, you'd use -). The -P and -o options to grep are also not standard. Note that the behaviour of read depends on the current value of $IFS. There's nothing bash-specific in your command. It would also work with the standard sh utility Commented Nov 10, 2017 at 12:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .