community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Loading Redshift pipe delimited data

I am receiving data from a source that is pipe-delimited. This data is pulled from a Redshift database and therefore is pipe-delimited, however, there is a field that contains pipes within the data.

 

Redshift gets around this by marking the pipe in user data as "/|" with the preceding slash. This is better explained here. 

 

In Alteryx, there's no great way to set this exception. I have imported the data as a .csv and set the delimiter to pipe, but I need a good way to get around this blip. 

 

This will be part of the primary workflow for our company; I need to find a good and strong solution even if that isn't Alteryx. 

 

thanks! 

Alteryx Partner

Is it possible to extract the Redshift data with a different/modified process that escapes the pipes, or delimits fields using another character? I don't see any tool guessing correctly which is delimiter and which is data; that intelligence should come from the extract/export of data. Seems like it's a pre-processing problem that needs to be resolved external to Alteryx.

Did I just restate your problem and add no value? :P

Maybe someone else in the community has specifically worked with Redshift data and solved this problem. Sorry I can't be of more help.

No, you're very right. I might close out this conversation.

 

Thank you! 

Magnetar
Magnetar

Knowing very little about Redshift, there could be some things I'm missing here, but could you possibly do something like the following:

 

1. Import it without specifying a delimiter, so it brings everything in as one line per record

2. Use a Replace formula to change the "\|" instances to something other than a pipe (some sort of obvious/easily replaceable keyword like "PIPEDELIM", or you could surround the | with parentheses or quotes that indicate you can ignore that delimiter in the next step)

3. Split your field using Text to Columns tool with the pipe delimiter (which will no longer split the one it shouldn't since you replaced it in step 2 with a keyword, or you can check the box to ignore delimiters in quotes or parentheses)

4. Put the pipe back into the appropriate field using another Replace formula to replace the keyword or remove any parentheses/quotes around it

 

Might look something like this?

 

PipeDelimiter.JPG

 

Let me know if that seems like an option/might work. :)

 

Cheers,

NJ

 

 

 

Labels