"ETL - extracting data from text-space delimited file"

Question

Hi all - I am trying to extract data from a very old file that is organized by placement of character per line (and uses spaces to fill in gaps), instead of CSV. For example: 1 GENZER SCOTT 76 33 KERWIN HILL ROAD NORWICH 2 JOHNSON JIM 81 4 MAIN ST NEW YORK CITY 3 CLINTON BILL 66 16 APPLEBY AVE LITTLE ROCK So the 1st attribute (ID) is at the 1st character, the 2nd attribute (LAST NAME) is from the 3rd character to the 17th character, the 3rd attribute (FIRST NAME) is from the 18th character and so on. Is there any elegant way to extract these data? The only way I can think to do this is to multiply the input, rename the attribute, and then join (XML below). Anything better? Each line of this datafile is about 2000 characters long with about 50 attributes. There is some pattern to the attributes but still... Thanks all! Scott

MartinLiebig · Answer

My first approach would be to add , at the places where i need to split. For example for
44HERNANDEZJIMEJOSE-EDUARDO51

i would use the regex
([0-9]+)([\w-]*)([0-9]+)

and replace it with
$1,$2,$3

Afterwards it is possible to split on the ,

sgenzer · Answer

Yes I tried to use split but could not figure out how to create a correct split pattern.  How do I do that in this case?

MartinLiebig · Answer

if you have some kind of schema, you might be able to have clever regular expressions.

The "Rename" operator in combination with the "Split" operator is your friend in this case.