We regularly receive CSV files from clients, and sometimes the data fields include embedded commas that need to print as part of the content. Since CSV uses commas as delimiters, these embedded commas cause parsing issues.
I understand that wrapping fields in double quotes allows embedded commas to be preserved correctly. My question is: What’s the best way to automate adding quotes around fields that contain commas—or is there another reliable method to fix this issue automatically? (I do not believe we can get the client to adjust their data).
Any thoughts or suggestions are greatly appreciated!
Since the commas are the delimiters, unless you want to manually add quotes, I am afraid I cannot see any way of doing this.
Unless the fields which contains the commas (as text) are of somekind very specific (like an amount) in which case you could try using Regex with Javascript but other than that, it is up to the customer to provide valid data. It is considered invalid at the moment.
@AndrewD: you can easily make the case with your customer that what they are providing you with is not a valid CSV. That said, if by any chance the fields are fixed-length, then it’s a simple matter to wrap the contents of all fields between double-quotes.
Another thing you could do is to pre-parse the CSV file in Workflow to count the number of commas in each record and whenever there are more commas than the number of fields in the header - assuming there are headers! - then you could filter out those records so that the rest of the job could be produced correctly.
But in the end, as @jchamel already pointed out, it really is the customer’s responsibility to provide you with valid data.
Thanks @jchamel & @Phil, always appreciate the support provided here.
Agreed, it would be preferable if our client could just provide us correct data to begin with. Either all escaped in quotes already, or perhaps using a different delimiter could work.
Realistically, I do not believe that we can get our client to change, so we’ll have to look at manually cleaning their data before starting the workflow. I’ll also look into your suggestion of pre-parsing the CSV data so that the clean records can still proceed print. We would need to communicate to the client those records that were omitted in this case.
For the data to be outputted like that, you might want to check how they output it and if there is any other format they could output that wouldn’t be invalid.
