Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Can I create a query to look for result in a text (csv) file
- Thread starter jmcg
- Start date
cfStarlight
Technical User
You could certainly convert the csv file to a query and run a QoQ on it. But can you describe more about what you're trying to do?
- Thread starter
- #3
I have a form that appends a row to a text file on the server using CFFILE (cannot use a database because of DSN restrictions from server, can only have 1 and cannot change the database used).
When another form is complete I want it to be able to check if a previous form has been completed for the customer in question and then advise the user it is done.
Hope this makes more sense
Thanks for the help
When another form is complete I want it to be able to check if a previous form has been completed for the customer in question and then advise the user it is done.
Hope this makes more sense
Thanks for the help
cfStarlight
Technical User
Even if you're only allowed 1 database can't you still create new tables in that database? I only ask because it sounds like this type of data is better off in a database table.
But if you really can't do that, you could use the CSVToQuery at cflib.org to convert the contents to a query. Then run your checks using a QoQ. You might also want to use some sort of locking when reading/writing the files to avoid conflicts.
<cffile action="read" file="c:\pathToFile\yourFile.csv" variable="csvData">
<cfset qCSVQuery = CSVToQuery(csvData) />
<!--- display query contents --->
<cfdump var="#qCSVQuery#">
<!--- use the appropriate WHERE clause ... --->
<cfquery name="CheckPrevious" dbtype="query">
SELECT *
FROM qCSVQuery
WHERE CustomerName = 'Some Customer'
</cfquery>
(Now for the disclaimer ;-)
The CSVToQuery function was written prior to MX 7. So the returned query does not include data types. You may want to modify the function to include column data types (data types were introduced in MX 7). If you need assistance with it, just post the columns (and type) in your csv file.
But if you really can't do that, you could use the CSVToQuery at cflib.org to convert the contents to a query. Then run your checks using a QoQ. You might also want to use some sort of locking when reading/writing the files to avoid conflicts.
<cffile action="read" file="c:\pathToFile\yourFile.csv" variable="csvData">
<cfset qCSVQuery = CSVToQuery(csvData) />
<!--- display query contents --->
<cfdump var="#qCSVQuery#">
<!--- use the appropriate WHERE clause ... --->
<cfquery name="CheckPrevious" dbtype="query">
SELECT *
FROM qCSVQuery
WHERE CustomerName = 'Some Customer'
</cfquery>
(Now for the disclaimer ;-)
The CSVToQuery function was written prior to MX 7. So the returned query does not include data types. You may want to modify the function to include column data types (data types were introduced in MX 7). If you need assistance with it, just post the columns (and type) in your csv file.
- Status