3

I have a program that outputs data in the format

date, time,field1,field2,field3,fieldn
12/20/14,08:01:53,318.622,0.93712,21.6867,1.1089

the file has many columns which all need to stay the same

The date format is US, however I need non-US ie

date, time,field1,field2,field3,fieldn,....
20/12/14,08:01:53,318.622,0.93712,21.6867,1.1089,....

What's the easiest way to achieve this? I did a search and there are some examples but not quite my situation. Also looked into the date command, and awk and sed but I don't know enough to make a command. For example this answer using awk looked good, but doesn't do anything on my file.

I'm using Mac so would need commands that work for the versions OSX give.

3
sed -E 's,^([0-9]+)/([0-9]+),\2/\1,'

Explanation

  • sed -E: use sed with extended regular expressions so we don't have to escape the (), etc.
  • s/foo/bar/: this is the general sed syntax to search for foo and replace it with bar. Here, I've used , instead of /, because there are /s in the expression, and this will simplify it (again avoiding confusing escaping. Hence, s,foo,bar,.
  • ^([0-9]+)/([0-9]+): search for the beginning of the line ^, followed by one or more digits [0-9]+, and put that in a capturing group (). This is the month. This is followed by / and again one or more digits in another capturing group (i.e. the day).
  • \2/\1: replace this with the second capturing group (the day) followed by /, followed by the first capturing group (the month).

Usage

You can pipe your file into this command, i.e. <your_command_here>| sed…, or you can run it directly on your file, i.e. sed… file.txt. This will output directly to the command line. To write to a new file append > output.txt to the command.

  • Thank you! And for the explanation! – Steve Shiny Jul 13 '19 at 5:28
  • @SteveShiny You are welcome! And welcome to U/L! FWIW on the StackExchange sites, you can say thank you by upvoting with the up arrow on the left. If the answer has solved your question, you can accept it by clicking the tick when possible. Cheers! – Sparhawk Jul 13 '19 at 5:30
  • P.S. @SteveShiny Is your name and avatar a Steve Smith and sandpapergate reference? – Sparhawk Jul 13 '19 at 5:32
  • actually no, but but I might claim so in the future :-) PS I upvoted your answer, however I have <15 rep so it doesn't show – Steve Shiny Jul 15 '19 at 3:35
  • Thanks @SteveShiny. – Sparhawk Jul 15 '19 at 9:25
0

I'd suggest while you're reformatting the date, you use a standard (ISO) format: YYYY-mm-dd

perl -MTime::Piece -F, -lane '
  if ($.>1) {$F[0] = Time::Piece->strptime($F[0], "%m/%d/%y")->strftime("%F")}
  print join ",", @F
'

Also, while you're thinking about non-local datetimes, what timezone does that timestamp belong in? Is it UTC?

  • jackson: well, each line references an event which can take place anywhere, and will be localised to the local TZ. Keeping is local is OK – Steve Shiny Jul 15 '19 at 3:38

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.