Expand my Community achievements bar.

Join us LIVE in San Francisco on November 14th for Experience Makers The Skill Exchange. Don't miss out on this free learning event!
SOLVED

Parse CSV question (column with comma separated values)

Avatar

Level 4

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?

 

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Level 6

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:

Screen Shot 2024-01-26 at 11.44.31 AM.png

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.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.The fields in the data structure as just Text type, nothing fancy. Screen Shot 2024-01-26 at 11.44.47 AM.png

 And here's what the test run shows:

Screen Shot 2024-01-26 at 11.44.51 AM.png

 

Screen Shot 2024-01-26 at 11.44.56 AM.png

Note how the input values match the ultimate output values.

View solution in original post

6 Replies

Avatar

Level 6

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.

Avatar

Level 4

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.

Avatar

Correct answer by
Level 6

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:

Screen Shot 2024-01-26 at 11.44.31 AM.png

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.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.The fields in the data structure as just Text type, nothing fancy. Screen Shot 2024-01-26 at 11.44.47 AM.png

 And here's what the test run shows:

Screen Shot 2024-01-26 at 11.44.51 AM.png

 

Screen Shot 2024-01-26 at 11.44.56 AM.png

Note how the input values match the ultimate output values.

Avatar

Level 4

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!!!

Avatar

Level 1

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??

Avatar

Level 6

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.