?
Solved

Help Returning File Attribute Values From SELECT Statement

Posted on 2008-02-04
2
Medium Priority
?
469 Views
Last Modified: 2012-05-05
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!!
0
Comment
Question by:jyancey241
2 Comments
 
LVL 5

Accepted Solution

by:
mfsamuel earned 500 total points
ID: 20818654
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
 

Author Closing Comment

by:jyancey241
ID: 31427938
This was exactly what I needed.  Thank You!!
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question