Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.
Looking for a quicker way to check files
We have several folders containing 3000+ files each. They contain scanned pdfs. Some of our staff have been scanning in blank pages to get around the system, and I have just finished going through them all by thumbnailing them via CFPDF and marking the offending files. I have all the filenames (good and bad) in a table.
I now need to keep up this task, looking at all new files added to the folders each week. I thought of the dateLastModified of CFDirectory, but some of the files will have old dates on them. Is there a quicker/better way (instead of going through a CFDirectory listing and querying the table for each file to see if it has already been processed)?
I now need to keep up this task, looking at all new files added to the folders each week. I thought of the dateLastModified of CFDirectory, but some of the files will have old dates on them. Is there a quicker/better way (instead of going through a CFDirectory listing and querying the table for each file to see if it has already been processed)?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Unfortunately, I don't think there's a simple method for doing this.
Edit: I agree that querying file by file is not the way to go. What I've done in the past is to generate a list of file names and save it to a .txt file. Then use db tools (like sql server's BULK INSERT) to import the file into a temporary table. Finally, use a query to compare the file names and find out what's missing. It sounds more complicated than it really is. I think I posted an example of this somewhere. I'll see if I can find it.
Edit: I agree that querying file by file is not the way to go. What I've done in the past is to generate a list of file names and save it to a .txt file. Then use db tools (like sql server's BULK INSERT) to import the file into a temporary table. Finally, use a query to compare the file names and find out what's missing. It sounds more complicated than it really is. I think I posted an example of this somewhere. I'll see if I can find it.
Quick questions -
1) I forgot, are you on windows or *nix?
2) Do you have cfexecute access on your server? While you could generate the file list with cfdirectory, o/s commands like LS or DIR are faster.
3) Which db - SQL Server or MySQL?
1) I forgot, are you on windows or *nix?
2) Do you have cfexecute access on your server? While you could generate the file list with cfdirectory, o/s commands like LS or DIR are faster.
3) Which db - SQL Server or MySQL?
I couldn't find the post I was thinking of (It'll probably turn up later - now that I've created a new one ;-)
Anyway, here's the basic idea:
1. Set the basic file paths (folder to check and path to output file)
2. Use DIR to generate the list of file names
3. If the file is successfully was generated, import it into a #temp table. Then do some sort of JOIN with your main table to identify the "new" files. For example, I used an OUTER JOIN to identify files that exist on disk but NOT in the existing db table: "YourMainTable"
Full code:
Anyway, here's the basic idea:
1. Set the basic file paths (folder to check and path to output file)
2. Use DIR to generate the list of file names
3. If the file is successfully was generated, import it into a #temp table. Then do some sort of JOIN with your main table to identify the "new" files. For example, I used an OUTER JOIN to identify files that exist on disk but NOT in the existing db table: "YourMainTable"
SELECT TOP 100 tmp.*
FROM ##TempImport tmp LEFT JOIN YourMainTable main
ON main.FileName = tmp.FileName
WHERE main.FileName IS NULL;
Full code:
<!--- folder to check for files --->
<cfset folderToCheck = "c:\somefolder\">
<!--- output file containing the file names --->
<cfset saveToFile = ExpandPath("./"& CreateUUID() &".txt")>
<!--- generate list of files --->
<cfexecute name = "C:\windows\System32\cmd.exe"
arguments = '/C dir /a-d /b "#folderToCheck#" '
outputFile="#saveToFile#"
timeout = "60"
errorVariable="errorMessage"></cfexecute>
<!--- import into db and compare --->
<cfif FileExists(saveToFile) AND NOT Len(errorMessage)>
<cfquery name="qImport" datasource="MSSQL">
<!--- temp storage --->
CREATE TABLE ##TempImport
(
FileName VARCHAR(500)
);
<!--- import file names --->
BULK INSERT ##TempImport
FROM '#saveToFile#';
<!--- query to find file names that do NOT exist in the existing db table --->
SELECT TOP 100 tmp.*
FROM ##TempImport tmp LEFT JOIN YourMainTable main
ON main.FileName = tmp.FileName
WHERE main.FileName IS NULL;
<!--- cleanup --->
DROP ##TempImport
</cfquery>
<cfdump var="#qImport#">
<!--- cleanup temp file --->
<cfset FileDelete(saveToFile)>
<cfelse>
ERROR: Temp file not created
<cfoutput>#errorMessage#</cfoutput>
</cfif>
BTW, the above is a quick and dirty example to demonstrate the concept. In real code, I'd put this logic in a stored procedure and pass in the file name as a parameter:
I am getting the following error on line 30
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Unknown object type '#TempImport' used in a CREATE, DROP, or ALTER statement.
<!--- query to find file names that do NOT exist in the existing db table --->
29 : SELECT TOP 100 tmp.*
30 : FROM ##TempImport tmp LEFT JOIN CF_Temp#Sfx#.dbo.CheckPPTb
31 : ON main.FileName = tmp.FileName
32 : WHERE main.FileName IS NULL;
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Unknown object type '#TempImport' used in a CREATE, DROP, or ALTER statement.
<!--- query to find file names that do NOT exist in the existing db table --->
29 : SELECT TOP 100 tmp.*
30 : FROM ##TempImport tmp LEFT JOIN CF_Temp#Sfx#.dbo.CheckPPTb
31 : ON main.FileName = tmp.FileName
32 : WHERE main.FileName IS NULL;
(Edit) Oh wait... I pasted the wrong version. My bad. It didn't seem to like the UUID file name, so I used this instead
<cfset saveToFile = ExpandPath("./fileNames.tx
<cfset saveToFile = ExpandPath("./fileNames.tx
Cool. You probably figured this out already :) but .. for the archives, to use dynamic file names you need to build a SQL string and execute it via exec or sp_executesql. See the BOL for an example
Premium Content
You need an Expert Office subscription to comment.Start Free Trial