Skip to main content

Hello

I currently have a column with the following file name in one of my tables. 
C:\Users\John\OneDrive - Sales Solutions\Desktop\TimeXtender\TMX-DataSamples\RLZ\BESTERS - BESTERS POINT - BESTERS IND_DEC 22_101602_0.txt

Is there a way to remove the path name in the column to only have the file name?

I have a file for each month and have merged the files so all the data is in one table. 

For example, remove this piece:
 C:\Users\John\OneDrive - Sales Solutions\Desktop\TimeXtender\TMX-DataSamples\RLZ\

and only show this piece:
BESTERS - BESTERS POINT - BESTERS IND_DEC 22 as the file name.


I also need to have the Month Year (DEC 22) of each file copied into a new column called “Date”.

 

Is this at all possible?

Thank you

 

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('_', aFileName], charindex('_', aFileName])+1)-1)

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

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

 


Reply