Read Only Specific fields from Fixed file

[cetricelaens]

Hey Guys, I have an fixed position file as below. From position 1 to 5 some Id, and I have to ignore next 5 characters and position 11 to 15 I have name and again I have to ignore next 5 characters and again I have to read the data. Basically we will accomplish the same in SAS as below

@01 ID $CHAR5.

@11 NAME $CHAR5.

@21 LNAME $CHAR5.  

Sample Data

12345XXXXXNAME1XXXXLNAME

54321XXXXXNAME1XXXXLNAME

So how to read similarly in Alteryx

[afv2688]

Hello @cetricelaens,

If you are reading a txt file you can indeed read fixed delimited width columns:

This should do the trick, if not, you can also use the substring function in the formula tool to achieve it.

Regards

[echuong1 · Accepted solution]

You can use regex to achieve this! See attached for an example.

I essentially set it up to take the first 5 characters, skip 5 characters, take the next 5, skip 4, then the last 5.

[RolandSchubert]

Hi @cetricelaens ,

you can read the file as a "Fixed Width Text File":

You have to select the start of each field visually, fields are created between the selected positions (or last selected position and end of line):

Within the visual field editor, you can change the field names (e.g. "ID" instead of "Field_1":

To remove the fields you do not need, add a select tool and uncheck the selection for the fields you do not need:

I've attached a sample workflow, hope this is helpful.

Best,

Roland