CSV parse, probably something simple I'm missing

[Carluccio555]

I have imported a CSV with all data in a single field as follows:

Dog";"Cat";"bird";"monkey"

This effectively makes the delimiter ";"

What is the best way to parse this? Also the field heading similarly is a bunch of headings in one which also needs to be parsed.

thanks 

[alexnajm · Accepted solution]

There's a few ways to tackle this:

• Use a Text to Columns to split on the semicolon, and then use a Formula / Multi-Field Formula after to TRIM the quotes
• Use Regex to split to columns based on the ";" - a little more complex but doable
• Use a Formula to replace ";" with a single character (like a pipe |) and then use a Text to Columns after on that single delimiter

Getting a file would be useful to help further - good luck!

[CoG]

Have you set the Delimiter value to ";" in the input tool?

[Carluccio555]

Thanks!

[Qiu]

@Carluccio555 
We can first remove the " by Replace function then parse with ;

For the headings, we can use Dynamic Rename tool.

[Carluccio555]

@Qiu so I have parsed and field headings looks as follows:

Is there an easy way to parse the field headings within the rename tool? Or do I need to extract separately, parse and then dynamic rename

[Qiu]

@Carluccio555 
I would tick off the option "First Row Contains Field Name" option when bringing the data, then parse the data and finally rename the data field with the first row of data with a Dynamic Rename tool.

[Carluccio555]

Easy peasy, thanks!