Cleaning a CSV string to remove unwanted rows in memory

user_7926263 0
Viewed 27

I have a CSV that is semicolon separated, and that uses double quotes as a quote character. I get this CSV from an API, the raw data looks like this :

1;"toto@toto.com";"Toto";"Tata"
2;"BAD_DATA"
"BAD_DATA"
3;"alice@alice;com";"Alice";"Dupont"
4;"bob@bob.fr";"Bob";"Morane"

The second and third lines are like this because of encoding problems at the source, I cannot change this.

I would like to change the string to this :

1;"toto@toto.com";"Toto";"Tata"
3;"alice@alice;com";"Alice";"Dupont"
4;"bob@bob.fr";"Bob";"Morane"

So that all the lines with the wrong length are deleted. But since my code will run on a server, I would like if possible to do all of this in memory, without writing to a file on the machine.

As you can see, some of the fields can contain semicolons so I need to protect those with double quotes around them. It adds a little complexity.

So far, I have this :

import csv

myString = f'''1;"toto@toto.com";"Toto";"Tata"
2;"BAD_DATA"
"BAD_DATA"
3;"alice@alice;com";"Alice";"Dupont"
4;"bob@bob.fr";"Bob";"Morane"'''

lines =  myString.splitlines()

reader = csv.reader(lines, delimiter=';', quotechar='"')

for row in reader:
    if len(row) == 4:
        # I don't know what to do here

I anybodys knows in what direction I can try to advance, that would be very nice :)

csv python-3.x
2 Answers

you can use list comprehension and .join method of str,

result_str = ''
for row in reader:
    if len(row) == 4:
        result_str += ';'.join(str(x) for x in row) + '\n'

print(result_str.strip())

which prints:

1;toto@toto.com;Toto;Tata
3;alice@alice;com;Alice;Dupont
4;bob@bob.fr;Bob;Morane
user_167223230

Your code correctly filters out rows that don't contain exactly 4 columns already. You can use a csv.writer to write what passes the filter to an IO.StringIO file object so you can get the output as a string with the object's getvalue method. However, since the first column of your expected output is unquoted and is the only numeric column, you should configure your writer to use the csv.QUOTE_NONNUMERIC quoting option, and convert the first column to an integer before passing the columns to the writer's writerows method for output:

import csv
from io import StringIO

myString = f'''1;"toto@toto.com";"Toto";"Tata"
2;"BAD_DATA"
"BAD_DATA"
3;"alice@alice;com";"Alice";"Dupont"
4;"bob@bob.fr";"Bob";"Morane"'''

output = StringIO()
reader = csv.reader(StringIO(myString), delimiter=';')
writer = csv.writer(output, delimiter=';', quoting=csv.QUOTE_NONNUMERIC)
writer.writerows((int(id), *rest) for id, *rest in reader if len(rest) == 3)
print(output.getvalue())

This outputs:

1;"toto@toto.com";"Toto";"Tata"
3;"alice@alice;com";"Alice";"Dupont"
4;"bob@bob.fr";"Bob";"Morane"
user_68909120
Related