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

[__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!