Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

File name update with inconsistent number of characters

kcoleman
5 - Atom

Hi all,

 

I'm trying to rename a group of files with a new generation date and date of report [beginning and end]

 

I was going off the basis that the middle section [between the third and sixth underscores _ ] always has the same number of characters, but as per below example that's not always the case. 

 

Is there any way to approach this variable? See below my currently formulae that extracted the "File Name Middle" section and then the later formulae that put my file name all back together

 

Substring([FileName],110,15)

 

"**Redacted File Location**\"+[File name start]+[Generation Date]+[File Name Middle]+[Report Date Begin New]+"_"+[Report Date End New]+[File Name End]

 

Thank you

 

 

kcoleman_0-1637851809876.png

 

5 REPLIES 5
afv2688
16 - Nebula
16 - Nebula

Hello @kcoleman ,

 

you can use the Padleft function to add whatever character you would like to assure that way always having the same length. In this case for example you could add:

 

 

Padleft([File Name Middle], 5, '0')

 

 

To have always 5 number combinations and if the number is lower add 0 at start, turning "_8721_" to "_08721_"

 

Regards

kcoleman
5 - Atom

Thanks afv, I don't think that has worked for me unfortunately.

 

Here is my middle section, the first set of digits [#8271] could have anything between 2 and 5 digits depending on the report, the second set [#3] could have as many as 2.

 

kcoleman_0-1637852958590.png

 

mceleavey
16 - Nebula

Hi @kcoleman ,

 

Can you post the mock data showing what you have and what you're trying to achieve? An image is no good as we can't use it.

 

M.

mceleavey
16 - Nebula

@kcoleman ,

 

if you're just looking to pad out the section to contain the same amount of characters then I've attached a workflow that will be dynamic no matter how many characters.

This simply splits out the string by underscores, amend to the correct length, pivots into a column then concatenates back using an underscore separator:

 

mceleavey_0-1637856154509.png

 

This gives the following results:

mceleavey_1-1637856186602.png

 

 

Hope this helps,

 

M.

 

 

danilang
18 - Pollux
18 - Pollux

Hi @kcoleman 

 

Another way to go about it is to use Regex to extract every thing from the 3rd to the 6th underscore.  The formula is this

Regex_replace([Filename],"(.*?_){3}(\d+_\d+_\d+).*","$2") 

 

Broken down, the regex is 

(.*?_){3}  any number of characters followed by "_" repeated 3 times.   this is first marked group $1

(\d+_\d+_\d+)   where \d+ is any number of digits. this is the 2nd marked group $2

.* the rest of the string

 

 

danilang_0-1638024842997.png

 

Dan

Labels