I'm currently looking for an alternative solution to cfx_Text2Query, as I'm having issues with that for some reason. It appears that it doesn't like to process non-strict files; such as having extra line returns and such.
I've seen some examples in place using a generic text driver via ODBC, but I haven't been able to find really anything that would help me put it into place. This will allow you to basically query the text file and return the results in a CFQUERY tag. Here's the steps I've found.
For this example, I'm using a Windows 2000 Server with ColdFusion 8.
1. Add a new Windows ODBC Data Source using Microsoft Text Driver (*.txt,*.csv). I called it GenericText.
2. Be sure that the ColdFusion ODBC Server is set to Automatic Start and it is started. Otherwise you will get a source not found error.
3. Next go to your ColdFusion Administrator and Add a new data source using the ODBC Socket driver. I also called that GenericText. The Windows ODBC should be listed in the ODBC DSN. Select that one. The rest of the defaults should be fine.
4. Create your query using cfquery in your code.
The query syntax is fairly full functional. It can be as simple as:
<cfquery name="qryTest1" datasource="GenericText">
SELECT *
FROM C:\data\test.csv
</cfquery>
OR more complex such as:
<cfquery name="qryTest1" datasource="GenericText">
SELECT COL1, COL2
FROM C:\data\test.csv
WHERE COL1 IS NOT NULL
</cfquery>
Do you need to strip out the first line, such as a header? Here's what I've come up with:
<!--- Read in uploaded file --->
<cffile action="read" file="#importedFile#" variable="ImportedRead">
<!--- Remove first line --->
<cfset importedRead = ReplaceNoCase(importedRead, "Sample Header#chr(13)##chr(10)#", "")>
<!--- Write out file --->
<cffile action="write" file="#importedFile#" output="#importedRead#">
<!--- Run SQL code from above here --->