Input Delimiters from external file

[ropra]

Hey Community,

I'm back with one more interesting request. I want to delimit multiple data columns and each one of the data columns can have different delimiter.  Find an example in the below table:

Column Name	Value	Delimiter
Country	USA, UK	,
Names	Sam | Bob	|
Emails	sam@xyz.com ; bob@abc.co	;

I have all the delimiters for different columns documented at a single place but how can I use them in alteryx?

Currently, I have to use multiple tranches of different columns and use multiple "text to columns" tools.

Any way I can automate this? Any help would be appreciated!

Best,

Rohit

[Aidan_K]

Hi @ropra ,

do you know that the "text to columns" tool will allow you use multiple delimiters in a single tool? This may be a quick win for you?

See example attached.

Aidan

[ropra]

Hey @Aidan_K,

Nice to meet you!  

Thanks for the quick reply - I missed this detail that each column has specific delimiters. 

For example Country could be "USA.UK" and Email could be "sam@abc.com ; bob@xyz.com" and I can't use "." as the delimiter for both of them (email would be screwed up).

I hope you understood the complication.

Best,

Rohit

[danilang]

Hi @ropra 

Here's a batch macro based solution

The macro basically just wraps a Text to Columns(TTC) and provides Interface tools to modify the column name and delimiter.  The extra Formula tool adds a new field to the result that gives the column name that the data was split on.  This is used in the calling program

 

In the calling program the column and delimiter table is passed to the control input of the macro and the data table with the addition of a recordID is passed to the data input.  The recordID is used to group the data when it comes back out of the Macro.  The data that comes out of the macro looks like this

In order to get it back to 1 record per input row, transpose the 1,2,3 fields, build the column headers and cross tab, giving you

This just give you a sample of what you can do in a batch macro.  You can extend this by adding interface tools to control the other options available in the TTC.  

Dan

[CharlieS]

Hi @ropra 

I suggest first using the delimiter field to replace the delimiter in your data with one that the standard for all fields like this:

Replace([Value],[Delimiter],"|")

This way, you only need to hardcode one delimiter ("|") into the Text-to-Columns tool. Check out the attached workflow for an example. 

[smoskowitz]

If you use the Text to Columns tool, you can add multiple delimiters. It will delimit on each of those. That might be a better solution.

Thanks,

Seth

[Aidan_K]

Nice to meet you too @ropra 🙂

I see you have a couple of dynamic options from the guys below which will likely resolve this for you.

All the best, Aidan

[ropra]

Hey @danilang 

Can you please downgrade the workflow and update it again? - I'm using version 2018.4.

Best,

Rohit

[ChrisTX]

See this post to adjust the version:

Adjusting Alteryx Files for Different Versions

How to fix the error: This document was created by a more recent version of this application and cannot be read

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Adjusting-Alteryx-Files-for-Different-Versio...

[danilang · Accepted solution]

Hi @ 

Here are both the workflow and the macro in 2018.4 version.  Put them both in the same directory.

For future reference, use the following process to downgrade a workflow and or macro.

1. Open the .yxmd(workflow), .yxwz(analytic app) or .yxmc(macro) with notepad.exe.

2. Change the second line in the file from <AlteryxDocument yxmdVer="XXXX.X"> to your version <AlteryxDocument yxmdVer="2018.4">

If it's a package(.yxzp) add the following steps before 1 and 2.  Assume the package file is in C:\WF and is called Workflow.yxzp 

A. Drag it onto the Alteryx canvas and let Alteryx extract all the components.  Once you get the "This workflow was created in a more recent version of Alteryx" message, click OK

B. In File Explorer navigate to the directory where the workflow was extracted.  This will be a sub-directory with the same name as the .yxzp file (minus the extension) the same directory, i.e. C:\WF\Workflow\  

C. Follow steps 1 and 2 above for the main workflow, C:\WF\Workflow\workflow.yxmd(or.yxwz, if it's an app)

D. If there are any macros, they will be in a subdirectory of the _externals directory in the main directory, i.e. C:\WF\Workflow\_externals\1. There may be more than one numbered subdirectory so check them all.  Follow steps 1 and 2 for each .yxmc that you find

Once all these steps are complete, run the main workflow

Dan