Extracting the Name in the Full Path

Hi,

I have the full path output as field name. For example [path name... |||'Marimar$'. I would like to get only the name Marimar or Jeger which is found at the end of the path which corresponds to sheet name.

Please let me lend any of your assistance.

Thanks,

Mar

Common Use Cases

Expression

Accepted answers

binu_acs

@KamenRider another option using formula

substring([RAWRECON], FindString([RAWRECON], '|')+3,Length([RAWRECON]))

All comments

atcodedog05

Hi @KamenRider

Here is how you can do it.

Workflow:

Hope this helps : )

sheet name.yxmd

binu_acs

@KamenRider Another method

Trim(Trim(SubString([FilePath],FindString([FilePath], '|')+3,Length([FilePath])),"'"),'$')

KamenRider

Hi @atcodedog05

I'm not sure but it is giving me "NULL" results. The path starts with for example - \\gonzales.singapore/peter/pablo/....|||'Marimar$'. The path is very long about more than 260 characters.

Please advise.

Thanks,

Mar

atcodedog05

Hi @KamenRider

It still seems to work. Can share some data in a yxdb file

Hope this helps : )

sheet name.yxmd

KamenRider

Hi @binu_acs @atcodedog05

The formula is good but I am receiving an error - Parse Error at Char (89) Malformed Function Call (Expression # 1). Do you know why?

Please advise.

Thanks,

Mar

binu_acs

@KamenRider The formula is working for me

binu_acs

@KamenRider Can you share your file full path not workflow

KamenRider

Hi @binu_acs @atcodedog05

The path is very long. Please see below. I changes some due to confidentiality.

\\NASOUTH.AD.CALIFORNIA.COM\AMERCFSMB SHARE25\BDTM_NML_bbQA\common\BDTM_NML_bbQA\1 BAKIMA INPEXING TEAM\PROKEY - ALL\2022 DIRECT PUTO\DA-FUNDING\CONTROL REPORT 2022\ALTERYX VERSION\BUYOFF LOG.xslx|||'JAYSON$'

The goal is to get or extract the sheet name from the input file. I welcome any ideas just to get sheet name.

Thanks and looking forward for your response.

Kamen

binu_acs

@KamenRider what is the error you are getting when you use the below formula

Trim(Trim(SubString([FilePath],FindString([FilePath], '|')+3,Length([FilePath])),"'"),'$')

I tried @atcodedog05 RegeEx formula and it is working fine.

binu_acs

@KamenRider also just post your formula from the Formula tool, just wanted to see what would be the error

KamenRider

Guys @binu_acs @atcodedog05

Truly I can't locate the error. Upon my reading, I suspect maybe because I've reach the limit of 260 characters. I tried the trim command and it doesn't reach the last word. For @atcodedog05 Reg Ex, the result is the whole full path instead of the sheet name. For @binu_acs , it always give me a "null" result.

Mar

binu_acs

@KamenRider can you try with a different path and see you are getting the result?

DylanDowrick

@KamenRider have you checked with a select tool if your FullPath string field is long enough?

DylanDowrick

Additionally, if your field is long enough, there is a formula that gets you 90% of the way there: FileGetExt()

KamenRider

Hi @binu_acs @DylanDowrick @atcodedog05

I made locating the shared folder to my home drive and the regex is still not working. I have checked the select tool and it is in VString with size of 1073741823.

In line to FileGetExt(), how it is being used? I use FileGetDir.

Thanks,

Kamen

binu_acs

@KamenRider can you share your home drive path also the screen shot of your formula tool, where you are calling the regex formula?

KamenRider

Hi @binu_acs ,

The data is confidential and I am very sorry. But I can change some part to illustrate or show how long it is as show below:

Column name: RAWRECON

\\NASOUT.AD.ABC COMPANY.COM\amerBBBS$\xbust\PAVHOME65\485636\PBMdesk\desktop\Roldan Files\Business Pattern\13_Alteryx\BAP-Payoff Detail\Output\Output + [filename] + ".xlsx|||" + [filename]

Then formula for regrex

REGEX_Replace([RAWRECON], ".*\|\|\|'(.+)\$'", "$1")

results:

\\NASOUT.AD.ABC COMPANY.COM\amerBBBS$\xbust\PAVHOME65\485636\PBMdesk\desktop\Roldan Files\Business Pattern\13_Alteryx\BAP-Payoff Detail\Output\Output\Recon Tracker 01/31/2022.xlsx|||Recon Tracker 01/31/2022

This means that the result did not shorten and the name only.

Thanks,

Kamen

binu_acs

@KamenRider

REGEX_Replace([RAWRECON], ".*\|\|\|(.+)", "$1")

fileName1.yxmd

binu_acs

@KamenRider another option using formula

substring([RAWRECON], FindString([RAWRECON], '|')+3,Length([RAWRECON]))

KamenRider

Hi @binu_acs

Finally your last formula using substring works. I can't understand but maybe you can tell me why your previous formula like regex is not working. Thanks a lot for your patience in helping me out to come up with the correct formula.

substring([RAWRECON], FindString([RAWRECON], '|')+3,Length([RAWRECON]))

Thanks,

Kamen

binu_acs

@KamenRider the Regex forumla REGEX_Replace([RAWRECON], ".*\|\|\|(.+)", "$1") first looking for the combination of the characters ||| and what ever comes after these characters is assigned to a group (.+) . Can you check your path again with this logic and try to analyze why it is not working.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335