4 Replies Latest reply on Dec 30, 2015 1:22 PM by Carl Von Stetten

    remove quotation mark

    Alan Koenig_920

      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.

        • 1. Re: remove quotation mark
          WolfShade Level 4

          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. Re: remove quotation mark
            Alan Koenig_920 Level 1

            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?

            • 3. Re: remove quotation mark
              Alan Koenig_920 Level 1

              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

              • 4. Re: remove quotation mark
                Carl Von Stetten Adobe Community Professional & MVP

                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.