How to replace the first date in each line, but not the second?

Blarneystone · Jan 04, 2018#12018-01-04T17:36+00:00

Hi I have a CSV file that changes daily for export to a different system. Trying to figure out how to drop the "day" from the first instance it appears in a line, but not the second.

CSV example:

Code: Select all

Item#,date1,sold,date2,PO#
ItemA,12/18/2017,375,1/22/2018,77954
ItemB,12/18/2017,60,1/22/2018,77954
ItemC,12/18/2017,105,1/22/2018,77954

By dropping the day off the first date, the result would look like this:

Code: Select all

Item#,date1,sold,date2,PO#
ItemA,12/2017,375,1/22/2018,77954
ItemB,12/2017,60,1/22/2018,77954
ItemC,12/2017,105,1/22/2018,77954

Is there any way to do this with UltraEdit? Thanks in advance.

Ovg · Jan 04, 2018#22018-01-04T18:14+00:00

Search->Replace.

Find what: (?<=Item.,)(\d\d)/\d\d(/\d\d\d\d)
Replace with: \1\2
Regular Expressions: Perl
Check Replace all from top of file
Click replace all

fleggy · Jan 05, 2018#32018-01-05T12:22+00:00

The solution by Ovg is OK but I would prefer a little more robust pattern (we don't know the upper limit for Items):

F: ^(Item[^,]++,\d\d)/\d\d(/\d\d\d\d)
R: \1\2

BR, Fleggy

MickRC3 · Jan 05, 2018#42018-01-05T15:07+00:00

Is that correct for single digit months and days? The example given does not zero pad the fields.

Blarneystone · Jan 05, 2018#52018-01-05T15:39+00:00

Thanks all! This solved the problem.

Ovg · Jan 05, 2018#62018-01-05T15:40+00:00

Nice shot, fleggy!

MikeW · Jan 06, 2018#72018-01-06T20:06+00:00

MickRC3 wrote:Is that correct for single digit months and days? The example given does not zero pad the fields.

This works on both single/double digit days...

^(Item[^,]++,\d\d)/\d+(/\d\d\d\d)

Ovg · Jan 07, 2018#82018-01-07T06:42+00:00

MikeW wrote:^(Item[^,]++,\d\d)/\d+(/\d\d\d\d)

I think it will be better ^(Item[^,]++,\d\d)/\d{1,2}(/\d\d\d\d)

fleggy · Jan 07, 2018#92018-01-07T08:23+00:00

I think it will be even better ^(Item[^,]++,\d{1,2})/\d{1,2}(/\d\d\d\d)

Yet it is not the best one

MikeW · Jan 07, 2018#102018-01-07T20:35+00:00

fleggy wrote: I think it will be even better ^(Item[^,]++,\d{1,2})/\d{1,2}(/\d\d\d\d)
Yet it is not the best one

In the interest of understanding better...

For the following pattern:

Code: Select all

Item#,date1,sold,date2,PO#
ItemA,1/1/2017,375,1/22/2018,77954
ItemB,2/9/2017,60,1/22/2018,77954
ItemC,3/18/2017,105,1/22/2018,77954
ItemD,12/1/2017,375,1/22/2018,77954
ItemE,12/9/2017,60,1/22/2018,77954
ItemF,12/18/2017,105,1/22/2018,77954

Is this the most compact/efficient?

^(Item[^,]++,\d{1,2})/\d+

fleggy · Jan 08, 2018#112018-01-08T05:46+00:00

Hi MikeW,

the most compact and the most efficient patterns are very often mutually exclusive.
E.g. the pattern
^([^/\r]++)/\d+
is more compact but less efficient.
I think your pattern is the most efficient one (or very close to the ideal) assuming there are only lines matching this pattern in the text.

BR, Fleggy

Mofi · Jan 08, 2018#122018-01-08T06:04+00:00

Perhaps usage of ^Item[^,]++,\d{1,2}\K/\d+ or ^[^/\r]++\K/\d+ as search string and an empty replace string is most efficient for this replace. But I don't know it for sure because I have not analyzed it. \K is documented in Boost Perl Regular Expression Syntax. Note: \k has a different meaning.

MickRC3 · Jan 08, 2018#132018-01-08T17:53+00:00

Thanks for checking that for me. Normally when I'm not sure about a REGEX I would simply try it against test data. However our office just went through a hardware refresh and so far they haven't decided on if they will re-install UltraEdit for my team on our new computers.

Seems that a high level supervisor wants all of the offices to use IntelliJ IDEA instead of the various solutions we have now. I've been required to use it as a test and it really doesn't meet my needs.For one thing, the entire design is Java-centric but less than 1% of our code is Java. Also it is designed to work with projects and we usually work at the program level so it often incorrectly links programs together and thinks they are a project. I do a lot of software coding and support for applications written in IDL which is not supported by IntelliJ. The IT office said that IntelliJ does according to their documentation but I had to explain to them that our software is written in Interactive Display Language IDL and not the Interface Design Language IDL that IntelliJ supports.