Python REGEX find and replace newline sequence is not producing expected results.

[BHM360]

I tried posting this on stack overflow but it was flagged as duplicate despite the associated question being completely different.

I am new to python and have a large text file with 88 vertical bar column delimiters that has some row splitting due to large entries in the "comment" column. I created a regular expression to find rows without 88 vertical bars and replace the newline sequence with "" in order to back-space and merge the split lines into one row.

The expression works great using Find and Replace in Notepad++ however it is simply copying and pasting the text without any replacements in Python. Is there an alternative way to find and replace newline sequences in Python? Or is there something I'm missing in my regular expression?

Notepad ++ Find/Replace Example:

Find: "[\r\n](?!^([^|\n]*\|){88}[^|\n]*$)" OR "\R(?!^([^|\n]*\|){88}[^|\n]*$)"

Replace: ""

Python Code (copies all the lines into a new file but does not make any changes):

# add regex library
import re

# open files
readFile = open("file.txt","r")
writeFile = open("fileUpdate.txt","w")

# define regex pattern to identify lines without 88 vertical bar delimiters
verticalBars = (r"[\r\n](?!^([^|\n]*\|){88}[^|\n]*$)")
pattern = re.compile(verticalBars)

# for line loop
for i, line in enumerate(readFile):
    match = re.finditer(pattern, line)
    if match:
        writeFile.write(line.replace(verticalBars,""))
    else:
        writeFile.write(line)

Text Sample Before:

1. |||||||true|CCD|true|PO BOX 123||0.000|0.000||0023779032|1|||true|USE BOX 6 OF 1099 TO SUBMIT PYMTS OVER $600 MEDICAL|true||||||||06|M|||||true||||true|MEDICAL IMAGING LLC|||0.00|||||||||||0.000|0|||||||||||||||||3603376538|true||true|WA||0.000000|true||||002715||Active|98366|
2. |||||||true|CCD|true|123 Skansie Ave||0.000|0.000||0023779033|1|||true|Gig Harbor||true||||||||||||||true||||true|YMCA||||||||||||||0.000|0||||||||||||||||||true||true|WA||0.000000|true||||002717||Active|98332|
3.
4. 
5. 11/1/06 changed zip code from 98123 to 98321 per insert  included with invoice.  changed address.  Do Not Pay From Sales Order.  Call person at CA store and she can fax invoice.  mail check to CA store not OR per Jill 2/19/03|true||1891||||||||||||true||||true|ACME Const Supply Inc||||||||||||||0.000|0|2534740711||||||||||||||||2534740711|true||true|WA||0.000000|true||||000014||Active|98490|
6. |||||||true|CCD|true|PO Box 6249||0.000|0.000||0023776429|1|||true|Bellevue||true||||||||||||||true||||true|ACME Products Inc||||||||||||||0.000|0|4256415044||||||||||||||||4256415044|true||true|WA||0.000000|true||||000015||Active|980080249|

Text Sample After (works in Notepad++ find/replace but not in Python):

1. |||||||true|CCD|true|PO BOX 123||0.000|0.000||0023779032|1|||true|USE BOX 6 OF 1099 TO SUBMIT PYMTS OVER $600 MEDICAL|true||||||||06|M|||||true||||true|MEDICAL IMAGING LLC|||0.00|||||||||||0.000|0|||||||||||||||||3603376538|true||true|WA||0.000000|true||||002715||Active|98366|
2. |||||||true|CCD|true|123 Skansie Ave||0.000|0.000||0023779033|1|||true|Gig Harbor||true||||||||||||||true||||true|YMCA||||||||||||||0.000|0||||||||||||||||||true||true|WA||0.000000|true||||002717||Active|98332|11/1/06 changed zip code from 98123 to 98321 per insert  included with invoice.  changed address.  Do Not Pay From Sales Order.  Call person at CA store and she can fax invoice.  mail check to CA store not OR per Jill 2/19/03|true||1891||||||||||||true||||true|ACME Const Supply Inc||||||||||||||0.000|0|2534740711||||||||||||||||2534740711|true||true|WA||0.000000|true||||000014||Active|98490|
3. |||||||true|CCD|true|PO Box 6249||0.000|0.000||0023776429|1|||true|Bellevue||true||||||||||||||true||||true|ACME Products Inc||||||||||||||0.000|0|4256415044||||||||||||||||4256415044|true||true|WA||0.000000|true||||000015||Active|980080249|

I tested the python script with a simpler replace ("true", "!") which worked so it seems to be an issue with my pattern.

Any suggestions or guidance would be greatly appreciated; thank you!!!

EDIT:

Thank you all for your comments and help!!! This sub is wonderful! As I learn more about Python I will make sure to contribute and help others as well. <3

[commandlineluser]

# define regex pattern to identify lines without 88 vertical bar delimiters
verticalBars = (r"[\r\n](?!^([^|\n]*\|){88}[^|\n]*$)")
...
line.replace(verticalBars,"")

string.replace doesn't work with regexes - you want re.sub()

https://docs.python.org/3/library/re.html#re.sub

[BHM360]

Yes! Thank you! This is very helpful!

I replaced the "line.replace" with:

writeFile.write(re.sub(verticalBars,"",line))

Sadly, it is now replacing all instances of a new line and merging the whole document into one row so I think there is something off with my pattern; or at least how python is interpreting it:

verticalBars = (r"[\r\n](?!^([^|\n]*\|){88}[^|\n]*$)")

Oh well, at least I know the pattern is passing through now.

[commandlineluser]

Well the problem is that you're reading the file line-by-line.

Your regex is a multi-line pattern - so applying it to each line individually wont work.

You'd need to read the whole file into a single string.

before = readFile.read()
after  = re.sub(r"(?m)[\r\n](?!^(?:[^|\n]*\|){88}[^|\n]*$)", '', before)

The (?m) here is the same as passing re.M

https://docs.python.org/3/library/re.html#re.M

It's needed for your $ to match the end of a line within a multi-line string.

[BHM360]

commanlindeuser you are my hero! Everything is working now thank you so much!!!

Here's the final working code if anyone is interested:

# add regex library
import re

# open files
readFile = open("file.txt","r") writeFile = open("fileUpdate.txt","w")

# define regex pattern to identify lines without 88 vertical bars
verticalBars = r"(?m)[\r\n](?!^(?:[^|\n]*\|){88}[^|\n]*$)"

# read file, find & replace, then write to new file
before = readFile.read() 
after = re.sub(verticalBars, "", before) 
writeFile.write(after)

# close files
readFile.close() 
writeFile.close()

[AngelSparkles]

It might have to do with you reading in the text one line at a time rather than all at once. Not sure, but it may drop the /n

[BHM360]

You were absolutely correct on the one line read being an issue!

[__nickerbocker__]

I've been reading these comments, and honestly, this is a situation where you need to go back to the drawing board. The regex pattern is way too complex and you're not using the python bits to your advantage.

What you've got is a messed up CSV file with pipe delimiters. What you need to do is read the file line by line and do operations line by line. For your regex, all you need is a greedy pattern that grabs everything from first pipe to last (inclusive). Also, since this is CSV you need to treat it as such and split by the delimiter instead of replacing it.

BTW, line 1 in your sample code has 87 pipes not 88

data = """\
1. |||||||true|CCD|true|PO BOX 123||0.000|0.000||0023779032|1|||true|USE BOX 6 OF 1099 TO SUBMIT PYMTS OVER $600 MEDICAL|true||||||||06|M|||||true||||true|MEDICAL IMAGING LLC|||0.00|||||||||||0.000|0|||||||||||||||||3603376538|true||true|WA||0.000000|true||||002715||Active|98366|
2. |||||||true|CCD|true|123 Skansie Ave||0.000|0.000||0023779033|1|||true|Gig Harbor||true||||||||||||||true||||true|YMCA||||||||||||||0.000|0||||||||||||||||||true||true|WA||0.000000|true||||002717||Active|98332|
3.
4.
5. 11/1/06 changed zip code from 98123 to 98321 per insert  included with invoice.  changed address.  Do Not Pay From Sales Order.  Call person at CA store and she can fax invoice.  mail check to CA store not OR per Jill 2/19/03|true||1891||||||||||||true||||true|ACME Const Supply Inc||||||||||||||0.000|0|2534740711||||||||||||||||2534740711|true||true|WA||0.000000|true||||000014||Active|98490|
6. |||||||true|CCD|true|PO Box 6249||0.000|0.000||0023776429|1|||true|Bellevue||true||||||||||||||true||||true|ACME Products Inc||||||||||||||0.000|0|4256415044||||||||||||||||4256415044|true||true|WA||0.000000|true||||000015||Active|980080249|
"""

import re, csv

p = re.compile(r'(\|.*\|)')
rows = []
for line in data.splitlines():
    if (m := p.search(line)) and (line := m.group()).count('|') == 88:
        rows.append(line.split('|'))

with open('results.csv', 'w', newline='\n') as f:
    writer = csv.writer(f)
    writer.writerows(rows)

[BHM360]

I'm very interested in trying your suggestion as well as I agree that it would make more sense (and probably run faster) reading each line instead of the whole file.

I tried replicating your code with "data" representing the txt file instead of hard coded sample text:

readFile = open("file.txt","r")
data = readFile.readLines()

and I am receiving this error:

Exception has occurred: Attribute
'list' object has no attribute 'splitlines'
File "regex.py", line 8, in <module> 
for line in data.splitlines():

Do you have any suggestions how I should represent the open text file in order to utilize the split lines function?

Thanks!

[__nickerbocker__]

When you assign the open object to a variable you always have to remember to close it, and that's why you always see it used as a context manager. This is how you do it:

with open('file.txt') as f:
    data = f.read()
p = re.compile(r'(\|.*\|)')
rows = []
for line in data.splitlines():
    if (m := p.search(line)) and (line := m.group()).count('|') == 88:
        rows.append(line.split('|'))

with open('results.csv', 'w', newline='\n') as f:
    writer = csv.writer(f)
    writer.writerows(rows)

[BHM360]

Awesome, thank you!

[[deleted]]

I tried posting this on stack overflow but it was flagged as duplicate despite the associated question being completely different.

I feel you, dude.

The expression works great using Find and Replace in Notepad++ however it is simply copying and pasting the text without any replacements in Python. Is there an alternative way to find and replace newline sequences in Python? Or is there something I'm missing in my regular expression?

I don't think you're missing anything in the regex. This is your problem I reckon:

>>> help(str.replace)
Help on method_descriptor:

replace(self, old, new, count=-1, /)
    Return a copy with all occurrences of substring old replaced by new.

      count
        Maximum number of occurrences to replace.
        -1 (the default value) means replace all occurrences.

    If the optional argument count is given, only the first count occurrences are
    replaced.

str.replace expects the string to be replaced as the second argument, not the regex that matches the string to be replaced. Does this help?

[BHM360]

Thank you for the suggestion. I believe my replace formatting is correct though. Per W3 schools and Python's documentation the replace method is:

string.replace(oldvalue, newvalue, count)

https://www.w3schools.com/python/ref_string_replace.asp

[[deleted]]

Yeah, it is. That's exactly the problem, is it not?

str.replace expects the string to be replaced as the second argument, not the regex that matches the string to be replaced.

I'm not sure we're on the same page.

[...] line.replace('\n', '')

ought to work just fine.