16 Replies Latest reply on Oct 15, 2007 10:45 AM by cf_dev2

    try/catch

    Level 7
      Where would I put a cftry/cfcatch in this following code? I am trying to
      create a query out of this file and having problems with some of the entries
      having bad data and I would like to step over those records and continue.

      <cfhttp
      url=" http://www.bpoprosonline.com/assets/property/idxvl101107.txt"
      method="GET" name="Vacant1" delimiter="|" textqualifier=""
      firstrowasheaders="yes" />

      <cfquery name="Vacant" dbtype="query">
      SELECT * FROM Vacant1
      </cfquery>


        • 1. Re: try/catch
          Dan Bracuk Level 5
          You would have to change your approach. Instead of cfhttp, use cffile and Cold Fusion Query functions. Then loop through the file, check each line with either if/else or try/catch and do what you have to do.
          • 2. Re: try/catch
            Level 7
            The issue is that the file is a pipe-delimited text file. Can cffile read
            that?

            Sample:
            AgentId|OfficeCode|Company|FirstName|LastName|MiddleName
            294494|328296|MI REAL ESTATE TODAY.COM|ANITA|SATTERFIELD|
            330337|25318301|CENTURY 21 TODAY-F H|JILL|GREENLEE|
            122408|25318301|CENTURY 21 TODAY-F H|DIANA|BLAIR|L
            294355|25318301|CENTURY 21 TODAY-F H|NORMA|SHEENA|
            313240|25318301|CENTURY 21 TODAY-F H|AMELIA DEAN|HAMM|
            294367|25318301|CENTURY 21 TODAY-F H|ELIZABETH|BENSCH|
            349402|25318301|CENTURY 21 TODAY-F H|MONICA|DAVIS|
            300492|25318301|CENTURY 21 TODAY-F H|GAIL|CLARK|H
            332314|25318301|CENTURY 21 TODAY-F H|SALAM|RABBAN|


            • 3. Re: try/catch
              cf_dev2 Level 1
              > The issue is that the file is a pipe-delimited text file. Can cffile read that?

              CFFILE can read any text file. What it won't do is convert the contents into a query for you. You would have to do that yourself, by looping through each line of the file and splitting the row values on the pipe-delimiter.
              • 4. Re: try/catch
                Level 7
                Is there a benefit to the cffile approach over the cfhttp? I changed my
                approach back to the original way. I download the file from RealComp to my
                server, I use cfhttp to turn the file into a query and then loop its
                contents into a database and then delete the file. I am only reading it
                once.

                What I need to do is if there is a bad line of file, to skip over it and
                move to the next. It is not critical to have all the files entered into the
                database. Its only filler data for searching properties.

                Is there a way to use try and catch to look at each line in the query and if
                its good, insert it, if not, skip over it? I may log it for reference in the
                future, but that is easy enough to do in the cfcatch block.



                • 5. Re: try/catch
                  Dan Bracuk Level 5
                  quote:

                  Originally posted by: Newsgroup User
                  Is there a benefit to the cffile approach over the cfhttp? I changed my
                  approach back to the original way. I download the file from RealComp to my
                  server, I use cfhttp to turn the file into a query and then loop its
                  contents into a database and then delete the file. I am only reading it
                  once.

                  What I need to do is if there is a bad line of file, to skip over it and
                  move to the next. It is not critical to have all the files entered into the
                  database. Its only filler data for searching properties.

                  Is there a way to use try and catch to look at each line in the query and if
                  its good, insert it, if not, skip over it? I may log it for reference in the
                  future, but that is easy enough to do in the cfcatch block.



                  Cffile enables you to look at each line separately. I don't think cfhttp does, but I've not actually tried it,
                  • 6. Re: try/catch
                    Level 7
                    Then the next question I need to ask is how do you step through the
                    delimiter and insert each record? Anyone have an example of that? Also where
                    do you put the try/catch? Do you surround the cffile and the insert cfquery
                    or just the cfquery? Anyone have any good example URLs for such a feat?

                    Thanks a ton for everyones help so far.


                    • 7. Re: try/catch
                      Level 7
                      Would this be something close?

                      <cfset filepath = "C:\CFusionMX\wwwroot\SalesCustomer.txt">

                      <cffile action="read" file="#filepath#" variable="fileData">

                      <cfloop index="record" list="#fileData#" delimiters="#chr(10)#,#chr(9)#">

                      <cfquery name="Q_insert" datasource="#dsn#">
                      insert into Clients (ClientName) values ('#record#')
                      </cfquery>

                      </cfloop>


                      • 8. Re: try/catch
                        cf_dev2 Level 1
                        > delimiters="#chr(10)#,#chr(9)#">
                        IIRC new line is chr(10)&chr(13) on windows, not chr(10)&chr(9). Though cfloop may treat "delimiters" as a list anyway.

                        > values ('#record#')

                        That would insert all values in the row into a single column. If you want to separate them, you need to further split the values on the pipe "|".
                        • 9. Re: try/catch
                          Level 7
                          Any ideas on how that can be done. A sample URL would be awesome.


                          • 10. Re: try/catch
                            cf_dev2 Level 1
                            I don't have a URL handy. But google parsing a CSV file. Its the exact same concept, except you substitute a pipe "|" for the comma "," delimiter.
                            • 11. Re: try/catch
                              Stressed_Simon Level 1
                              I have attached code that works for parsing the pipe delimited file. Notice I have used the Java String Object instead of looping through as a list. This is because on a large file this approach will have unbelievable speed increases over looping through as a list.
                              • 12. Re: try/catch
                                cf_dev2 Level 1
                                quote:

                                Originally posted by: Stressed_Simon
                                Notice I have used the Java String Object instead of looping through as a list. This is because on a large file this approach will have unbelievable speed increases over looping through as a list.



                                I'm not sure what you mean. The variable is already a java.lang.String. Perhaps you're talking about the performance of arrays versus of lists?
                                • 13. Re: try/catch
                                  Stressed_Simon Level 1
                                  Essentially ever since ColdFusion moved from C++ to Java with MX then arrays have been much faster than lists. The most performant way to convert a list to an array is using the split() method of java.lang.String. You would think that the overhead of converting a list to an array to then loop through it would be more than just looping through the list in the first place but this is not the case. I had a CSV import that I converted from using the standard loop though each line approach to using the split() method and it reduced the execution time from 45 seconds to less than 3. That is a massive performance gain in anyone's book
                                  • 14. Re: try/catch
                                    cf_dev2 Level 1
                                    No, what I meant was the variables are already java.lang.String's. Why create another String?
                                    createObject("java", "java.lang.String").init(trim(pipeFile))

                                    I haven't done any performance tests on the difference between CF's listToArray() and java.lang.String.split(). Is there a difference? I'm wondering if it simply uses java.lang.String().split() under the covers.
                                    • 15. Re: try/catch
                                      Stressed_Simon Level 1
                                      I know it is already a string, but I just prefer to do it that way as coldfusion is not strongly typed so it makes more sense. Also, for demonstration purposes it shows were the method comes from.

                                      listToArray() definitely does not use split() under the covers as it is not even close to as fast. I never use it any more. I got told by a guy from Macromedia that it was quicker to use cfloop and arrayAppend() to create an array from a list than using listToArray() I haven't tested it but it would not surprise me.
                                      • 16. Re: try/catch
                                        cf_dev2 Level 1
                                        quote:


                                        listToArray() definitely does not use split() under the covers as it is not even close to as fast. I never use it any more. I got told by a guy from Macromedia that it was quicker to use cfloop and arrayAppend() to create an array from a list than using listToArray() I haven't tested it but it would not surprise me.



                                        Do you know what actually is used for listToArray() and did this employee give a reason why? I have my own suspicions but that's all they all they are, I was kind of looking for something more concrete. I don't know the answer myself - that's why I'm asking around :)