Parse a field

[#1]

So I have a field, we'll call it Detail.

I need to separate the field into different parts. The field contains a folder structure. So it may look something like this..

/Folder1/Folder2/Folder3/Folder4/FileName/

I want to separate each part of the structure into its own field so I can choose them individually.

I already tried the subfield function like this...

subfield(Detail,'/',1) as Detail_Test1

This seems to work to bring the last part of the folder structure back. But I'm unsure how to get the rest. I tried changing 1 to 2,3,etc but it doesn't show me the correct data.

Thanks

[swuehl]

Subfield should return you the parts of your string:

LOAD *,

SubField(Detail,'/',2) as Test1,

SubField(Detail,'/',3) as Test2,

SubField(Detail,'/',4) as Test3,

SubField(Detail,'/',5) as Test4,

SubField(Detail,'/',6) as Test5;

LOAD * INLINE [

Detail

/Folder1/Folder2/Folder3/Folder4/FileName/

];

Are you always coping with same depth of your hierarchy?

[#3]

I am not. Could that be the problem?

Some are only two folders deep, some may be 4 or 5.

[swuehl · Accepted solution]

I assume you need to parse the file name into a certain field, and then the parts of your hierarchy in other fields.

You can use negative indices with subfield to access the partial strings starting from the back.

Maybe like

LOAD *,

SubField(Detail,'/',-1) as Filename_Test,

If(substringcount(Detail,'/')>1,SubField(Detail,'/',2)) as Folder1,

If(substringcount(Detail,'/')>2,SubField(Detail,'/',3)) as Folder2,

If(substringcount(Detail,'/')>3,SubField(Detail,'/',4)) as Folder3,

If(substringcount(Detail,'/')>4,SubField(Detail,'/',5)) as Folder4,

If(substringcount(Detail,'/')>5,SubField(Detail,'/',6)) as Folder5;

LOAD * INLINE [

Detail

/Folder1/Folder2/Folder3/Folder4/FileName

/Folder1b/Folder2b/FileNameb

];

[#5]

I was putting the subfield into the wrong resident load in my script. So this works.

Thanks for your help!