Remove path name from File Name in table column

Hi @Acumen 

I tested the following SQL code

create table #Temp (FilePath Varchar(2000))
insert into #Temp
SELECT 'C:\Users\John\OneDrive - Sales Solutions\Desktop\TimeXtender\TMX-DataSamples\RLZ\BESTERS - BESTERS POINT - BESTERS IND_DEC 22_101602_0.txt'

create table #Temp2 ([FileName] Varchar(2000))

INSERT INTO #Temp2
SELECT REPLACE(SUBSTRING( FilePath , LEN(FilePath) -  CHARINDEX('\',REVERSE(FilePath)) + 2  , LEN(FilePath)),'.txt','')
FROM #Temp

create table #Temp3 ([FileNameShort] Varchar(2000))

INSERT INTO #Temp3
select 
left([FileName], charindex('_', [FileName], charindex('_', [FileName])+1)-1) 
from #Temp2

select * from #Temp2
select * from #Temp3
select 
SUBSTRING(FileNameShort,CHARINDEX('_',FileNameShort)+1,LEN(FileNameShort)) as MonthYear
From #Temp3
Drop Table #Temp
Drop Table #Temp2
Drop Table #Temp3

and it seems to generate the desired output for the given file path

However, whether or not it will work for the other records depends on the file names being structured similarly. Therefore it needs to be tested further with different file paths. 

In terms of how to implement it in TimeXtender, you could create 3 fields each with a custom transformation

Custom transformation for the File Name (i.e. taking the part after the last ‘\’ and removing ‘.txt’):

REPLACE(SUBSTRING( FilePath , LEN(FilePath) -  CHARINDEX('\',REVERSE(FilePath)) + 2  , LEN(FilePath)),'.txt','')

Custom transformation for the File Name Short (i,e, removing the last part ‘_101602_0’ at the end based on the second position of ‘_’):

LEFT([FileName], charindex('_', [FileName], charindex('_', [FileName])+1)-1)

Custom transformation for the MonthYear (i,e, extracting the part after ‘_’ in File Name Short):

SUBSTRING(FileNameShort,CHARINDEX('_',FileNameShort)+1,LEN(FileNameShort))