locked
How to read the file name from folder RRS feed

  • Question

  • Hi,

     I need to find the file name in a particular folder for ex:E:\Test in that daily i am getting files with date format.txt files i need to extract or read  todays file only. 21-09-2011.txt is todays file hw to read the file name.

                  can any one help

    Wednesday, September 21, 2011 10:18 AM

Answers

All replies

  • I think SSIS Package is what you need. You can put a flat file source in foreach loop container which will store the file names in a variable and will insert the same in a table.

    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Proposed as answer by Naomi N Wednesday, September 21, 2011 1:05 PM
    Wednesday, September 21, 2011 10:29 AM
  • declare @file_name varchar(200)
    set @file_name = convert( varchar(10), getdate(), 105 ) + '.txt'
    declare @t table(val varchar(max))
    insert into @t
    exec master..xp_cmdshell 'dir c:\temp\'
    
    if exists ( select * from @t where val like '%' + @file_name + '%')
    begin
    set @file_name = 'type c:\temp\' + @file_name
    exec master..xp_cmdshell @file_name
    end
    


    http://www.t-sql.ru
    Wednesday, September 21, 2011 10:34 AM
  • DECLARE @cmd VARCHAR(300);
    SET @cmd = 'O:\Temp\*.txt' 
    SET @cmd='dir /b ' + '"' + @cmd  + '"' 
    DECLARE @files TABLE(fn VARCHAR(64));
    INSERT @files EXEC master..xp_cmdshell @cmd;
    SELECT * FROM @files WHERE fn LIKE @yourparam

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 21, 2011 10:38 AM
    Answerer
  • Hi

    Check OPENROWSET in BOL,  Section E Using the OPENROWSET BULK provider with a format file to retrieve rows from a text file

    declare @file_name varchar(200)
    set @file_name = convert( varchar(10), getdate(), 105 ) + '.txt'
    declare @t table(val varchar(max))
    insert into @t
    exec master..xp_cmdshell 'dir c:\temp\'
    
    if exists ( select * from @t where val like '%' + @file_name + '%')
    begin
    	SELECT a.* FROM OPENROWSET( BULK @file_name, 
       	FORMATFILE = 'c:\test\values.fmt') AS a;
    end

    VT

    Wednesday, September 21, 2011 10:47 AM
  •  It is showing an error

     

    SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

    Wednesday, September 21, 2011 12:23 PM
  • In General access for xps shell command are not provided due to all users due to security reasons. If your requirement is only for DBAs than we can have them. But if the stored proc will get executed by client applications than there might be security issue for not provided access. SSIS would be the best choice.

    Hope this helps!!

    Wednesday, September 21, 2011 12:30 PM
  • For security  reason xp_cmdshell is disabled, you need to enable it.

    type this in management studio 

     

     

    sp_configure 'show advanced',1

    reconfigure

    sp_configure 'xp_cmdshell',1

     

    reconfigure

     

    or in SQL 2005 you can enable it using surface area configuration tool

     

    vt

    Wednesday, September 21, 2011 12:32 PM
  • Hi,
    First you need to configure on your server.

    EXEC sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    EXEC sp_configure 'xp_cmdshell', 1
    GO
    RECONFIGURE
    Shatrughna
    Wednesday, September 21, 2011 12:33 PM
  • It is showing incorrrect syntax near @filename. Filename is E:\Test\21-9-2011.txt

    declare @file_name varchar(200)
    set @file_name = convert( varchar(10), getdate(), 105 ) + '.txt'

    --select @file_name as filename
    declare @t table(val varchar(max))
    insert into @t
    exec master..xp_cmdshell 'dir E:\Test\'

    if exists ( select * from @t where val like '%' + @file_name + '%')
    begin
        SELECT a.* FROM OPENROWSET( BULK @file_name,

           FORMATFILE = 'E:\Test\values.fmt') AS a;

    end
    Msg 102, Level 15, State 1, Line 11
    Incorrect syntax near '@file_name'.

    • Edited by Himabindhu Wednesday, September 21, 2011 12:37 PM
    Wednesday, September 21, 2011 12:33 PM
  • Try this

     

    declare @file_name varchar(200)
    set @file_name = convert( varchar(10), getdate(), 105 ) + '.txt'

    --select @file_name as filename
    declare @t table(val varchar(max))

    declare @c nvarchar(1000)

     
    insert into @t
    exec master..xp_cmdshell 'dir E:\Test\'

    if exists ( select * from @t where val like '%' + @file_name + '%')
    begin
              set @c='SELECT a.* FROM OPENROWSET( BULK '''+ @file_name +''', FORMATFILE = ''E:\Test\values.fmt'') AS a;'
    exec sp_executesql @c
    end

    Wednesday, September 21, 2011 12:50 PM
  • Thanks ,it is working fine.......
    Wednesday, September 21, 2011 12:52 PM
  • I had a  doubt that if the file not exits it is showing as 10 rows are affected.
    Wednesday, September 21, 2011 12:55 PM
  • Hi,
    It is showing because of this statement

    insert into @t

    exec master..xp_cmdshell 'dir E:\Test\'

    Inserting all files in t table.
    Shatrughna
    Wednesday, September 21, 2011 1:29 PM
  • hi,

    what is the need of 'type' here in @file_name if remove that i shows an error.

    Thursday, September 22, 2011 5:31 AM
  • Hi,

     

    Please read this article: 

    http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

    It will give u brief idea, How to read file name using SSIS package.

     

    Thanks,

    Mohit G

    Thursday, September 22, 2011 7:15 AM
  • Hi All,

    This is my procedure and for some reason, it is not reading the file in my folder

    ALTER procedure GetFilesFromFolder
    as
    DECLARE @cmd VARCHAR(300);
    SET @cmd = 'C:\CheckPro\*.docx'
    SET @cmd='dir /b ' + '"' + @cmd  + '"'
    DECLARE @files TABLE(fn VARCHAR(64));
    INSERT @files EXEC master..xp_cmdshell @cmd;
    SELECT COUNT(*) as No_of_records FROM @files WHERE fn LIKE convert( varchar(10), getdate(), 105 ) + '.docx'

    ----Prior to executing this I had run these as mentioned in this thread


    EXEC sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    EXEC sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    EXEC sp_configure 'xp_cmdshell', 1
    GO
    RECONFIGURE

    -----Can someone tell why its not working for me


    Sharath
    • Edited by Sharath048 Thursday, September 22, 2011 10:04 AM
    Thursday, September 22, 2011 10:02 AM
  • The following article deals with the same topic:

    http://www.sqlusa.com/bestpractices2008/pathfilename/

    To import flat file into a table:

    http://www.sqlusa.com/bestpractices2005/notepad/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by Kalman Toth Wednesday, November 16, 2011 6:49 PM
    Tuesday, September 27, 2011 2:32 PM