Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Pulling Filename from Windows directory into table Expand / Collapse
Author
Message
Posted Thursday, September 17, 2009 7:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
fm447k (8/10/2009)
How do I email the list of filenames from the table? I'm using SQL 2005 and have configured Database Mail.


Apologies here because I don't know. They have Lotus Notes and SQL Server 2000 and have the email system setup where I can't even do automatic DBA notifications where I'm currently working. I had to use CDONTS to setup any kind of automated email and that differs a whole lot from system to system. Because it's operating on SS2k, it has an 8k limit (unless I used the TEXT data type and I'm not going there) so I end up writing such things out to a file and then identify the file as an attachment. It's a real pain.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #790053
Posted Friday, September 18, 2009 6:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 30, 2014 1:07 PM
Points: 4, Visits: 66
Attached file shows results 250 to 260. Note that after row 255 all the file details are the same yet the row number changes.

  Post Attachments 
row255.txt (6 views, 4.21 KB)
Post #790324
Posted Friday, September 18, 2009 6:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
erouse (9/18/2009)
Attached file shows results 250 to 260. Note that after row 255 all the file details are the same yet the row number changes.


That looks like a bug in the sp_OA* canned sprocs. I guess I've never gotten there because I always move processed files to a different directory. Thanks for posting the text file. I'll see if I can recreate the problem tonight and maybe even come up with an alternate.

With that in mind, can you use xp_CmdShell on your server(s)? There's a very fast method we can use there but it does require a dip into the xp_CmdShell world.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #790334
Posted Friday, September 18, 2009 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 30, 2014 1:07 PM
Points: 4, Visits: 66
Yes I can use xp_cmdshell
Post #790371
Posted Saturday, September 19, 2009 2:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
Apologies for the delay. This will do the trick so long as you understand that if MS ever changes the spacing on the output of the DOS DIR command, this proc will need to be tweaked.

CREATE PROCEDURE dbo.GetDirectoryFileInfo
/****************************************************************************************
PURPOSE:
This utility stored procedure returns the long filename, CreateDate, LastModifiedDate,
and the file size in Bytes from any given directory or UNC.

INPUT PARAMETERS:
The unquoted \\MachineName\Path or d:\Path where "d:" is the drive letter. Wildcards
may be used for file names and extensions.  Only path information is allowed. Inclusion
of anything not interpreted as a valid path will cause an empty result set to be
returned for security reasons.

OUTPUTS:
Column name      DataType     Description
-----------      --------     ----------------------------------------------------------
RowNum           INTEGER      Sequential number
FileName         VARCHAR(256) Filename and extension from the DIR command
CreateDate       DATETIME     Date the file was created on
LastModifiedDate DATETIME     Date the file was last modified
Bytes            BIGINT       The number of bytes the file contains

If the path is not found, is empty, the parameter passed was not an actual path, or
the permissions to access a legitimate path does not exist for MS-SQL Server, the stored
procedure will return an empty result set.  This is partially for security reasons...
if a hacker gets no return, they don't know if they're on the right track or not.

REVISION HISTORY:
Rev 00 - Jeff Moden - Initial creation and unit test
****************************************************************************************/
--===== Declare I/O parameters
@pPath VARCHAR(512) --The path info and wildcards to be used with a DIR command

    
AS

--=======================================================================================
--===== Presets
--=======================================================================================
--===== Supress the autodisplay of rowcounts for appearance and speed
    
SET NOCOUNT ON

--===== Declare local variables
DECLARE @Command VARCHAR (300) --Holds the dynamic DOS command for the DIR command

--===== If the temp table that holds the Directory output is not null, drop the table
    
IF OBJECT_ID('TempDB..#DosOutput') IS NOT NULL
        
DROP TABLE #DosOutput

--===== Create the temp table that holds the Directory output
CREATE TABLE #DosOutput
        
(
        
RowNum INT IDENTITY(1,1),
        
Data VARCHAR(300)
        )

--===== If the temp table that holds the file information is not null, drop the table
    
IF OBJECT_ID('TempDB..#FileInfo') IS NOT NULL
        
DROP TABLE #FileInfo

--=======================================================================================
--===== Get the directory information and the LastModifiedDate for lines with files only.
--=======================================================================================

--===== Setup to do a "DIR" with the following switches
     -- /TW  = Date/Time file was last written to (LastModifiedDate)
     -- /-C  = List number of bytes without commas
     -- Enclose the @pPath variable in quotes to all for paths with spaces.
    
SET @Command = 'DIR "' + @pPath + '" /TW /-C'

--===== Execute the "DIR" command and save the output in #DosOutput
     -- (order preserved by the Primary Key)
INSERT INTO #DosOutput (Data)
  
EXEC Master.dbo.xp_CmdShell @Command

--===== Parse the Dos output into the file info table.
     -- The criteria in the WHERE clause ensures only file info is returned
SELECT
        
IDENTITY(INT,1,1) AS RowNum,
        
SUBSTRING(Data,40,256) AS [FileName],
        
CAST(NULL AS DATETIME) AS CreateDate, --Populated on next step
        
CONVERT(DATETIME,SUBSTRING(Data,1,23)) AS LastModifiedDate,
        
CAST(SUBSTRING(Data,22,17) AS BIGINT) AS Bytes
  
INTO #FileInfo
  
FROM #DosOutput
  
WHERE SUBSTRING(Data,15,1) = ':' --Row has a date/time on it
    
AND Data NOT LIKE '%<DIR>%'    ---Row is not a directory listing

--=======================================================================================
--===== Update each file's info with the CreateDate
--=======================================================================================

--===== Setup to do a "DIR" with the following switches
     -- /TC  = Date/Time File was created (CreateDate)
     -- Enclose the @pPath variable in quotes to prevent SQL Injection attacks
    
SET @Command = 'DIR "' + @pPath + '" /TC'

--===== Clear the #DosOutput table
TRUNCATE TABLE #DosOutput

--===== Execute the "DIR" command and save the output in #DosOutput
     -- (order preservation not important here)
INSERT INTO #DosOutput (Data)
  
EXEC Master.dbo.xp_CmdShell @Command

--===== Parse the DOS output table for the CreateDate and add it to the
     -- file info table.
UPDATE #FileInfo
    
SET CreateDate = CONVERT(DATETIME,SUBSTRING(do.Data,1,23))
  
FROM #FileInfo fi,
        
#DosOutput do
  
WHERE fi.FileName = SUBSTRING(do.Data,40,256) --Filenames match
    
AND SUBSTRING(do.Data,15,1) = ':' --Row has a date/time on it
    
AND do.Data NOT LIKE '%<DIR>%'    --Row is not a directory listing

--=======================================================================================
--===== Return a result set to the calling object
--=======================================================================================
SELECT * FROM #FileInfo

--===== Exit the proc with no error reporting for security reasons
RETURN
GO



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #790834
Posted Saturday, September 19, 2009 2:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
So far as why the filenames begin duplication at the 256th file, I have no idea. It must be a limit in the sp_AO* sprocs. I can't imagine that the file system object would have such a limit.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #790838
Posted Saturday, September 18, 2010 7:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 21, 2010 6:15 PM
Points: 3, Visits: 24
Jeff,

Your SP is exactly what I've been looking for.
I just have one problem. I can't seem to find the temp table it created.
Is the table somehow dropped after running the SP?
when I try to execute the following:
SELECT * FROM #FileDetails

Results
Msg 208, Level 16, State 0, Line 1
Invalid object name '#FileDetails'.

I'm using SQL Server Express 2005 and I'm kinda new to SQL

I'm looking to get the filenames and attributes into a permanent table to automate alot of manual work.
I also do not want to delete the filename from the table, just append to it if the filename doesn't already exist.

Thanx in advance Jeff and hope you can help
Post #988826
Posted Sunday, September 19, 2010 2:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
brianparow (9/18/2010)
Jeff,

Your SP is exactly what I've been looking for.
I just have one problem. I can't seem to find the temp table it created.
Is the table somehow dropped after running the SP?
when I try to execute the following:
SELECT * FROM #FileDetails

Results
Msg 208, Level 16, State 0, Line 1
Invalid object name '#FileDetails'.

I'm using SQL Server Express 2005 and I'm kinda new to SQL

I'm looking to get the filenames and attributes into a permanent table to automate alot of manual work.
I also do not want to delete the filename from the table, just append to it if the filename doesn't already exist.

Thanx in advance Jeff and hope you can help


Let me ask you... do you really need the attributes or do the names of the files contain enough information (such as a date)?

So far as your question goes, a "#" at the beginning of a file signifies a "temporary table" which is scope sensitive. It automatically drops when the stored procedure completes. I suppose you could change it a bit to keep the data active in a real table so that you could log process dates and the like.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #989003
Posted Sunday, September 19, 2010 6:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 21, 2010 6:15 PM
Points: 3, Visits: 24
Thanx Jeff,
I already altered the SP to do just that.

I do not really need the attributes except for the size.
and I guess I probably do not need to keep a permanent table.
What I'm trying to do is grab the filenames from a directory and see if there are 2 files with the same name and different extensions..
ie:
123.txt and 123.pdf
I want it to check and find all txt files that do not have a corresponding pdf file and output those txt filenames to the results of the query.
I have been trying to work on a query using:
left(FileDetails.[Name],len(FileDetails.[Name])-4)

This will give me the filenames without the extension and I've been trying for a while to get my results to no avial...
Can anybody help me?
Post #989047
Posted Sunday, September 19, 2010 7:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
brianparow (9/19/2010)
What I'm trying to do is grab the filenames from a directory and see if there are 2 files with the same name and different extensions..
ie:
123.txt and 123.pdf
I want it to check and find all txt files that do not have a corresponding pdf file and output those txt filenames to the results of the query.


Heh... jeez Brian... why didn't you just say so at the beginning??? Problem solved... read the comments in the code that follows

--===== Conditionally drop temp tables to make reruns easier
IF OBJECT_ID('TempDB..#FileList','U') IS NOT NULL
DROP TABLE #FileList

--===== Create the working temp table with a couple of calculated columns to isolate the extention
-- and just the file name.
CREATE TABLE #FileList
(
FullFileName SYSNAME,
Level TINYINT,
IsFile TINYINT,
FileName AS SUBSTRING(FullFileName,1,LEN(FullFileName)-CHARINDEX('.',REVERSE(FullFileName))),
Extension AS RIGHT(FullFileName,CHARINDEX('.',REVERSE(FullFileName)))
)

--===== Populate the table with file names from the given directory or UNC
-- (YES, you CAN convert this section to dynamic SQL if you need to)
INSERT INTO #FileList
(FullFileName, Level, IsFile)
EXEC xp_DirTree 'C:\Temp',1,1 --<<<<Change to the desired directory here

--===== Ok... let see what that has in it. Not part of the problem but thought you should see it.
SELECT * FROM #FileList

--===== And finally, list all filenames that have a .txt extension but DON'T have a .PDF extension
SELECT FileName FROM #FileList WHERE Extension = '.txt' AND IsFile = 1
EXCEPT
SELECT FileName FROM #FileList WHERE Extension = '.pdf' AND IsFile = 1




--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #989057
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse