Hi - I have a .csv file I need to parse and one of the fields contains a number of attributes (called ExtendedAttributes in the sample .csv file). The attributes have a pattern of fieldname:fieldvalue, The issue is that I do not know how many of these attributes will be in the field and I cannot split by comma as some of the field values contain commas within them. I would like the result to have the initial columns and then a column for each of the attributes. For instance country, firstname, etc.Some of the new columns will have null because there isn't an attribute in the field for them. IDFirst NameLast NameDisplay NameHireDateCountryFirstnameiplanet-am-user-alias-listnextcoletc1JK5LJohnJonesJohn Jones20190103USJohnjjones@myemail.com 201938BethBeckerBeth Becker20180607USnullbethbecker@myemail.com fe1305982309834RoseRothRose Roth20181011USRose rrroth@myemail.com I have only done simple manipulation in Alteryx so I think I need to somehow incorporate REGEX and text to rows and possibly pivot the data but I am not really sure how to do this. Any help pointing me in the right direction would be great. Thank you again! test.csv

Split field and value from one cell into multiple columns

Hi - I have a .csv file I need to parse and one of the fields contains a number of attributes (called ExtendedAttributes in the sample .csv file). The attributes have a pattern of fieldname:fieldvalue, The issue is that I do not know how many of these attributes will be in the field and I cannot split by comma as some of the field values contain commas within them.

I would like the result to have the initial columns and then a column for each of the attributes. For instance country, firstname, etc.Some of the new columns will have null because there isn't an attribute in the field for them.

ID	First Name	Last Name	Display Name	HireDate	Country	Firstname	iplanet-am-user-alias-list	nextcol	etc
1JK5L	John	Jones	John Jones	20190103	US	John	jjones@myemail.com
201938	Beth	Becker	Beth Becker	20180607	US	null	bethbecker@myemail.com
fe1305982309834	Rose	Roth	Rose Roth	20181011	US	Rose r	rroth@myemail.com

I have only done simple manipulation in Alteryx so I think I need to somehow incorporate REGEX and text to rows and possibly pivot the data but I am not really sure how to do this. Any help pointing me in the right direction would be great. Thank you again!

test.csv

Datasets

Parse

Transformation

Preparation

Expression

Accepted answers

RolandSchubert

Hi @amw1 ,

solved without RegEx ...

I use the Text to Columns tool (separator "," and splitting "to rows") and "re-build" the original attribute using a Multi-Row formula tool. Sample workflow attached.

Best,

Roland

SplitAttributes.yxmd

All comments

CharlieS

The general strategy I take in scenarios like this is to isolate that field so it can be parsed and transformed, then rejoin it to the original data. A RecordID tool at the beginning will make this possible, along with some parsing tools.

I have put together the attached example to get you started. It works for many fields and shows how this can be achieved. That being said, there are still some inconsistent punctuation and characters that need to be handled in further testing.

20200117-AttributeParsing.yxmd

amw1

Thank you Charlie! I will download and take a look at your solution and will post how it works. I really appreciate the help!

RolandSchubert

Hi @amw1 ,

solved without RegEx ...

I use the Text to Columns tool (separator "," and splitting "to rows") and "re-build" the original attribute using a Multi-Row formula tool. Sample workflow attached.

Best,

Roland

SplitAttributes.yxmd

amw1

Hi Charlie,

I am not sure this will work because one of the attributes has commas within it:

dn: CN=John,OU=Temp Users,OU=Remote,OU=Users,OU=People,DC=us,DC=ad,DC=lfg,DC=com,

so this one attribute has the name of dn and has commas within its value. Splitting on the comma to start will split each of these values apart from what I can see. Also when I run this some of the values are column names so something else isn't quite working.

amw1

Hi Roland,

Thank you for posting this! Downloading and taking a look today.

Thanks again!!

Anne

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335