Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

Replace ascii delimiters

Highlighted
5 - Atom

I am trying to parse a tar file that uses an ascii character as the delimiter, but I cannot find how to either get Alteryx to recognize it or successfully write a formula to replace it with, for example, a pipe

 

I have attached the data as a csv file

 

johnwillcox_1-1581596562625.png

 

Here is some info from Apple  about the file formatting

 

https://affiliate.itunes.apple.com/resources/documentation/itunes-enterprise-partner-feed/

File Format

Each file in the feed is in plain text format, separated into columns and rows. Each record has the same set of fields. The following are the delimiters for each field and record:

  • Field Separator (FS): SOH (ASCII character 1)
  • Record Separator (RS) : STX (ASCII character 2) + “n”

For example, these characters are represented in programming languages as shown below:

Perl:

$field_separator = chr(1); $record_separator = chr(2) . "n";

Java:

String fieldSeparator = String.valueOf((char)1); String recordSeparator = String.valueOf((char)2) + "n";

Highlighted
15 - Aurora

It looks like the ascii character is ASCII code 05. You can use a regex replace tool to replace it with a | and then use a regular text to columns to parse the columns. 

 

Like this (mocked up example with input file attached)

 

DavidP_0-1581601524793.png

 

Highlighted
15 - Aurora

If it's a different ascii character, just amend the number 05 in the regex tool.

5 - Atom

Thanks, when I connect the .csv or the .tar to the Regex pill. Column to parse has 'No valid columns'?

 

johnwillcox_0-1581603165419.png

 

Highlighted
15 - Aurora

In the Input data tool, uncheck option 6 First row contains Field Names

Highlighted
5 - Atom

Thanks

Labels