If you have any control over it, the first thing I'd do is set it so that anyone who enters information that contains commas, use a replace() to change the comma with it's ASCII equivalent so it won't throw the CSV into a tizzy.
As far as the double-quote, can you use replace() to remove it from output? Or does the data, itself, need changing before being used for something else?
INSERT INTO property (Amenities) VALUES ('#replace(listGetAt(index,1),'"','','all')#')
PS. Personally, if I can avoid using CSVs for anything, I do (comma-delimited and tab-delimited). Can you request the data be provided in a different format?
Thank you for your help. I am beginning to see what you mean about csv files. I can download it it at comma delimited text file. What should I look out for or do differently for my data being formatted like this?
With Txt File:
<cffile action="read"file="http://website.com/testtmk.txt" variable="data">
<cfloop index="index" list="#data#" delimiters= "#chr(9)#" >
<cfquery datasource="X" username="Y" password="Z" timeout="90">
INSERT INTO property
Error Executing Database Query.
Field 'Association' doesn't have a default value The error occurred in D:/home/manpcs.com/wwwroot/Untitled_cfm.cfm: line 8
6 : (Amenities) 7 : VALUES 8 : ('#replace(listGetAt(index,1),'"','','all')#') 9 : 10 : </cfquery>
sqlState HY000 datasource pcs vendorErrorCode 1364 sql INSERT INTO property (Amenities) VALUES ('BO1F')
How do I get all of information to get inserted into the database?
Thank you in advance.
We'd have to see what the structure of your table looks like. Do you have a column in that table called "Association" that has a required constraint on it? If so, you have to provide a value for that column in your <cfquery>.