Home / ubuntu / Questions / 1285475
In Process
Vic
Asked: 2020-10-21 14:46:42 +0800 CST

How do you change date format in ubuntu

  • 772

The time manager in Qgis demands date in YYYY-MM-DD format. I have a large text file with the dates in DD/MM/YYYYY format. It's too large to manually change format. Is there any way of automating this process? I have tried altering format in Libre office but it still stays the original format.The date format required is 2020-01-16. Human name fields removed.

Record ID   English Name    Grid Reference  Latitude    Longitude   Site Name   Abundance   Record Type Date    Comments    Time of sighting    Squirrel ID Weight (g)  CRST Comments
7765235 Red Squirrel    SJ0369952368    53.059853   -3.4384031  Clocaenog: Bron Branog: Location 10 1 Adult     01/10/2020      13:09           
7765230 Red Squirrel    SJ0367552319    53.059408   -3.4387464  Clocaenog: Bron Branog: Location 9  1 Adult     01/10/2020      16:54           
7761554 Red Squirrel    SH9856552277    53.058084   -3.5149526  Clocaenog: Pentre-llyn-cymmer: Location 5   1       01/10/2020      17:22           
7761417 Red Squirrel    SJ0454252299    53.059384   -3.4258081  Clocaenog: Main Block East: Location 15 1       01/10/2020      16:36           
7761416 Red Squirrel    SJ0454252299    53.059384   -3.4258081  Clocaenog: Main Block East: Location 15 1       01/10/2020      08:51           
7765217 Red Squirrel    SJ0357152125    53.057646   -3.4402395  Clocaenog: Bron Branog: Location 7  1 Adult     02/10/2020      13:42           
7765210 Red Squirrel    SJ0354052072    53.057164   -3.440686   Clocaenog: Bron Branog: Location 5  1 Sub-adult     02/10/2020      16:07           
7765209 Red Squirrel    SJ0354052072    53.057164   -3.440686   Clocaenog: Bron Branog: Location 5  1 Adult     02/10/2020  Patchy squirrel 13:54
libreoffice

4 Answers

  1. Not knowing anything about the structure/look of your textfile I'll pretend it only has one column, the date field:

    $ awk '{a=gensub(/(..)\/(..)\/(....)/,"\\3-\\2-\\1","1",$0); print a}' file.txt
2020-02-01

    To put that into a file (rather than on screen) redirect the output:

    $ awk '{a=gensub(/(..)\/(..)\/(....)/,"\\3-\\2-\\1","1",$0); print a}' file.txt  >new_text_file

    Where file.txt is to be replaced with the name of your file.

    • 0

  2. If you want to reformat date strings in free-form text, I'd suggest using Perl since it allows you to combine sed-style s/pattern/replacement/ regex search-and-replace with Unix-style strptime and strftime. Ex.

    perl -MTime::Piece -pe '
  s;\d{4}-\d{2}-\d{2};Time::Piece->strptime($&,"%Y-%m-%d")->strftime("%d/%m/%Y");ge
' yourfile

    For columnar data, I'd probably choose Miller instead - it has a builtin strptime/strftime that you can apply on a fieldwise basis.

    • 0

  3. The date command converts dates to whatever you dictate...

    date -d "10/28/2020" +%Y-%m-%d
    2020-10-28

    .
    .

    If there is only dates in the file thedates.txt
10/28/2020
10/29/2020
11/27/2021
12/20/2020
5/6/1999
11/6/73
11/11/2011

    ...then the file can be read with the date command date -f thedates.txt +%Y-%m-%d

    2020-10-28
2020-10-29
2021-11-27
2020-12-20
1999-05-06
1973-11-06
2011-11-11 
2020-10-21<---- and if you unintentionally read something like a blank line at the end of the file.. it will translate that to today's date... dont leave blanks or parse them out.

    If there is more than just dates in the file(likely), then you need to parse thedates.txt file with what I fear would be a for statement and dropping them into a variable and then running the date command on the $variable with the -d flag as above, then sending the formatted output back to the desired location....

    This makes the conversion extremely simple, but the parsing makes it lumpy...
    ...at least for someone with my regex/scripting clumsiness. Depending on the difficulty of the not yet seen sample data, this may assist another answer if nothing else.

    • 0

  4. In Calc insert new date column, format column as YYYY-MM-DD. Highlight/copy original date column but when pasting choose "Special", "Paste as unformatted text". It then stays in desired format so you can delete original (wrongly formattted) date column. Thank you Wu-Tang for pointing me in right direction.

    • 0