try/catch

[Newsgroup_User]

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>

[Dan_Bracuk]

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.

[Newsgroup_User]

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|

[cf_dev2]

> 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.

[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.

[Dan_Bracuk]

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,

[Newsgroup_User]

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.

[Newsgroup_User]

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>

[cf_dev2]

> 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 "|".

[Newsgroup_User]

Any ideas on how that can be done. A sample URL would be awesome.

[Stressed_Simon]

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.

[cf_dev2]

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?

[Stressed_Simon]

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

[cf_dev2]

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.

[Stressed_Simon]

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.

[cf_dev2]

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 🙂

[cf_dev2]

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.