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”.
createtable #Temp (FilePath Varchar(2000))
insertinto #Temp
SELECT'C:\Users\John\OneDrive - Sales Solutions\Desktop\TimeXtender\TMX-DataSamples\RLZ\BESTERS - BESTERS POINT - BESTERS IND_DEC 22_101602_0.txt'createtable #Temp2 ([FileName] Varchar(2000))
INSERTINTO #Temp2
SELECTREPLACE(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’):
createtable #Temp (FilePath Varchar(2000))
insertinto #Temp
SELECT'C:\Users\John\OneDrive - Sales Solutions\Desktop\TimeXtender\TMX-DataSamples\RLZ\BESTERS - BESTERS POINT - BESTERS IND_DEC 22_101602_0.txt'createtable #Temp2 ([FileName] Varchar(2000))
INSERTINTO #Temp2
SELECTREPLACE(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’):
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.