2 Replies Latest reply on Sep 9, 2009 10:49 AM by brianism

    Looping through a directory of files and inserting them into database

    brianism Level 1

      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>

        • 1. Re: Looping through a directory of files and inserting them into database
          Dan Bracuk Level 5

          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?

          • 2. Re: Looping through a directory of files and inserting them into database
            brianism Level 1

            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>