Hi all,

I need to extract a specific part of a file name in a loop.

I created a task that lists all .zip files in that folder and then created a loop over the output which only returns the filenames.
The filenames use the following schema A1_A2_A3_A4_A5.zip and I need to extract the value A3 from the filenames

Unfortuntately, the underscore is not listed as "Field Seperator" in the loop settings so I set this property to "None".
Instead, I use the underscore in the "Text Qualifier" proprerty and set the "Use column" property to 3.

Lastly, I use a PopUp task to show the current x value of the loop in order to check the result of this configuration.

The result when i itterate through the "use column" property is the following and I don't understand why:

1 = A
2 = 1
3 = A2
4 = A
5 = 3
6 = A4
7 = A
8 = 5
9 = .
10 = z
11 = i
12 = p

Does anybody have an explanation why sometimes the whole "column" e.g. A2 and A4 are returned and sometimes only the character?
How do I have to configure the loop in order to get the "column" A3?

Thanks in advance and best regards,
Markus

Try using the GetColumn function instead (in variables)

{STRING(GetColumn|1|3|_|A1_A2_A3_A4_A5.zip)}

It returns row 1, column 3 from the filename when it is split on _

Hi all,

I need to extract a specific part of a file name in a loop.

I created a task that lists all .zip files in that folder and then created a loop over the output which only returns the filenames.
The filenames use the following schema A1_A2_A3_A4_A5.zip and I need to extract the value A3 from the filenames

Unfortuntately, the underscore is not listed as "Field Seperator" in the loop settings so I set this property to "None".
Instead, I use the underscore in the "Text Qualifier" proprerty and set the "Use column" property to 3.

Lastly, I use a PopUp task to show the current x value of the loop in order to check the result of this configuration.

The result when i itterate through the "use column" property is the following and I don't understand why:

1 = A
2 = 1
3 = A2
4 = A
5 = 3
6 = A4
7 = A
8 = 5
9 = .
10 = z
11 = i
12 = p

Does anybody have an explanation why sometimes the whole "column" e.g. A2 and A4 are returned and sometimes only the character?
How do I have to configure the loop in order to get the "column" A3?

Thanks in advance and best regards,
Markus

Thomas's answer is going to be the more useful solution to you, but I thought I would explain the results in case that helps you in the future.

The "Text Qualifier" property basically means "this character is used to wrap columns" - usually to allow them to contain the field separator. It therefore has to come in pairs.

So you've essentially told it to separate columns on the empty string...except where bounded by a pair of underscores.

So "A2" and "A4" are seen as columns bounded by the text qualifier you specified, and everything else is separated on "none" - therefore into individual characters.

The looping controls support only a limited set of field and line separators, but apparently allow any text qualifier (which I hadn't noticed before). The GetColumn functions, on the other hand, don't appear to support text qualifiers at all. I also never noticed before that it doesn't directly support fixed-width column data, although in that particular case looping on rows and splitting the whole row value ought to be clunky but serviceable. I feel kind of fortunate I've never run into a problem with those limitations, at least not that I couldn't resolve by falling back on putting the loop inside powershell and using ConvertFrom-Csv.

You guys are awesome!

Thanks a lot for the getColumn solution and the explanation!

It works :)

Have a great day!

HA! As I posted this I thought I should try getcolumn with multiple delimiters and it works! So, you could just ignore this post.
{STRING(GetColumn|1|5|_.|A1_A2_A3_A4_A5.zip)}
But I'll leave it here in as you'd like to read on if you should want more options! Information is Power!


FYI - I'm on 9.8.5 and the getcolumn solution does not work if you want the 5th column. It returns "A5.zip". It seems it's designed to expect that a column ends with the character specified or the end of the string.

For a quick and dirty this would get the last element before the period.


However depending on your use case it may be better to call powershell and use a single regular expression that works for both cases.
Pass in the string containing the file name to a powershell task and do this. Return the 3rd element that is delimited by underscores or periods. Note it returns "A5" if asked for the 4th element.