Parsing .csv with multiple character separator

Hello All,

I'm trying to parse a .csv file that has a multi-character delimiter "<|>".

I am trying to do this by using the regex tool but so far have failed miserably

As I understand, the expression /<\|>/g should match to all my delimiters, as checked here http://regexr.com/

This doesn`t seem to work with Alteryx however, as even when checking for matches I get falses for all rows.

I could always use a find/replace on the delimiters, but that would force me to recheck the model with every new dataset, as some of the fileds are free text user input and you never know when someone will use your separator in their text.

TL;DR: How do I parse a .csv file delimited by the "<|>" separator using regex?

All answers are super appreciated!

Regex

Parse

Accepted answers

AdamR_AYX

I think the attached does what you want.

Not the prettiest of regex, but does the job.

Key points are:

Read in the file with \0 delimiter (which just reads the raw rows)
Use the regex tool in the tokenize mode marking the data you want to keep (.*?)(?:<\|>|$)
First ? is non greedy
Second ? is non marked group
Have to make that second group match your delimiter or the end of the line

gtpipeltdelimiters.yxzp

All comments

AdamR_AYX

I think the attached does what you want.

Not the prettiest of regex, but does the job.

Key points are:

Read in the file with \0 delimiter (which just reads the raw rows)
Use the regex tool in the tokenize mode marking the data you want to keep (.*?)(?:<\|>|$)
First ? is non greedy
Second ? is non marked group
Have to make that second group match your delimiter or the end of the line

gtpipeltdelimiters.yxzp

nathanpartel

Hi Adam,

That worked beautifully! Thank you for helping

To follow up on it, I have two questions:

1) Could you explain how the expression used works? I'm fairly new to regex and as such don't understand it completely.

2) Is there a way to name the new columns based on the initial column name? (see image)

Cheers!

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157