Skip to main content
Alan_Koenig_920
Alan_Koenig_920
Known Participant
December 30, 2015
Answered

remove quotation mark

  • December 30, 2015
  • 2 replies
  • 794 views

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.

    This topic has been closed for replies.
    Correct answer WolfShade

    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?

    2 replies

    Alan_Koenig_920
    Alan_Koenig_920Author
    Known Participant
    December 30, 2015

    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?

    WolfShade
    WolfShadeCorrect answer
    Legend
    December 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.  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?

      Alan_Koenig_920
      Alan_Koenig_920Author
      Known Participant
      December 30, 2015

      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

      Carl Von Stetten
      Carl Von Stetten
      Legend
      December 30, 2015

      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.

        Terms & ConditionsAccessibility statement
        Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices