[SOLVED] How to edit the lines in text file in Linux - format the date to YYYY-MM-DD and then grep the line by time period

Issue

Can anyone help to format this text file(YYYYMMDD) as a date formatted(YYYY-MM-DD) text file using bash script or in Linux command line? I am not sure how to start editing 23millon lines!!!

I have YYYYMMDD format textfile :-

3515034013|50008|20140601|20240730

and I want to edit like YYYY-MM-DD formatted text file(Only 3rd and 4th fields need to be changed for 23million lines):-

3515034013|50008|2014-06-01|2024-07-30

I Want to convert from YYYYMMDD formatted text file to the YYYY-DD-MM format and I want to get specific lines from the text file based on the time period after this file manipulation which is the end goal.

The end goal is to format the 3rd field and 4th field as YYYY-MM-DD and also want to grep the line by date from that formatted text file:- 03rd field is the start date and the 04th field is the end date Let's say for example I need, (01). The end date(04th field) before today i.e 2022-08-06 - all the old lines (02). The end date(04th field) is 2 years from now i.e lines in between 2022-08-06th <-> 2024-08-06th?

Please note:- There are more than a 23million lines to edit and analyze based on the date.

How to approach this problem statement? which method is time efficient awk or sed or Bash line-by-line editing?

Solution

Here's one way to change the format with awk:

awk '{$3=substr($3,1,4) "-" substr($3,5,2) "-" substr($3,7,2); $4=substr($4,1,4) "-" substr($4,5,2) "-" substr($4,7,2); print}' FS='|' OFS='|'

It should work given that

• | is only used for field separation
• all dates have the same format

You can pipe the transformed lines to a new file or change it in place. Of course you can do the same with sed or ed. I'd go for awk because you'd be able to extract your specific lines just in the same run to an extra file.

Answered By - steffen

Answer Checked By - Pedro (WPSolving Volunteer)