17 Replies Latest reply on Dec 30, 2010 9:10 AM by Adam Cameron.

    Insert Excel information to Access Table, cf error

    cfsetNewbie Level 1

      I am trying to create a file upload that will allow me to use a .csv file, to insert the data into an access database. I have the code written, but right now, it's giving me an error and I can't figure out what I'm doing wrong, this is my first time doing this, so any and all help would be greatly appreciated.

       

      This is my code to read the .csv file.:


      <cftry>
      <cffile action="DELETE" file="#FORM.attachment_1#"/>
      <cfcatch>
      <!--- File delete error. --->
      </cfcatch>
      </cftry>
      <cfelse>
      <!--- no errors with the file upload so lets upload it--->

       

      <cffile action="upload"
                       filefield="attachment_1"
                       result="myResult"
                       accept = ""
                       destination="f:\websites\211562Fe3\uploads\"
                       nameconflict="Makeunique">
                      
      <cfset svrFile = "#myResult.ServerDirectory#"&"\"&"#myResult.ServerFile#"/>

       

       

       

      <!--- get and read the CSV-TXT file --->
      <cffile action="read" file="#svrFile#" variable="csvfile">

       

      <!--- loop through the CSV-TXT file on line breaks and insert into database --->
      <cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
          <cfquery name="importcsv" datasource="#APPLICATION.dataSource#">
               INSERT INTO employees (siteID,empstatus,empfirstname,empmiddlename,emplastname,empnickname,emplocation,empgende r,empdob,empdoh,empee)
               VALUES
                        ('#listgetAt('#index#',1, ',')#',
                         '#listgetAt('#index#',2, ',')#',
                         '#listgetAt('#index#',3, ',')#',
                         '#listgetAt('#index#',4, ',')#',
                         '#listgetAt('#index#',5, ',')#',
                         '#listgetAt('#index#',6, ',')#',
                         '#listgetAt('#index#',7, ',')#',
                         '#listgetAt('#index#',8, ',')#',
                         '#listgetAt('#index#',9, ',')#',
                         '#listgetAt('#index#',10, ',')#',
                         '#listgetAt('#index#',11, ',')#'
                        )
         </cfquery>
      </cfloop>

       

      <cffile action="DELETE" file="#svrFile#"/>

       


      <!--- use a simple database query to check the results of the import - dumping query to screen --->
      <cfquery name="rscsvdemo" datasource="#APPLICATION.dataSource#">
               SELECT * FROM csvdemo
      </cfquery>
      <cfdump var="#employees#">
      </cfif>

       

      The error is in the insert query, I have all my columns named the same ad the database table and in the same order, I also formatted each cell in the .csv to be the same as the database.


      This is the error:

      Error Executing Database Query.

      [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC  Microsoft Access Driver] Data type mismatch in criteria expression.

       

      The error occurred in  C:\websites\211562fe3\partners\cfm\csvUploadAction.cfm: line 60

       

      58 :                    '#listgetAt('#index#',9, ',')#',

      59 :                    '#listgetAt('#index#',10, ',')#',

      60 :                    '#listgetAt('#index#',11, ',')#' 

      61 :                   )

      62 :    </cfquery>

       

       

      Thank you

        • 1. Re: Insert Excel information to Access Table, cf error
          Adam Cameron. Level 5

          Access is pretty forgiving about such things, but by putting quotes around all your values, you're saying they're all strings. I suspect the ID is numeric, and there's a coupla dates in there too. These should be passed as the correct data type.

           

          Also, you should be using  tags when passing dynamic values to the DB, rather than hard-coding them in your SQL string.

           

          And - more trivially but definitely a "best practice" thing - learn when & when not to use pound signs. More to the point, you are quoting a lot of your variables for no good reason, which is then forcing you to resolve them with the pound-signs. Only use quotes and pound-signs when it's actually necessary.

           

          --

          Adam

          • 2. Re: Insert Excel information to Access Table, cf error
            cfsetNewbie Level 1

            how owuld I make tags? cfparam tags? Can you write one? and then I just make it like a normal insert query with cfqueryparam from the "tags" correct?

             

            Also, besides the query, where am I quoting and pounding too much?

             

            Thank you for your help, like I said, this is the first time I am trying to make excel update access.

            • 3. Re: Insert Excel information to Access Table, cf error
              Adam Cameron. Level 5

              Argh, sorry: the forums software munged my reply. Yes, I meant (and typed...) CFQUERYPARAM tags.

               

              As for the quotes / pound-signs, every time you have this:

               

              "#someVar#"

               

              in a CF expression, you can simple do this:

               

              someVar

               

              Eg:

               

              someOtherVar = myVar;

               

              rather than:

               

              someOtherVar = "#myVar#";

               

              --

              Adam

              • 4. Re: Insert Excel information to Access Table, cf error
                cfsetNewbie Level 1

                I changed my code a little.. now I get an error about converting a date.. so I must be on the right track.... Can you help me straiten this out?

                 

                This is my "new" code:

                <cftry>
                <cffile action="DELETE" file="#FORM.attachment_1#"/>
                <cfcatch>
                <!--- File delete error. --->
                </cfcatch>
                </cftry>
                <cfelse>
                <!--- no errors with the file upload so lets upload it--->

                 

                <cffile action="upload"
                                 filefield="attachment_1"
                                 result="myResult"
                                 accept = ""
                                 destination="c:\websites\211562Fe3\uploads\"
                                 nameconflict="Makeunique">
                                
                <cfset svrFile = "#myResult.ServerDirectory#"&"\"&"#myResult.ServerFile#"/>

                 

                 

                 

                <!--- get and read the CSV-TXT file --->
                <cffile action="read" file="#svrFile#" variable="csvfile">

                 

                <!--- loop through the CSV-TXT file on line breaks and insert into database --->
                <cfloop index="i" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
                    <cfquery name="importcsv" datasource="#APPLICATION.dataSource#">
                         INSERT INTO employees (siteID,empstatus,empfirstname,empmiddlename,emplastname,empnickname,emplocation,empgende r,empdob,empdoh,empee)
                         VALUES
                                  (<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,1))#">,
                                    <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,2)#">,
                                    <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,3)#">,
                                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,4))#">,
                                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,5)#">,
                                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,6)#">,
                                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,7)#">,
                                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,8)#">,
                                 <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#listgetAt(i,9)#">,
                                 <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#listgetAt(i,9)#">,
                                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,9)#">
                                  )
                   </cfquery>
                </cfloop>

                 

                <cffile action="DELETE" file="#svrFile#"/>

                 


                <!--- use a simple database query to check the results of the import - dumping query to screen --->
                <cfquery name="rscsvdemo" datasource="#APPLICATION.dataSource#">
                         SELECT * FROM employees
                </cfquery>
                <cfdump var="#employees#">
                </cfif>

                 

                 

                I'm not sure it's totally correct.. but the error is different!

                 

                This is the error:

                 

                The cause of this output exception was that:  coldfusion.runtime.Cast$DateStringConversionException: The value empdob  cannot be converted to a date..

                 

                The error occurred inf :\websites\211562fe3\partners\cfm\csvUploadAction.cfm: line 58

                 

                56 :                  <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar"  VALUE="#listgetAt(i,7)#">,

                57 :                  <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar"  VALUE="#listgetAt(i,8)#">,

                58 :                  <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE"  VALUE="#listgetAt(i,9)#">,

                59 :                  <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE"  VALUE="#listgetAt(i,9)#">,

                60 :                  <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar"  VALUE="#listgetAt(i,9)#">

                 

                Also I can make my .csv file available for you if you need it. Also, the date is being inseted in this format: 3/3/1952

                • 5. Re: Insert Excel information to Access Table, cf error
                  Dan Bracuk Level 5

                  When the user gives you a string that is supposed to represent a date, parsedatetime() will convert it to a data object.  Date objects are better than string in many situations, such as variables in queries.

                   

                  Just out of curiousity, if you open your csv file with notepad, is each field surrounded by double quotes?

                  • 6. Re: Insert Excel information to Access Table, cf error
                    cfsetNewbie Level 1

                    ok, so how do I write a cfset to convert the excel date to one the database will accept.

                     

                    Also, no, each field is not surrounded by quotes... How do I fix that?

                     

                    This is what I see in wordpad:
                    siteID,empstatus,empfirstname,empmiddlename,emplastname,empnickname,emplocation,empgender ,empdob,empdoh,empee
                    12,active,Douglas,L,Berg,,"Marshall, MN",Male,3/3/1952,,

                     

                    Thanks again

                    • 7. Re: Insert Excel information to Access Table, cf error
                      Dan Bracuk Level 5

                      Regarding "so how do I write a cfset to convert the excel date to one the database will accept"

                      I gave you the name of the function.  If you need help with it, google "coldfusion name of function" and the reference material you need will come up.

                       

                      Regarding the rest of your post, the reason for the error is that the person does not have a nickname.  This is creating empty list elements which Coldfusion tends to ignore.  You are going to have to deal with this eventually, so now is as good a time as any.

                       

                      Your task might be simpler if you use cfhttp to read your file instead of cffile.  The documentation will tell you how you can get a query object which should give you blank fields instead of empty list elements.

                       

                      Even if you stick with cffile, you still have to decide what to do about missing data and then write the applicable code.

                      • 8. Re: Insert Excel information to Access Table, cf error
                        cfsetNewbie Level 1

                        I'm researching the functions you are pointing me too.. now I'm trying to write the code, one step at a time...

                         

                        Right now, I'm uploading the file, and using cfhttp to read it, then dump those results.. I haven't gotten to inserting the info yet..

                         

                        Right now, I'm getting an error with this code:

                         

                        <cftry>
                        <cffile action="DELETE" file="#FORM.attachment_1#"/>
                        <cfcatch>
                        <!--- File delete error. --->
                        </cfcatch>
                        </cftry>
                        <cfelse>
                        <!--- no errors with the file upload so lets upload it--->.

                         

                        <cffile action="upload"
                                         filefield="attachment_1"
                                         result="myResult"
                                         accept = ""
                                         destination="c:\websites\211562Fe3\uploads\"
                                         nameconflict="Makeunique">
                                        
                        <cfset svrFile = "#myResult.ServerDirectory#"&"\"&"#myResult.ServerFile#"/>


                        <cfset dataUrl = ("http://" &cgi.server_name & getDirectoryFromPath( cgi.script_name ) & "svrFile") />


                        <cfhttp name="employeeCSV" method="get" url="#dataUrl#" />

                         

                        <cfdump var="#employeeCSV#" label="CSV Query" />

                         

                        At this point, it's simple, upload it, read it, but it's giving me this error:

                         

                        The column name is invalid.
                        Column names must be valid variable names. They must start with a letter and can only include letters, numbers, and underscores.

                        The error occurred in C:\websites\211562fe3\partners\cfm\csvUploadAction.cfm: line 42

                         

                        40 :
                        41 : <cfset dataUrl = ("http://" &cgi.server_name & getDirectoryFromPath( cgi.script_name ) & "svrFile") />
                        42 : <cfhttp name="employeeCSV" method="get" url="#dataUrl#" />
                        43 :
                        44 : <cfdump var="#employeeCSV#" label="CSV Query" />

                         

                        Now all this aside, once I get this working, what do I do to grab the info from the .csv on the query insert: use something like this?

                         

                        <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(employeeCSV.siteID)#">,

                        and all the other columns that are labled in the .csv doc go after this?

                         

                        I'm finding a lot of docs on this, but not exactly what I need.. so I'm trying to figure out how to make this work for what I need, and your idea seems to be better.. if i can get over these little issues. Thank you.

                        • 9. Re: Insert Excel information to Access Table, cf error
                          Dan Bracuk Level 5

                          At first glance it appears that you are attempting to open a different file than the one you uploaded.  It's hard to tell because you are using so many functions.

                           

                          You might want to create a mapping for your uploads directory, something like /uploads.  Then your code becomes this

                           

                          <cfhttp method="get" url="/uploads/#Cffile.clientFile#" name="NameOfYourQuery"></cfhttp>

                          • 10. Re: Insert Excel information to Access Table, cf error
                            cfsetNewbie Level 1

                            I can clean this up in a bit, it is reading the file, now it's telling me there isn't the proper amount of columns and to verify it...

                             

                            This is what I've done so far:

                             

                            <cftry>
                            <cffile action="DELETE" file="#FORM.attachment_1#"/>
                            <cfcatch>
                            <!--- File delete error. --->
                            </cfcatch>
                            </cftry>
                            <cfelse>
                            <!--- no errors with the file upload so lets upload it--->

                             

                            <cffile action="upload"
                                             destination="c:\websites\211562Fe3\uploads\"
                                             filefield="#FORM.attachment_1#"
                                             nameconflict="overwrite"
                                             result="myResult">
                                            
                            <cfset svrFile = "#myResult.ServerDirectory#"&"\"&"#myResult.ServerFile#"/>
                            <cfset dataUrl = ("http://" &cgi.server_name & getDirectoryFromPath( cgi.script_name ) & "svrFile") />
                            <cfhttp url="#dataUrl#" name="employee" columns="siteID,empstatus,empfirstname,empmiddlename,emplastname,empnickname,emplocation, empgender,empdob,empdoh,empee" delimiter="#chr(32)#" resolveurl="no" />

                             

                            <cfdump var="#employee#" label="CSV Query" />

                             

                            This is the error now:


                            Incorrect number of columns in row.
                            Verify the number of columns specified in the columns attribute and in the target file

                            The error occurred in C:\websites\211562fe3\partners\cfm\csvUploadAction.cfm: line 40

                             

                            38 : <cfset svrFile = "#myResult.ServerDirectory#"&"\"&"#myResult.ServerFile#"/>
                            39 : <cfset dataUrl = ("http://" &cgi.server_name & getDirectoryFromPath( cgi.script_name ) & "svrFile") />
                            40 : <cfhttp url="#dataUrl#" name="employee" columns="siteID,empstatus,empfirstname,empmiddlename,emplastname,empnickname,emplocation, empgender,empdob,empdoh,empee" delimiter="#chr(32)#" resolveurl="no" />
                            41 :
                            42 : <cfdump var="#employee#" label="CSV Query" />

                             


                            Ok.. now what! The columns named in the columns portion of the tag are the columns in the .csv file.

                            • 11. Re: Insert Excel information to Access Table, cf error
                              Dan Bracuk Level 5

                              The error message was pretty explicit. Maybe cfhttp doesn't like empty cells either.

                              • 12. Re: Insert Excel information to Access Table, cf error
                                cfsetNewbie Level 1

                                I put some text in all fields for this, to make sure it first accepts all fiels filled, and it's giving this error.

                                 

                                How about a simple question

                                 

                                for using the parsdate would i use it like this?<CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#ParseDateTime(listgetAt(i,9))#">

                                 

                                I don't need time though. Can I just use parsedate?

                                • 13. Re: Insert Excel information to Access Table, cf error
                                  Dan Bracuk Level 5

                                  Regarding using parsedate(), try it and see what happens.

                                   

                                  You didn't say what error message you were getting.  Is it still the one for the wrong number of columns?

                                   

                                  I was curious about using cfhttp when there were empty cells.  I created a file that looks like this:

                                   

                                  abcd
                                  1234
                                  4578

                                   

                                  Then I ran this code:

                                  <cfhttp method="get" url="/work/abc.csv" name="x">
                                  </cfhttp>

                                  <cfdump var="#x#" metainfo=no>

                                   

                                  and got my query as expected.  Then I took out a couple of numbers, and ran the same code.  I got my query with empty fields.

                                  • 14. Re: Insert Excel information to Access Table, cf error
                                    cfsetNewbie Level 1

                                    ok, then my .csv file is formatted improperly. I was using excel converted to a .csv what am I doing wrong, because it isn't coming out like you posted. a1, b1 and so on is the titles of each column, but they are not separated like you have it... why?

                                    • 15. Re: Insert Excel information to Access Table, cf error
                                      Adam Cameron. Level 5

                                      That said, if the "date" value comes from user input or any system that could be "unreliable", make sure to check the string •can• be parsed as a date first, and try/catch the parseDateTime() call.

                                       

                                      --

                                      Adam

                                      • 16. Re: Insert Excel information to Access Table, cf error
                                        Dan Bracuk Level 5

                                        All I can tell you is what I did.  I opened excel, typed letters into the cells A1 to D1, numbers into A2 to D3, and saved it as csv.  In Notepad, it now looks like this:

                                         

                                        a,b,c,d
                                        1,2,3,4
                                        4,,7,8

                                         

                                        What you saw in my earlier post was pasting from excel.  This rich textarea converted it to an html table.

                                        • 17. Re: Insert Excel information to Access Table, cf error
                                          Adam Cameron. Level 5

                                          OK: a tip? When working with text files, don't open then in an Office application as the app will do all sorts of things to try to present the file all "nice" for the user. However what Excel - in this case - displays is not necessarily what's in the file.  And it's what's in the file that matters.

                                           

                                          By all means •create• the file with Excel, but when troubleshooting problems, look at the actual file contents, not what Excel suggests the file might look like.

                                           

                                          Use Notepad or something. Use whatever you write your CFML code in! That's probably the most obvious option.

                                           

                                          --

                                          Adam