Write CSV data containing \n line breaks

Question

I have JSON data that has strings that may contain "\n" linebreaks:

{
    "Id": 12345,
    "firstname": "Peter",
    "lastname": "Normalname"
},
{
    "Id": 76890,
    "firstname": "Paul",
    "lastname": "Evil\nLinebreak" # here's the \n
},

I'm trying to write this to a CSV file using Python 3. This is my approach:

def write_csv_file(filename, resp):

    # open file
    csvFile = open(filename, "w", newline='', encoding='utf-8')
    csvWriter = csv.writer(csvFile, delimiter=',', quoting=csv.QUOTE_ALL)

    # write data
    for line in resp:
       csvWriter.writerow(line.values())

    # close file
    csvFile.close()

Now my problem is that the \n character messes up my CSV file by adding an unexpected line break.

So instead of

"76890","Paul","Evil\nLinebreak"

I get

"76890","Paul","Evil
Linebreak"

What's the best way to solve this, ideally without changing the data, i.e., maintaining the \n character, but without breaking the CSV structure.

Any hints are greatly appreciated.

No @marcin-orlowski - I'm already wrapping my data in "", as your post suggests. — Sebastian, Nov 05 '20 at 15:47
This is not "messed up" - this is valid quoted stringcontent inlcuding a newline - any program that is able to read quoted csv will understand it. — Patrick Artner, Nov 05 '20 at 15:55
@patrick-artner well at least the Python library mysql-connector-python fails when I try to LOAD DATA INFILE. — Sebastian, Nov 05 '20 at 16:01
@Sebastian, have you checked [this](https://stackoverflow.com/q/47713903/10824407) question? — Olvin Roght, Nov 05 '20 at 16:04
@PatrickArtner, I'll take that back, you were absolutely right. Was a different error I was looking at. — Sebastian, Nov 05 '20 at 17:09

Rahul juneja - DevOps · Answer 1 · 2020-11-05T16:22:31.007

0

You must escape backslash before putting it into csv.

Change this line

csvWriter.writerow(line.values())

into this

csvWriter.writerow([  s.replace('\n', '\\n') if isinstance(s, str) else s  for s in line.values()])

OR to escape all use

csvWriter.writerow([ s.encode('unicode_escape').decode() if isinstance(s, str) else s  for s in line.values()])

edited Nov 05 '20 at 16:22

answered Nov 05 '20 at 15:43

Rahul juneja - DevOps

782
5
15

3

Do not reinvent a wheel, you can use [`unicode-escape`](https://docs.python.org/3/library/codecs.html#text-encodings) codec: `s.encode('unicode_escape').decode()` – Olvin Roght Nov 05 '20 at 15:55

Write CSV data containing \n line breaks

1 Answers1