SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pulling Filename from Windows directory into table


Pulling Filename from Windows directory into table

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214586 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
erouse
erouse
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 151
Attached file shows results 250 to 260. Note that after row 255 all the file details are the same yet the row number changes.
Attachments
row255.txt (15 views, 4.00 KB)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214586 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
erouse
erouse
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 151
Yes I can use xp_cmdshell
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214586 Visits: 41979
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)
        Wink

--===== 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214586 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
brianparow
brianparow
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214586 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
brianparow
brianparow
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214586 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search