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 NameValue DelimiterCountryUSA, UK,NamesSam | Bob|Emailssam@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

Input Delimiters from external file

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

Regex

Common Use Cases

Preparation

Accepted answers

danilang

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

TextToColumns.yxmc

BatchTTC.yxmd

All comments

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

delimiters.yxmd

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

BatchTTC.yxzp

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.

20200617-DynamicDelimiters.yxmd

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

ropra

Hey @danilang

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

Best,

Rohit

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703