March 4, 2008 at 6:29 pm
Hi I have the following script
--= Verify if table name exists
IF NOT EXISTS (select name from sysobjects where xtype = 'U' and name = 'Test_Table')
--= Create Table
CREATE TABLE Test_Table (Files varchar(100))
--= Populate Test_Table
INSERT INTO Test_Table
SELECT 'D:\File\Lester.xls' UNION ALL
SELECT 'D:\File\Data\test\Test.xls' UNION ALL
SELECT 'D:\Excel\Data\Sheet\Excel.xls'
--= Select in Table Test_Table
Select * from Test_table
This will output the file path ofthe excel file. I want only the filename excluding the path for example
D:\File\Data\test\Test.xls - i only want Test.xls
I can do this by using the reverse command
select reverse(substring (reverse(files),1,(charindex('\',reverse(files))-1))) from test_table
Are there any other solution for this??
"-=Still Learning=-"
Lester Policarpio
March 5, 2008 at 8:03 am
If you input data manually, what if you only input file name?
If you would like to input data programmically, you may consider to use Scripting.FileSystemObject, which can retrieve file name only.
March 5, 2008 at 8:41 am
Sure... dunno how many of these you have to do, but the following is about twice as fast because there's only 1 Reverse and RIGHT is faster than SUBSTRING.
[font="Courier New"]DECLARE @FileName VARCHAR(256)
    SET @FileName = 'D:\File\Data\test\Test.xls'
 SELECT RIGHT(@FileName,CHARINDEX('\',REVERSE(@FileName))-[/font]1)
Of course, if you want to get all file names from a particular directory...
[font="Courier New"]--===== Assign a path for the files to be found
DECLARE @Path VARCHAR(1000)
    SET @Path = 'C:\' --Must be UNC if outside of server
--===== Table to hold all the directory info from xp_DirTree.
     -- Will have both file names and directory names.
 CREATE TABLE #MyDir
        (
        FileNum  INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,
        LongName VARCHAR(1000),
        Depth    INT,
        IsFile   INT
        )
--===== Store all the directory info for path just one level deep
     -- Syntax note: Master.dbo.xp_DirTree path,level,markfiles
     -- "level" should probably always be "1". "0" means "all levels".
     -- "markfiles" will produce extra column "file" if is not null or "0".
 INSERT INTO #MyDir
        (LongName,Depth,IsFile)
   EXEC Master.dbo.xp_DirTree @Path,1,1
--===== Show only the file names...
 SELECT LongName 
   FROM #MyDir
  WHERE IsFile = 1
   DROP TABLE #MyDir
[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2008 at 5:20 am
Thanks never thought of the RIGHT command can i do it without the reverse command?
"-=Still Learning=-"
Lester Policarpio
March 6, 2008 at 6:54 am
Yes, but it will take longer... you can do it with a Tally table and a join on the backslashes using SUBSTRING. I've already tried it (damn... should have kept the code) and it was quite a bit slower.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2008 at 7:10 am
when you can use CLR assembly, it is simple command in .NET System.Path.GetFileName(fullName). create and register user function, template is here http://msdn2.microsoft.com/cs-cz/library/ms131052(en-us).aspx
March 6, 2008 at 7:18 am
Why would you want to create, compile, register, and attach a CLR to do the same thing that was just done in T-SQL?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply