Looping through a directory of files and inserting them into database

[brianism]

Am I doing this correctly?  I'm trying to loop over the folder "/reports" that has .pdf files, all are named accordingly.  I am trying to find a way to loop over all of those files, read their file name and dump that information into my database.

Example:  A .pdf file named "100010_200711.pdf" in the folder  would need to be put into my database "reportinfo" as:

investorcode_id = 100010

year = 2007

month = 11

report = 100650_2004_1_200809.PDF

This is the code I am trying, it is not working:

<cfdirectory directory="/reports" action="list" name="FindItems">

<cfloop query="FindItems">

<cfif LEN(FindItems.URL) EQ "17">     
<cfset year = "#Mid(FindItems.URL, 8, 4)#">
<cfset month = "#Mid(FindItems.URL, 12, 2)#">
</cfif>

<cfif LEN(FindItems.URL) EQ "24">     
<cfset year = "#Mid(FindItems.URL, 15, 4)#">
<cfset month = "#Mid(FindItems.URL, 19, 2)#">
</cfif>

<cfquery name="add" datasource="mydb">

INSERT INTO reportinfo(investorcode_id,year,month,report)
VALUES('#session.investorcode2#','#year#','#month#','#FindItems.URL#')
</cfquery>
</cfloop>

[Dan_Bracuk]

You imply that you want the investorcode_id to be the first x characters of the file name, but your query is using a session variable.

Also, is url a field name in the query returned by your cfdirectory tag?

[brianism]

Well, I did a little more research on CFDIRECTORY and I figured it out. I want to post how I did this incase anyone else runs into it.

Answer:

<cfdirectory directory="pathtomypdffiles" name="FindItems" action="LIST">

<cfloop query="FindItems">

<cfif LEN(name) EQ "17">   
<cfset investorcode_id = "#Mid(name, 1, 6)#">
<cfset year = "#Mid(name, 8, 4)#">
<cfset month = "#Mid(name, 12, 2)#">
</cfif>

<cfif LEN(name) EQ "24">     
<cfset investorcode_id = "#Mid(name, 1, 6)#">
<cfset year = "#Mid(name, 15, 4)#">
<cfset month = "#Mid(name, 19, 2)#">
</cfif>

<cfquery name="add" datasource="mydb">

INSERT INTO reportinfo(investorcode_id,year,month,report)
VALUES('#investorcode_id#','#year#','#month#','#name#')
</cfquery>

</cfloop>