Using Parse CSV to read a single line CSV file. All works well until someone enters any of the following characters into one of the fields:
quote - "
comma - ,
Enter - CRLF
Any Extended characters such as emojiis, etc. (data entry on an iPad).
I can understand the quote being a problem, and maybe the extended characters, but comma and CRLF should be handled as they are inside the field quotes.
I have tried different ways to strip these out with varying success. GSUB lets me replace commas and quotes for example... but I don't want to have to build a huge formula that covers all possible exceptions. And how do I strip the CRLF and extended non ascii characters?
I got told adding .parameterize to the CSV Content Body formula would work, but that just returns empty fields.
Mark
Cleaning bad characters when using Parse CSV
Using Parse CSV to read a single line CSV file. All works well until someone enters any of the following characters into one of the fields:
quote - "
comma - ,
Enter - CRLF
Any Extended characters such as emojiis, etc. (data entry on an iPad).
I can understand the quote being a problem, and maybe the extended characters, but comma and CRLF should be handled as they are inside the field quotes.
I have tried different ways to strip these out with varying success. GSUB lets me replace commas and quotes for example... but I don't want to have to build a huge formula that covers all possible exceptions. And how do I strip the CRLF and extended non ascii characters?
I got told adding .parameterize to the CSV Content Body formula would work, but that just returns empty fields.
Stuck. Apprecaite any suggestions.
1 person has this question