remove quotation mark

New Here ,
Dec 29, 2015 Dec 29, 2015

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> 

sqlState  HY000
datasource  pcs
vendorErrorCode  1364
sql   INSERT INTO property (Amenities) VALUES ('"BO1F')

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.

Views

334

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

LEGEND , Dec 30, 2015 Dec 30, 2015
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...

Likes

Translate

Translate
LEGEND ,
Dec 30, 2015 Dec 30, 2015

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 30, 2015 Dec 30, 2015

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> 

sqlState  HY000
datasource  pcs
vendorErrorCode  1364
sql   INSERT INTO property (Amenities) VALUES ('BO1F')

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Dec 30, 2015 Dec 30, 2015

Copy link to clipboard

Copied

LATEST

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 30, 2015 Dec 30, 2015

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines