Best way to insert text file using ColdFusion & SQL?

I have some text files that I have to insert in one of my tables in SQL. I have separate table where my text file names are stored. I have to grab the name from that table and then use the fileOpen() to get the file from specific folder. After I finish that step I have the problem to decide what is the best way to do the next steps. So I have to use fileReadLine() to get the context of each text file row. Also I should not read the first line since that contains only column names. Second I have to check once I hit the end of the file. On the very end I have to loop and do the INSERT into the table. I was wondering is out there any new ways of doing this? My current code has two cfloops and that looks very inefficient. Here is my current code:

<!--- Grab stuff from File Table.  --->
<cfquery datasource="test" name="myQuery1">
    SELECT * 
    FROM FilesTxt
</cfquery> 

<cfloop query="myQuery1"> 
    <!--- Read File --->
    <cfset dataFile = fileOpen(here is my path&"\"&#FileName#, "read" ) /> 
    <cfset line = fileReadLine( dataFile ) />

    <!--- Loop to see if hit the end of file, if not, read next line --->
    <cfloop condition="!fileIsEOF( dataFile )">
        <cfset line = fileReadLine( dataFile ) />

        <cfif trim(line) NEQ "">
           <cfset line = #Replace(line,"'","","ALL")#>
           <cfset line = #Replace(line,'"',"","ALL")#>
           <!--- Build array of junk in the file --->
           <cfset sList = ListToArray(line, chr(9),'yes')>

           <cftry>
                <cfquery datasource="test" name="Insert">
                //Here is my Insert statement
                </cfquery>
           </cftry>
        </cfif>
    </cfloop>
</cfloop>

I was thinking of doing separate loop that will create array with all elements that should be inserted and then to run another loop to do the insert. I'm not sure what is the best approach in this case. If anyone knows any other way please let me know. Thanks

Solution

You can loop file lines directly like so:

<cfloop file="**path/filename**" index="LineOfMyFile">
    <cfoutput>#LineOfMyFile#</cfoutput> 
</cfloop>

The loop terminates at end of file so you don't really need the fileisEOF() function.

Inside your loop you can use list functions rather than looping through an array. If you know the positions of the items in your list. like so:

<cfloop file="**path/filename**" index="LineOfMyFile">

    <cfquery name="myinsert" datasource="#blah#">
        INSERT INTO myTable (col1, col2, col3)
        VALUES (<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#listgetat(lineOfMyFile,1,char(9))#">,
                <cfqueryparam cfsqltype="CF_SQL_CHAR" value="#listgetat(lineofmyFile,2,char(9))#">,
                <cfqueryparam cfsqltype="CF_SQL_CHAR" value="#listgetat(lineofMyFile,3,char(9))#">)
    </cfquery>

</cfloop>

This would be a single loop. Depending on the size of your file it may or may not be more efficient. Also, there are usually checks for type, null, empty strings - stuff like that. So you might have some of that data massaging code before your insert. Hope this helps.