When I try reading my string with csv reader the output I get converts the json string to
'{filter":"freeinternet"', 'region:"178307"}"' which needs to stay
"{\"filter\":\"freeinternet\",\"region\":\"178307\"}"
This is what I’ve tried. I’ve even tried adding quotechar, escapechar, and trying different versions, but it results in incorrect results
import csv
from io import StringIO
s = u"""url,forgeresponsetype,identifiers,metadata,partitionkey,sortkey,expirationdate,lastmodifieddate,redirectkey,siteid,locale,type,update_date
https://www.expedia.com/Seattle-Hotels.d178307.Travel-Guide-Hotels,MOVED_PERMANENTLY,"{\"filter\":\"freeinternet\",\"region\":\"178307\"}",,HOTEL_DESTINATION_THEME.494647,1.en_US.filter:freeinternet.region:178307,1696746399,2023-06-18T06:26:40.521Z,,1,en_US,HOTEL_DESTINATION_THEME,21/06/23
https://www.expedia.com/Seattle-Hotels.d178307.Travel-Guide-Hotels,MOVED_PERMANENTLY,"{\"filter\":\"freeinternet\",\"region\":\"178307\"}",,HOTEL_DESTINATION_THEME.494647,1.en_US.filter:freeinternet.region:178307,1696746399,2023-06-18T06:26:40.521Z,,1,en_US,HOTEL_DESTINATION_THEME,21/06/23
https://www.expedia.com/Seattle-Hotels.d178307.Travel-Guide-Hotels,MOVED_PERMANENTLY,"{\"filter\":\"freeinternet\",\"region\":\"178307\"}",,HOTEL_DESTINATION_THEME.494647,1.en_US.filter:freeinternet.region:178307,1696746399,2023-06-18T06:26:40.521Z,,1,en_US,HOTEL_DESTINATION_THEME,21/06/23"""
f = StringIO(s)
reader = csv.reader(f, delimiter=',', escapechar = '\\')
xd = [row for row in reader]
Any help is appreciated
>Solution :
What you can do is, manipulate the input string to have different quote characters for the file and for dictionary data.
For example, use ' as a quote character for input and keep " as a quote character for your JSON/dictionary values.
import csv
from pprint import pprint
from io import StringIO
s = """url,forgeresponsetype,identifiers,metadata,partitionkey,sortkey,expirationdate,lastmodifieddate,redirectkey,siteid,locale,type,update_date
https://www.expedia.com/Seattle-Hotels.d178307.Travel-Guide-Hotels,MOVED_PERMANENTLY,"{\"filter\":\"freeinternet\",\"region\":\"178307\"}",,HOTEL_DESTINATION_THEME.494647,1.en_US.filter:freeinternet.region:178307,1696746399,2023-06-18T06:26:40.521Z,,1,en_US,HOTEL_DESTINATION_THEME,21/06/23
https://www.expedia.com/Seattle-Hotels.d178307.Travel-Guide-Hotels,MOVED_PERMANENTLY,"{\"filter\":\"freeinternet\",\"region\":\"178307\"}",,HOTEL_DESTINATION_THEME.494647,1.en_US.filter:freeinternet.region:178307,1696746399,2023-06-18T06:26:40.521Z,,1,en_US,HOTEL_DESTINATION_THEME,21/06/23
https://www.expedia.com/Seattle-Hotels.d178307.Travel-Guide-Hotels,MOVED_PERMANENTLY,"{\"filter\":\"freeinternet\",\"region\":\"178307\"}",,HOTEL_DESTINATION_THEME.494647,1.en_US.filter:freeinternet.region:178307,1696746399,2023-06-18T06:26:40.521Z,,1,en_US,HOTEL_DESTINATION_THEME,21/06/23"""
f = StringIO(s.replace(',"{', ',\'{').replace('}",', '}\','))
reader = csv.reader(f, delimiter=',', quotechar = '\'')
pprint([row for row in reader])
The line s.replace(',"{', ',\'{').replace('}",', '}\',') replaces " quote character with ' and then we are using that in csv.reader‘s argument.
Note: It’ll work as long as there are no nested dictionaries.