Copy link to clipboard
Copied
My error:
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 11 | ||||||||
9 : 10 : VALUES 11 : ('#listgetAt(index,1 )#') 12 : 13 : </cfquery> | ||||||||
|
My Code:
<cffile action="read"file="http://website.com/testtmk.csv" variable="datacsv">
<cfloop index="index" list="#datacsv#" delimiters= "#chr(10)##chr(13)#" >
<cfquery datasource="X" username="Y" password="Z" timeout="90">
INSERT INTO property
(Amenities)
VALUES
('#listgetAt(index,1 )#')
</cfquery>
</cfloop>
I am pulling from a CSV file. Some of the data in one cell is seperated by comas, I also seem to be picking up some extra " marks. Any help is appreciated.
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?
Something like:
INSERT INTO property
(Amenities)
VALUES
('#replace(listGetAt(index,1),'"','','all')#')
HTH,
^_^
PS. Pe
...Copy link to clipboard
Copied
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?
Something like:
INSERT INTO property
(Amenities)
VALUES
('#replace(listGetAt(index,1),'"','','all')#')
HTH,
^_^
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?
Copy link to clipboard
Copied
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
(Amenities)
VALUES
('#replace(listGetAt(index,1),'"','','all')#')
</cfquery>
</cfloop>
Error:
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> | ||||||||
|
Actual Data:
BO1F,FBO1F,LANDSC,PATDEC,STORAG,WALFEN
How do I get all of information to get inserted into the database?
Thank you in advance.
A
Copy link to clipboard
Copied
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>.
-Carl V.
Copy link to clipboard
Copied
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?