Help Returning File Attribute Values From SELECT Statement

This example over simplies the situation that I have but allows me to ask the question clearly without giving tons and tons of details.

I have an application that copies files to a folder structure and creates a record in a table pointing to said file.  I'd like to write a SELECT statement that allows me to confirm that the number of records matches the number of files in the folder.

Is there a SQL function that will return the count of files in a folder?

In my xBase (FoxPro/Clipper) days the functions where there.  I would have accomplished this with the following:
SELECT LEN(ADIR(drive:\folder)) AS 'File Count'

ADIR was a funtion that poputated several attributes (name, ext, size, date, etc...) of each file into an array.  Going for the length of the array gives the number of files in the array/dir.

Anything similar in SQL?

Thanks!!
jyancey241Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mfsamuelCommented:
I was not able to find any direct access to do what you want, but I was able to find a script that uses the command line to run what you need.

http://www.mssqltips.com/tip.asp?tip=1263

This basically runs a "dir folder_path" command and looks at just the files in the folder.  does do what you want, just a round about method.  best I could find.

let me know if you need this reworked to your specific application.


/*
----------------------------------------------------------------------------
-- Object Name: Script
-- Project: Misc
-- Business Process: Supports SQL Server 2000 and 2005
-- Purpose: Capture the files in a specific directory with xp_cmdshell
-- Database: N\A
-- Dependent Objects: 
-- Called By: N\A
-- Upstream Systems: N\A
-- Downstream Systems: N\A
-- 
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
--
*/ 
SET NOCOUNT ON
 
-- 1 - Variable declarations
DECLARE @CMD1 varchar(5000) 
DECLARE @CMD2 varchar(5000)
DECLARE @FilePath varchar(200)
 
-- 2 - Create the #OriginalFileList temporary table to support the un-cleansed file list
CREATE TABLE #OriginalFileList (
Col1 varchar(1000) NULL
)
 
-- 3 - Create the #ParsedFileList temporary table to suppor the cleansed file list
CREATE TABLE #ParsedFileList (
PFLID INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
DateTimeStamp datetime NOT NULL,
FileSize varchar(50) NOT NULL,
FileName1 varchar (255) NOT NULL
)
 
-- 4 - Initialize the variables
SELECT @CMD1 = ''
SELECT @CMD2 = '' 
SELECT @FilePath = 'C:\Progra~1\Micros~2\MSSQL.1\MSSQL\Log\'
 
-- 5 - Build the string to capture the file names in the restore location
SELECT @CMD1 = 'master.dbo.xp_cmdshell ' + char(39) + 'dir ' + @FilePath + '\*.*' + char(39)
 
-- 6 - Build the string to populate the #OriginalFileList temporary table
SELECT @CMD2 = 'INSERT INTO #OriginalFileList(Col1)' + char(13) +
'EXEC ' + @CMD1
 
-- 7 - Execute the string to populate the #OriginalFileList table
EXEC (@CMD2)
 
-- 8 - Delete unneeded data from the #OriginalFileList
DELETE FROM #OriginalFileList
WHERE COL1 IS NULL
 
DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Volume%'
 
DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Directory%'
 
DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%<DIR>%'
 
DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%bytes%'
 
-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList (DateTimeStamp, FileSize, FileName1)
SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList
 
-- ********************************************************************************
-- INSERT code here to process the data from the #ParsedFileList table 
-- ********************************************************************************
 
-- 10 - Drop the temporary tables
DROP TABLE #OriginalFileList
DROP TABLE #ParsedFileList
 
SET NOCOUNT OFF
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jyancey241Author Commented:
This was exactly what I needed.  Thank You!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2003

From novice to tech pro — start learning today.