I am parsing CSV files where values include strings that represent JSON objects, among boolean, normal strings, and other values. The CSV file has a header. As I iterate over the non-header rows of the CSV file, I use Javascript's split method with the following regex to grab the value at each 'cell' of the CSV row:
let currentLine = lines[i].split(/,(?=(?:(?:[^"]*"){2})*[^"]*$)/)
Unfortunately, it doesn't always delimit lines containing legal JSON strings. Some strings are captured, but strings like this one gets truncated in odd places, wrecking the parsing of the entire CSV file:
'{"an object" : [{"sub-object 1": {
"description": "sub-object is for blah blah",
"nestedArray": ["param1","param2","param3"]}},
{"sub-object 2": {
"description": "sub-object is for blah blah",
"nestedArray": ["param1","param2","param3"]}},
{"sub-object 3": {
"description": "sub-object is for blah blah",
"nestedArray": ["param1","param2","param3"]}}
]
}'
As far as I can tell, the above is legal JSON (curiously, https://jsonformatter.curiousconcept.com/# validates the above if it's pasted without single quotes surrounding it -- not sure why that would be). Any thoughts on how to properly parse this? I thought this would be a fairly trivial task but apparently JSON is not frequently embedded as a value in CSV files. Not sure if there's a standard or best practices way of handling this.
EDIT: To clarify, if anyone has a regex that would capture the above json string in a CSV file, I'd be greatly appreciated if you could share it with me
This can be used as a template for general purpose CSV parsing.
(?:(?:^|,|\r?\n)[^\S\r\n]*)(?:("[^"\\]*(?:\\[\S\s][^"\\]*)*"|'[^'\\]*(?:\\[\S\s][^'\\]*)*'|[^,\r\n]*)(?:[^\S\r\n]*(?=$|,|\r?\n)))
test: https://regex101.com/r/AnQqyv/1
Where the field is trimmed in capture group 1
(?: # Delimiter comma or newline
(?: ^ | , | \r? \n )
[^\S\r\n]* # leading optional whitespaces
)
(?:
( # (1 start), field
" # " Quoted
[^"\\]*
(?: \\ [\S\s] [^"\\]* )*
"
| # or
' # ' Quoted
[^'\\]*
(?: \\ [\S\s] [^'\\]* )*
'
| # or
[^,\r\n]* # Non-quoted
) # (1 end)
(?:
[^\S\r\n]* # trailing optional whitespaces
(?= $ | , | \r? \n ) # Delimiter ahead, comma or newline
)
)