Parse CSV question (column with comma separated values)

[Viktoriia]

Hi everyone,

I have a question reg. parsing CSV. While processing data, it's replacing " with empty string to get clean data.

Everything is working fine until we get comma separated values in one of the columns, for example "Senior Manager Audit, Compliance, and Risk Management" as then Fusion throws an error as it sees it as multiple columns and it does not match the expected number.

I tried to escape it in different ways but was not able to make it work.

Any idea how to do this? 

Could you please advise?

[cverges-medallia]

Ensure your input file conforms to RFC-4180 (https://www.rfc-editor.org/rfc/rfc4180.html).  This is the standard for CSV files and ensures what you're talking about is properly handled.  Fusion's CSV parser is RFC-4180 compliant, and we import files like this all the time.

[Viktoriia]

Thank you for suggestion, but the thing is that data is being processed within scenario: at some point it being collected to Datastore and then Fusion is using this data to create the csv file to store and generate report and notices.  And here we have issue with quotes in the a.m. case, that they are either being removed and then we get error with extra columns or if we check "Preserve quotes inside unquoted field", it goes through, but all labels are quoted as "field1", "field2", etc and it shows as empty value in the following modules.

All is working with regular values though, if we remove the field under question.

[cverges-medallia · Accepted solution]

I'm attaching a blueprint that you can import and test the functionality of the CSV create/parser mechanism.  Hopefully this helps show you how it works.  If you still have problems, I'd recommend creating an isolated scenario like this that reproduces and then post the blueprint here.

Here's the overall pipeline:

Each of the configs for the modules:

Note that I added the quotes, newlines, and commas in Column C just to be as complex as possible.

The fields in the data structure as just Text type, nothing fancy. 

 And here's what the test run shows:

Note how the input values match the ultimate output values.

[Viktoriia]

Thank you so much for your help! I figured out how to adjust our scenario and it worked like a charm!

p.s. It was also a bit tricky to further map the output values using labels as they were not showing in the mapping panel, but then I figured it out and it worked. Thank you!!!

[AkshatSr]

Hi,

I have a sceario which is downloading an attatched CSV file containing data, which i need to parse in order to get the data, i am facing the similar issue, but i dont have the data before i parse it, is there any other way to maintain the comma seprated data, while parsing it??

[cverges-medallia]

It may be best if your question is a separate thread in the forum; otherwise, there just becomes one major thread with everything CSV-related in it.

I'm not fully following your scenario.  You have some kind of Download File module followed by a CSV Parser module, but you also want to maintain the original, unparsed CSV content?  There are at least two ways I can imagine to do that:

1. Use an Aggregator module that's sourced on the CSV Parser, then just reference the Download File module's content afterwards
2. Add a Router between the Download File and CSV Parser modules, and access the Download File content from a second path out of the Router.