Remove CRLN *if* next line DOESN'T start with particular character (trying again)
-
(Apologies for deleting the first post - I’d made a mistake that I didn’t see before the 3 minute editing window closed.)
So, I’ve exported data out of an antiquated program into a CSV. I’ve been using search and replace with regex and have the data reasonably clean, but some of the lines have a random line break (carriage return) that needs to go away. I have the end of the records (in this case, lines are records) marked by “###” (without the quotes) and the next line IF it’s really a new record is marked with “@@@” (without the quotes). So, records begin with “@@@” and end with “###” (both without the quotes) and the data should - and most of it does - look like this:
Line 1:@@@date time&&&Name$$$Phone Number%%%Message text goes here.###\r\n
Line 2:@@@date time&&&Name$$$Phone Number%%%Message text goes here.###\r\nBut, every now and again - and there’s no pattern to the hiccups - it looks like this:
Line 1:@@@date time&&&Name$$$Phone Number%%%Message\r\n
Line 2:text goes here.###\r\n
Line 3:@@@date time&&&Name$$$Phone Number%%%Message text goes here.###\r\nI’ve been combing forums and found this which identifies (highlights) the lines after a \r\n that DON’T have an @ at the beginning, which reads: “^[^\r\n@].*” (without the quotes) but there are too many to replace manually, so I’m wondering if someone can help me with the regex that’ll find and replace the carriage return with a space, if the next line DOESN’T have an @ sign.
Any ideas?
Total noob here, apologies for taking so long to explain it…just trying to be clear. And sorry for the misfire on my first post.
Thanks in advance!
-
@Eric-Ettinger said in Remove CRLN *if* next line DOESN'T start with particular character (trying again):
I have the end of the records (in this case, lines are records) marked by “###” (without the quotes) and the next line IF it’s really a new record is marked with “@@@” (without the quotes). So, records begin with “@@@” and end with “###” (both without the quotes) and the data should - and most of it does - look like this:
It’s better to show exactly what the original data looks like (don’t add any characters but please do change the data if it is sensitive) and also what you want the data to look like after the change. When each set is inserted in your post, select the examples (before and after lines separately please) and then click the
</>button above the posting window (it encapsulates the examples in a black box). That allows the forum members to trust the data hasn’t been changed during posting. We can then see if a solution can be found.Terry
-
@Eric-Ettinger said in Remove CRLN *if* next line DOESN'T start with particular character (trying again):
Line 1:@@@date time&&&Name$$$Phone Number%%%Message text goes here.###\r\n
Line 2:@@@date time&&&Name$$$Phone Number%%%Message text goes here.###\r\n
But, every now and again - and there’s no pattern to the hiccups - it looks like this:
Line 1:@@@date time&&&Name$$$Phone Number%%%Message\r\n
Line 2:text goes here.###\r\n
Line 3:@@@date time&&&Name$$$Phone Number%%%Message text goes here.###\r\nThis might be some actual data, without any “fake” stuff added?:
@@@date time&&&Name$$$Phone Number%%%Message text goes here.### @@@date time&&&Name$$$Phone Number%%%Message text goes here.### @@@date time&&&Name$$$Phone Number%%%Message text goes here.### @@@date time&&&Name$$$Phone Number%%%Message text goes here.###I don’t know, seems like a regex replacement like this would do it?:
Find:
\R(?!@)(?!\z)
Replace: a single space
Search mode: Regular expression -
@Terry-R Excellent point. Here are a couple of examples of good lines, then suboptimal lines.
Good:
@@@12-12-12 10:12&&&miguel(with) something insurance$$$504-867-5309%%%francis###
@@@12-12-12 9:00&&&liz smith$$$867-5309###Not so good line (the “christie jones” line):
@@@12-11-12 10:04&&&sherrie (with) company$$$867-5309 ext. 43810###
@@@12-11-12 9:06&&&christie kone$$$867-5309%%%"re referral from phil bigsby
family law###
@@@12-07-12 1:32&&&cathy cheshire$$$713-867-5309%%%re gil gerard###In following your advice on not adding anything to the data, I didn’t but in case some formatting weirdness takes place, I’ll just add here that there is a \r\n at the end of each line, including the line ending in “phil bigsby.”
Thanks!
-
@Alan-Kilborn said in Remove CRLN *if* next line DOESN'T start with particular character (trying again):
I don’t know, seems like a regex replacement like this would do it?:
Find:
\R(?!@)(?!\z)
Replace: a single space
Search mode: Regular expressionOk. So I searched on: ^[^\r\n@@@].* and it successfully highlighted a problematic record (row), below. (Note: Since there are asterisks naturally occurring in the data, I added the two asterisks.)
@@@12-11-12 9:06&&&jenny$$$867-5309%%%"re referral from slim pickens family law###So, I tried your replacement with one or three asterisks and/or an upper and lower case R for that first R and what I get is below.
@@@12-11-12 9:06&&&jenny$$$867-5309%%%"re referral from slim pickens R?!@@@?!zJust to reiterate, I’m a noob and if there was some change I was supposed to make in your regex, I don’t know it…I appreciate the patience.
-
When I try exactly what I suggested earlier, it transforms this data:
@@@12-11-12 9:06&&&jenny$$$867-5309%%%"re referral from slim pickens family law###into:
@@@12-11-12 9:06&&&jenny$$$867-5309%%%"re referral from slim pickens family law###Is this not what is wanted??
I tried your replacement with one or three asterisks and/or an upper and lower case R for that first R and what I get is below
So you tried exactly what I suggested and it didn’t work for you?
And then you started changing it? -
@Alan-Kilborn said in Remove CRLN *if* next line DOESN'T start with particular character (trying again):
So you tried exactly what I suggested and it didn’t work for you?
And then you started changing it?OK. I found the problem. I’m a moron. That’s the problem. Instead of asking for clarification I…well…failed to do exactly what you suggested.
I need to thank you for your patience and assistance!
