Find nth occurrence of a character in a row and replace it in Python, in order to fix/replace a wrong/leftover ";" separator in a text file

Question

I would like to import a text file, with 14 columns with the symbol ";" as a column separator. So, if we have 14 columns, it means we'll find 13 ";" separators in every line/row.

The problem is, as you can see below, there is a row, with a ";" character inside of one of the columns. It makes this line to have 14 ";" separators (translated to 15 columns).

If we open the file in Excel, we can see this wrong "extra" column there:

Trying to read this file with "read_csv" pandas function, it raises an error telling that you have 15 columns (instead of 14).

So, before using the read_csv function I would like to open the file, find the rows with 14 ";" separators in the line, modify/replace the 13th ocurrence of the ";" symbol with a "." and writing back in another text file.

As an string is immutable in Python, theoretically, I don't know how to continue with my Python script. Doubting if the solution is appending in a list or pasting text. By the way, this code will be part of a final script, because this problem will happen more times in the future.

# Creating an empty list to store all the lines from the file will be readed.
listoflines = []

# Opening the file.txt in only "reading" model
with open(r"D:\file.txt", mode='r') as reader:
    for line in reader.readlines():
        listoflines.append(line)

# Creating an empty list to store all the lines including the modified lines.
finaldocument = []
# Detecting the row(s) where the count of the ";" symbol is equal to 14.
for row in listoflines:
    if row.count(';') == 14:
        # Creating a counter.
        n=0
        # Iterating over the letters of the row
        for letter in row:
            if letter == ';':
                n+=1
                # We need to detect the 13rd ";" symbol of the row.
                if n==13:
                    print(letter)
                    letter = letter.replace(';','.')
                    print(letter)
                    
    ## HOW TO APPEND/REPLACE THE MODIFIED LETTER TO THE ROW???? ##
      
    # Storing, again, all the lines (including modified lines) to a final object.
    finaldocument.append(row)

print(finaldocument)

You should in fact not replace the semi-colon, but surround the string with double quotes. This is the CSV standard. And I would do this in Notepad++ with a regular expression replace-all command. — trincot, May 07 '22 at 09:51
tincot suggestion seems to be a better approach. But, your attempt to change a letter in row won;t work since you are modifying the iterator (e.g. letter). You could use [Replace nth occurrence of substring in string](https://stackoverflow.com/questions/35091557/replace-nth-occurrence-of-substring-in-string) to change 14th occurrence in row. — DarrylG, May 07 '22 at 10:20

score 2 · Accepted Answer · answered May 07 '22 at 10:17

You should not replace that semi-colon with another character, but instead wrap the string with double quotes (and escape any double quotes that might already be part of the string by doubling them). This is how in CSV syntax you can include such delimiters. In fact, it is not bad practice to wrap such text with double quotes always.

Here is how I'd adapt your loop:

for row in listoflines:
    if row.count(';') > 13:  # maybe there are even two or more of them
        cells = row.split(';')
        # Escape double quotes and wrap in double quotes
        s = '"' + ';'.join(cells[12:-1]).replace('"', '""') + '"'
        # Replace the involved parts with that single part
        cells[12:-1] = [s]
        # ...and convert back to the string format
        row = ';'.join(cells)
      
    finaldocument.append(row)

I have tested the script using your solution and it works perfectly! Indeed I have added more incorrect ";" characters and works without problem. Thanks for the solution! — jorge_vicente, May 08 '22 at 18:45

score -2 · Answer 2 · answered May 07 '22 at 09:58

Instead of doing this:

if row.count(';') == 14:
    # Creating a counter.
    n=0
    # Iterating over the letters of the row
    for letter in row:
        if letter == ';':
            n+=1
            # We need to detect the 13rd ";" symbol of the row.
            if n==13:
                print(letter)
                letter = letter.replace(';','.')
                print(letter)

Do this : row = row.split(";") this will convert row to a list, if you only need len 14 you can add this: row = row[:14]

Find nth occurrence of a character in a row and replace it in Python, in order to fix/replace a wrong/leftover ";" separator in a text file

2 Answers2