1

The second column are dates and I want it to be printed as DD.MM.YYYY. Please notice that the 4th line is a bit different.

89  150716  -50,00
101 170816  -50,00
123 141016  -100,00
142 91216   -100,00
19  140217  -100,00
38  230317  -50,00
50  200417  -50,00
65  260517  -50,00
77  190617  -50,00
95  200717  -50,00
106 150817  -50,00
120 150917  -50,00
164 171117  -100,00
10  150118  -100,00
  • 1
    See ISO 8601. The only unambiguous date format. It also sorts correctly. – cas Jan 17 '18 at 14:40
  • @cas Interesting, didn't know this. Not surprising that Excel can't handle this. That's why I'm using awk to sort/process my lists. – the_bookkeeper Jan 18 '18 at 9:45
  • I don't use Excel but I'd be very surprised if it couldn't format dates as YYYY-MM-DD. It's been a common date format for a very long time, and it has long been known that it sorts correctly. – cas Jan 18 '18 at 9:49
  • It can do YYYY-MM-DD but not YYYYMMDD or DDMMYY. I think I misunderstood you. – the_bookkeeper Jan 18 '18 at 10:16
5
awk '{ s = sprintf("%06d", $2); d = substr(s,1,2); m = substr(s,3,2); y = "20" substr(s,5,2); printf("%s%s%s.%s.%s%s%s%s", $1, OFS, m, d, y, OFS, $3, ORS) }' file.in
89 07.15.2016 -50,00
101 08.17.2016 -50,00
123 10.14.2016 -100,00
142 12.09.2016 -100,00
19 02.14.2017 -100,00
38 03.23.2017 -50,00
50 04.20.2017 -50,00
65 05.26.2017 -50,00
77 06.19.2017 -50,00
95 07.20.2017 -50,00
106 08.15.2017 -50,00
120 09.15.2017 -50,00
164 11.17.2017 -100,00
10 01.15.2018 -100,00

The awk code in a nicer form:

{
    s = sprintf("%06d", $2)

    d = substr(s,1,2)
    m = substr(s,3,2)
    y = "20" substr(s,5,2)

    printf("%s%s%s.%s.%s%s%s%s", $1, OFS, m, d, y, OFS, $3, ORS)
}

s is first set to the zero-filled six digit form of the second input field. d, m and y (day, month and year) are then extracted from this string and 20 is added as a prefix to the year part.

The printf then prints out the resulting record. OFS is the current output field separator (usually a space), and ORS is the output record separator (usually a newline).

To get tab-separated output:

awk -vOFS='\t' '{ ...as above... }' file.in
| improve this answer | |

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.