CODEWITHSUNDEEP
pythonexcelpandascsvrow
Simone Panico
Simone Panico

Reputation: 115

Excel added quotation marks to csv

Good morning, I was working with a CSV file in Excel. I usually use python for reading, but I had to perform a very quick operation (add a few rows), so I decided to do it via Excel.

Once I saved the CSV however my read script on python was not working. I then noticed that extra quotes had been added.

from:

"16/09/20 11:50:00";"26.3";"26.6";"60.6";"60.9";"2357.0";"2438.0"

to:

"16/09/20 11:50:00;""26.3"";""26.6"";""60.6"";""60.9"";""2357.0"";""2438.0"""

Is there any way to restore the csv to its original format?

Upvotes: 0

Views: 283

Answers (2)

sitting_duck
sitting_duck

Reputation: 3720

import re

bad_csv = open('your_bad_csv', "r")
lines = bad_csv.readlines()

good_csv = open('your_target_good.csv', 'w')

for line in lines:
    new_line = re.sub('""', '"', line[1:-1])
    new_line = '"' + re.sub(';', '";', new_line, count=1)
    good_csv.write(f"{new_line}\n")
    
bad_csv.close()
good_csv.close()

Upvotes: 0

Riceblades
Riceblades

Reputation: 349

If you want to bring it back to its original format you can use find and replace. For example you can replace "" with just ". Hopefully this helps. Here's how to do it:

  1. Windows: Ctrl H
  2. Mac: hold "shift" + "command" + "H" on your keyboard.

Upvotes: 1

Related Questions