SQL Server - CSV FileName Getting issues

Asked By harsh shah on 05-Sep-11 04:58 AM
Hi All,

Good AfterNoon.

i want to fetch all CSV FileName From perticular Folder Through Sql server.

i have one folder 'c:\MyFolder'

this Folder Contains 10 Files. 10 of them 3 Files Are .CSV and other 7 are Excel file.

on that time i want to fetch 3 CSV Files Through Sql server .


please help me how can i achive this.

Regards,

Harsh Shah
Asked By harsh shah on 05-Sep-11 05:22 AM
Hi Tom,

Thanx

i want code for sql server. not in C#
i was mention i need through sql server.

Regards,

Harsh Shah
Anoop S replied to harsh shah on 05-Sep-11 06:14 AM
this code used to find List Files In Directory Using SQL

SET NOCOUNT ON

DECLARE @Command VARCHAR(100)
SET @Command = 'dir /b /s '

DECLARE @Folder VARCHAR(100)
SET @Folder = 'D:\AS'

DECLARE @FilesInAFolder TABLE  (FileNamesWithFolder VARCHAR(500))
INSERT INTO @FilesInAFolder
EXEC MASTER..xp_cmdshell @Command

; WITH CTE AS
(
  SELECT REVERSE(FileNamesWithFolder) ReverseFileNames FROM @FilesInAFolder
)
SELECT --FileNames = STUFF ( FileNamesWithFolder , 1 , (LEN(FileNamesWithFolder) - CHARINDEX ('\', REVERSE(FileNamesWithFolder))+1) , '')
    FileNames = REVERSE ( LEFT (ReverseFileNames, CHARINDEX ('\', ReverseFileNames)-1))
FROM  CTE
WHERE ReverseFileNames IS NOT NULL
Reena Jain replied to harsh shah on 05-Sep-11 10:03 AM
hi,

like this you can get the file extension in sql server
CREATE PROCEDURE [dbo].[spGetDocumentNature]
 -- Add the parameters for the stored procedure here
  @FILE_PATH   VARCHAR(MAX)
 ,@EXTN     VARCHAR(10) OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
--DECLARE @FILE_PATH VARCHAR(MAX)
DECLARE @FILE_LENGTH INT
DECLARE @FILE_NATURE VARCHAR(10)
  
 SET @FILE_LENGTH = LEN(@FILE_PATH)
 WHILE (@FILE_LENGTH <> 0)
 BEGIN
  IF SUBSTRING(@FILE_PATH, @FILE_LENGTH, 1) = '.'
   BEGIN
  SET @FILE_NATURE = RIGHT(@FILE_PATH, LEN(@FILE_PATH) - @FILE_LENGTH)
  BREAK
   END
 SET @FILE_LENGTH = @FILE_LENGTH - 1
 END
   
 SET @EXTN = @FILE_NATURE
 RETURN
END
 
Devil Scorpio replied to harsh shah on 05-Sep-11 11:17 PM
Hi Harsh,

Here's an example using BULK insert. you can do this via SSIs as well, but it's a different technique. In example below, there are 4 known folders, and an unknown number of files inside the folders.

Assuming all files are the same structure, and get imported into the same table. If you are doing something different, this solution is not for you.
Note this also uses xp_cmdshell to get the list of files...if you have that disabled, and cannot enable it, you can't use this technique either.
--BULK INSERT MULTIPLE FILE 
    --a table to loop thru filenames drop table ALLFILENAMES
    CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))
    --the source table: yours already exists, but needed for this example.
    CREATE TABLE BULKACT(RAWDATA VARCHAR (8000))
    --some variables
    declare @filename varchar(255),
            @path     varchar(255),
      @sql      varchar(8000),
            @cmd      varchar(1000)
    --get the list of files to process:
    --#########################################
    SET @path = 'C:\DB\'
    SET @cmd = 'dir ' + @path + '*.txt /b'
    INSERT INTO  ALLFILENAMES(WHICHFILE)
    EXEC Master..xp_cmdShell @cmd
    UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

    SET @path = 'C:\DB2\'
    SET @cmd = 'dir ' + @path + '*.txt /b'
    INSERT INTO  ALLFILENAMES(WHICHFILE)
    EXEC Master..xp_cmdShell @cmd
    UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

    SET @path = 'C:\DB3\'
    SET @cmd = 'dir ' + @path + '*.txt /b'
    INSERT INTO  ALLFILENAMES(WHICHFILE)
    EXEC Master..xp_cmdShell @cmd
    UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

    SET @path = 'C:\DB4\'
    SET @cmd = 'dir ' + @path + '*.txt /b'
    INSERT INTO  ALLFILENAMES(WHICHFILE)
    EXEC Master..xp_cmdShell @cmd
    UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
    --#########################################
    --cursor loop
    declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.txt%'
    open c1
    fetch next from c1 into @path,@filename
    While @@fetch_status <> -1
      begin
    --bulk insert won't take a variable name, so make a sql and execute it instead:
       set @sql = 'BULK INSERT BULKACT FROM ''' + @path + @filename + ''' '
           + '     WITH ( 
                   DATAFILETYPE = ''char'', 
                   FIELDTERMINATOR = '','', 
                   ROWTERMINATOR = ''\n'', 
                   FIRSTROW = 2 
                ) '
    print @sql
    exec (@sql)

      fetch next from c1 into @path,@filename
      end
    close c1
    deallocate c1