[Python] CSV writer in Linux 3.12 breaking file integrity due to special character constraints

Currently running into an issue in our Python 3.8 to 3.12 upgrade for our lambda function. For context, the Lambda exports to a csv file, and includes a column called metadata that contains raw JSON. Here’s a glimpse of a valid outputted file for context:

Valid CSV

As you can see, the file is a bunch of string columns with a JSON column in the middle of them. The requirement expects our metadata column to have valid JSON data in its rows. The issue we are running into is maintaining the integrity of the JSON data while following the CSV writers rules.

The requirement expects our metadata column to have valid JSON data in its rows. If vanilla Python objects are written to the CSV output file, these objects come in single quoted, which is invalid JSON. Another requirement is to maintain the integrity of the data coming in, so a find and replace with single quotes to double quotes isn't possible. Therefore, we have to use a json.dumps on our metadata object to convert it to a string, which introduced the following characters in our outputted file:

Invalid JSON data CSV file

As you can see, we have introduced a bunch of nasty escaped quotes that are creating a new variation of our invalid JSON data. This is because without setting quote_char, the default value for the csv writer is a double quote, so it is trying to escape those when it comes across them in the data. You can also see that because we have a carriage return, our codes data is being surrounded properly by the double quote quote_char.

So, next step was configuring the CSV writer. When it runs into a special character in the actual data itself (i.e. escape_chars, line_terminators, delimeters, quote_chars), its expected use an unprintable ascii character for the quoted delimiter in order to ensure that the json isn't malformed. That way, we can get rid of all these escaped double quotes. This works perfectly in Python 3.8 (Linux).

Valid CSV file 3.8

However, when we upgrade to Python 3.12, we see these NAK quote_chars actually being written to our CSV file.

Invalid CSV file 3.12 with NAK chars being introduced

An option we explored is Pandas as an alternative to the csv module. Unfortunately, we are running into the same issues introduced by the CSV writer when trying to correctly format the JSON data to the CSV file which is a non starter. Additionally, even if that worked correctly we could run into memory issues due to pandas storing datasets in memory, so all around doesn't seem like the best path for us to keep pursuing.

There seems to be a dialect option on the csv writer, but updating its value didn't change the issue we're facing since the dialect itself it just a class determining delimiter, quote_char, etc. which we already need to be fixed to our custom case that is breaking. Also, we are unfortunately stuck with the CSV module version that's provided by the python version we are on so were unable to go back to the working CSV library that 3.8 provided.

If anyone has any advice, that would be greatly appreciated, we seem to have reached a limitation with the CSV writers ability to handle writing valid JSON are are wondering if anyone has run into similar issues.

import csv
import json



headers = ['record_id', 'metadata', 'codes']
data= json.dumps({"test": "data"})

output = []

# Define CSV parameters
delimiter = "\t"
escape_char = "\\"
line_terminator = '\r'
# Use unprintable ascii character for quoted delimiter (https://www.ascii-code.com/) in order to ensure that the json isn't malformed.
# Replaced single quote with unprintable NAK control character.
quote_char = chr(21)

output.append(1)
output.append(data)
output.append("code1\rcode2")

with open('data.csv', mode='w', newline='') as file:
csv_writer = csv.writer(file, delimiter=delimiter, escapechar=escape_char, doublequote=False, lineterminator=line_terminator, quoting=csv.QUOTE_MINIMAL, quotechar=quote_char)
#csv_writer = csv.writer(file, delimiter=delimiter, escapechar=escape_char, doublequote=False, lineterminator=line_terminator, quoting=csv.QUOTE_MINIMAL)
csv_writer.writerow(headers)
csv_writer.writerow(output)

Continue reading...