A file with two columns looks like the following:

A file with two columns looks like the following:

There are two lines with the same DateTime entry 31.03.2024 05:00. I need to change the first one to 31.03.2024 04:00and leave the values in Column B unchanged. I can find it, but how to replace only the first one?

My trial to locate the correct area: ^[0-9]{1,2}.[0-9]{1,2}.[0-9]{4}\s05:00$\n^[0-9]{1,2}.[0-9]{1,2}.[0-9]{4}\s05:00$

Any advice is appreciated!

Rgds, DS

• regex
• multiline
• regexp-replace
• multilinestring

• what's the constraint on using regex to solve this? – njzk2 Commented Feb 10 at 18:39
• No constraint at all. I am open to any other suggestions! – DStruct Commented Feb 10 at 18:59
• Load it into an array, dataframe, database table, etc. and use the language's features for manipulating it. – Barmar Commented Feb 10 at 19:46
• Answering this probably needs knowledge of which regex version is being used. Different implementations have different ways of doing these more challenging operations. – AdrianHHH Commented Feb 10 at 19:54
• As was already asked, the regular expression engine version is needed to give an exact solution. However the process I'd use would be to look for the string you want and at the tail end of the regular expression add in a lookahead for the exact same string. As the lookahead would span multiple lines (you show the very next line is the repeated value, would that always be true?) the code for that depends on your regex version. The extra code would look something like (?=.+?\1) where \1 is the first group so you would want to include your code in () to make it the first group. – Terry R Commented Feb 11 at 2:36

2 Answers 2

These regex can be used that will match your line and replace with the alternate time. Since the replacement requires a capture group, they are language specific syntax to avoid capture group number conflicting next to a number in the replacement.

PCRE:

(?m)^([0-9]{1,2}\.[0-9]{1,2}\.[0-9]{4}\s*)(05:00)(?=.*\s+^\1\2)

replace ${1}04:00
https://regex101/r/Q5GG8B/1

Java:

(?m)^(?<g1>[0-9]{1,2}\.[0-9]{1,2}\.[0-9]{4}\s*)(05:00)(?=.*\s+^\1\2)

replace ${g1}04:00
https://regex101/r/YF1D7G/1

Python:

(?m)^([0-9]{1,2}\.[0-9]{1,2}\.[0-9]{4}\s*)(05:00)(?=.*\s+^\1\2)

replace \g<1>04:00
https://regex101/r/e9ZH1t/1

Dot-net:

(?m)^([0-9]{1,2}\.[0-9]{1,2}\.[0-9]{4}\s*)(05:00)(?=.*\s+^\1\2)

replace ${1}04:00
https://regex101/r/wjmKn5/1

Ahoy!

Not sure why you guys would want to write regexs so overcomplicated like that. Because you only want to change 31.03.2024 05:00 to 31.03.2024 04:00, why not make the regex exactly that?

s/31.03.2024 05:00/31.03.2024 04:00/

That will change the first occurrence. If you wanted to change both, make it a global search by adding the /g option to the end. I put the data in a file like this...

$ cat change.column.value.txt 
Column A            Column B
31.03.2024 01:00    1.002
31.03.2024 03:00    2.003
31.03.2024 05:00    3.007
31.03.2024 05:00    4.985
31.03.2024 06:00    2.987
--------            --------

I wrote my solution in Perl, the code looks like this...

#!/usr/bin/perl -w

undef $/; #read entire file at once, only change first instance
while(<>){
  s/31.03.2024 05:00/31.03.2024 04:00/;
  print;
}

Output looks like this...

$ perl change.column.value.pl change.column.value.txt
Column A            Column B
31.03.2024 01:00    1.002
31.03.2024 03:00    2.003
31.03.2024 04:00    3.007
31.03.2024 05:00    4.985
31.03.2024 06:00    2.987
--------            --------

If you wanted to create a new file with the new data you could run something like this...

$ perl change.column.value.pl change.column.value.txt > updated.column.value.txt

If you wanted to change the existing file, you could use the -i option like this...

$ perl -i change.column.value.pl change.column.value.txt

Golfed at 36 characters

$ perl -0777 -pe 's/31.03.2024 05:00/31.03.2024 04:00/' change.column.value.txt
Column A            Column B
31.03.2024 01:00    1.002
31.03.2024 03:00    2.003
31.03.2024 04:00    3.007
31.03.2024 05:00    4.985
31.03.2024 06:00    2.987
--------            --------

Good Luck!