Skip to main content
Alan_Koenig_920
Alan_Koenig_920
Known Participant
Dec 30, 2015 3:41 AM UTC
Answered

remove quotation mark

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 is closed to new replies. Start a new post to keep the conversation going.
    WolfShade
    WolfShadeCorrect answer
    Legend
    Dec 30, 2015 2:22 PM UTC

    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?

    View reply

    4 replies

    Alan_Koenig_920
    Alan_Koenig_920Author
    Known Participant
    Dec 30, 2015 8:03 PM UTC

    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?

    Translate
    WolfShade
    WolfShadeCorrect answer
    Legend
    Dec 30, 2015 2:22 PM UTC

    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?

    Translate
      Alan_Koenig_920
      Alan_Koenig_920Author
      Known Participant
      Dec 30, 2015 8:58 PM UTC

      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

      Translate
      Carl Von Stetten
      Carl Von Stetten
      Legend
      Dec 30, 2015 9:22 PM UTC

      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.

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