[cfaussie] Re: Large CSV's and server timeouts
Grant, The most efficient way that I can think about of the top of my head, is to write a .Net / C++ / Java application that will run and buffer read the file. I came across this once before and the only solution is to write a parser in a language that is able to file seek through the file. I am not 100% sure but I also believe there are tools out there that could maybe use the csv as a datasource and migrate that way, but I am not sure if they will file seek or hold the entire file open. HTH It's been 10 years since I had to write a program to do just that. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. http://www.aegeon.com.au www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of grant Sent: Thursday, 14 June 2007 9:54 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Large CSV's and server timeouts Hi y'All Here's the scenario: we have a csv file that contains 1.5 million rows and is provided by an external vendor. we need to either (a) parse the csv and insert into a DB, or (b) do lookups directly on the csv. We do not and cannot have SQLloader. The DB is ORA10g. The servers are old and under fairly high load all the time. We've tried splitting the csv into smaller chunks and inserting into the DB and also trying a SQL link straight into the CSV. Anything we try time's out and basically brings down the server. Who can shed light on the most efficient way to solve this problem? Thanks Grant --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Perhaps you could import it into MS Access (by hand) then into Oracle. I am a MS SQL user myself so I do not know what oracle tools there are available. grant [EMAIL PROTECTED] 14/06/2007 9:53 am Hi y'All Here's the scenario: we have a csv file that contains 1.5 million rows and is provided by an external vendor. we need to either (a) parse the csv and insert into a DB, or (b) do lookups directly on the csv. We do not and cannot have SQLloader. The DB is ORA10g. The servers are old and under fairly high load all the time. We've tried splitting the csv into smaller chunks and inserting into the DB and also trying a SQL link straight into the CSV. Anything we try time's out and basically brings down the server. Who can shed light on the most efficient way to solve this problem? Thanks Grant --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Thanks Andrew. That's actually the path we're heading down (a custom java app); I thought I'd post here to ensure we're not re-inventing the wheel... Thanks! On 14/06/07, Scott Thornton [EMAIL PROTECTED] wrote: Perhaps you could import it into MS Access (by hand) then into Oracle. I am a MS SQL user myself so I do not know what oracle tools there are available. grant [EMAIL PROTECTED] 14/06/2007 9:53 am Hi y'All Here's the scenario: we have a csv file that contains 1.5 million rows and is provided by an external vendor. we need to either (a) parse the csv and insert into a DB, or (b) do lookups directly on the csv. We do not and cannot have SQLloader. The DB is ORA10g. The servers are old and under fairly high load all the time. We've tried splitting the csv into smaller chunks and inserting into the DB and also trying a SQL link straight into the CSV. Anything we try time's out and basically brings down the server. Who can shed light on the most efficient way to solve this problem? Thanks Grant --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Hi Grant, we have used http://www.emerle.net/programming/display.cfm/t/cfx_text2query with success Cheers Rob _ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of grant Sent: Thursday, 14 June 2007 9:54 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Large CSV's and server timeouts Hi y'All Here's the scenario: we have a csv file that contains 1.5 million rows and is provided by an external vendor. we need to either (a) parse the csv and insert into a DB, or (b) do lookups directly on the csv. We do not and cannot have SQLloader. The DB is ORA10g. The servers are old and under fairly high load all the time. We've tried splitting the csv into smaller chunks and inserting into the DB and also trying a SQL link straight into the CSV. Anything we try time's out and basically brings down the server. Who can shed light on the most efficient way to solve this problem? Thanks Grant --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
On Thu, Jun 14, 2007 at 10:04:13AM +1000, Andrew Scott wrote: Grant, The most efficient way that I can think about of the top of my head, is to write a .Net / C++ / Java application that will run and buffer read the file. I came across this once before and the only solution is to write a parser in a language that is able to file seek through the file. Take a look at: http://www.informationsavvy.com/coldfusion/ FileReader.cfc - This ColdFusion Java CFC File Reader can be used to read files incrementally However, this blog entry: http://tinyurl.com/2vrsqw claims CFHTTP is faster than Java Buffered in their tests. Try FileReader.cfc with your data and let us know how it turns out. Paul Haddon Technical Services Manager Formstar Print Technologies --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Grant, That's why I thought I would add that there might be an application already out there, I can't be 100% sure but I have seen apps that have been able to manipulate CSV files (IIS Server log files in particular) but whether they will do what you are looking at doing then that I can't answer. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of grant Sent: Thursday, 14 June 2007 10:11 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Thanks Andrew. That's actually the path we're heading down (a custom java app); I thought I'd post here to ensure we're not re-inventing the wheel... Thanks! On 14/06/07, Scott Thornton [EMAIL PROTECTED] wrote: Perhaps you could import it into MS Access (by hand) then into Oracle. I am a MS SQL user myself so I do not know what oracle tools there are available. grant [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 14/06/2007 9:53 am Hi y'All Here's the scenario: we have a csv file that contains 1.5 million rows and is provided by an external vendor. we need to either (a) parse the csv and insert into a DB, or (b) do lookups directly on the csv. We do not and cannot have SQLloader. The DB is ORA10g. The servers are old and under fairly high load all the time. We've tried splitting the csv into smaller chunks and inserting into the DB and also trying a SQL link straight into the CSV. Anything we try time's out and basically brings down the server. Who can shed light on the most efficient way to solve this problem? Thanks Grant br --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Rob, I doubt CF will be able to handle a query of this magnitude that grant is looking at, but to split the file up into 1mg chunks could be viable. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Rob Wilson Sent: Thursday, 14 June 2007 10:16 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Hi Grant, we have used http://www.emerle.net/programming/display.cfm/t/cfx_text2query with success Cheers Rob _ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of grant Sent: Thursday, 14 June 2007 9:54 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Large CSV's and server timeouts Hi y'All Here's the scenario: we have a csv file that contains 1.5 million rows and is provided by an external vendor. we need to either (a) parse the csv and insert into a DB, or (b) do lookups directly on the csv. We do not and cannot have SQLloader. The DB is ORA10g. The servers are old and under fairly high load all the time. We've tried splitting the csv into smaller chunks and inserting into the DB and also trying a SQL link straight into the CSV. Anything we try time's out and basically brings down the server. Who can shed light on the most efficient way to solve this problem? Thanks Grant BR --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
can you use cfsetting and increase the timeout massively for that request to stop the timeout? If you are reading in a huge file, cffile is not good, as it can will read in the entire file to memory (I understand this has been imporved in CF8) The is an example of using Java to read in a file line in CF7 here: [path to cfide]/cfide/gettingstarted/experience/index_content.cfm then Code Snippets by Feature and Tasks Under Java Integration so, with those two, could you read in the file and put it in your DB? Rob Wilson wrote: Hi Grant, we have used http://www.emerle.net/programming/display.cfm/t/cfx_text2query with success Cheers Rob _ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of grant Sent: Thursday, 14 June 2007 9:54 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Large CSV's and server timeouts Hi y'All Here's the scenario: we have a csv file that contains 1.5 million rows and is provided by an external vendor. we need to either (a) parse the csv and insert into a DB, or (b) do lookups directly on the csv. We do not and cannot have SQLloader. The DB is ORA10g. The servers are old and under fairly high load all the time. We've tried splitting the csv into smaller chunks and inserting into the DB and also trying a SQL link straight into the CSV. Anything we try time's out and basically brings down the server. Who can shed light on the most efficient way to solve this problem? Thanks Grant --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Ok, First of all... If anyone has actually done what Grant is trying to do, then PLEASE look at it this way. Coldfusion is an interpreted language, when I did this on only a 5meg file all those years ago. Coldfusion would always timeout, no matter how long I made the timeout request for. Not only that, but it began chewing up more memory than it really needed to. (This was V5.0 coldfusion, and I know that it will be a bit faster on MX 6.0+) however speed is an issue as well. I ended up writing a C++ solution all those years ago, and the application ran in 20 minutes compared to 12 hours later and timing out on CF. There is a performance issue to consider here. And I am sorry but this sort of processing is not what coldfusion is about, this is where we as developers really need to say NO to CF in this case. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, 14 June 2007 10:21 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts On Thu, Jun 14, 2007 at 10:04:13AM +1000, Andrew Scott wrote: Grant, The most efficient way that I can think about of the top of my head, is to write a .Net / C++ / Java application that will run and buffer read the file. I came across this once before and the only solution is to write a parser in a language that is able to file seek through the file. Take a look at: http://www.informationsavvy.com/coldfusion/ FileReader.cfc - This ColdFusion Java CFC File Reader can be used to read files incrementally However, this blog entry: http://tinyurl.com/2vrsqw claims CFHTTP is faster than Java Buffered in their tests. Try FileReader.cfc with your data and let us know how it turns out. Paul Haddon Technical Services Manager Formstar Print Technologies --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Not sure if this is an option. But CF8 has new file functions that do incremental reads also. Regards Dale Fraser http://dalefraser.blogspot.com http://dalefraser.blogspot.com From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, 14 June 2007 10:27 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Grant, That's why I thought I would add that there might be an application already out there, I can't be 100% sure but I have seen apps that have been able to manipulate CSV files (IIS Server log files in particular) but whether they will do what you are looking at doing then that I can't answer. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of grant Sent: Thursday, 14 June 2007 10:11 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Thanks Andrew. That's actually the path we're heading down (a custom java app); I thought I'd post here to ensure we're not re-inventing the wheel... Thanks! On 14/06/07, Scott Thornton [EMAIL PROTECTED] wrote: Perhaps you could import it into MS Access (by hand) then into Oracle. I am a MS SQL user myself so I do not know what oracle tools there are available. grant [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 14/06/2007 9:53 am Hi y'All Here's the scenario: we have a csv file that contains 1.5 million rows and is provided by an external vendor. we need to either (a) parse the csv and insert into a DB, or (b) do lookups directly on the csv. We do not and cannot have SQLloader. The DB is ORA10g. The servers are old and under fairly high load all the time. We've tried splitting the csv into smaller chunks and inserting into the DB and also trying a SQL link straight into the CSV. Anything we try time's out and basically brings down the server. Who can shed light on the most efficient way to solve this problem? Thanks Grant br --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Actually wouldn't say they are new functions... Unless I am missing something? But yes you can now loop over a file using cfloop, but the problem is that has to be in memory. And cffile can now process files line by line. However, all things aside when you deal with a large file CF is very slow when processing files. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Dale Fraser Sent: Thursday, 14 June 2007 10:36 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Not sure if this is an option. But CF8 has new file functions that do incremental reads also. Regards Dale Fraser http://dalefraser.blogspot.com From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, 14 June 2007 10:27 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Grant, That's why I thought I would add that there might be an application already out there, I can't be 100% sure but I have seen apps that have been able to manipulate CSV files (IIS Server log files in particular) but whether they will do what you are looking at doing then that I can't answer. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of grant Sent: Thursday, 14 June 2007 10:11 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Thanks Andrew. That's actually the path we're heading down (a custom java app); I thought I'd post here to ensure we're not re-inventing the wheel... Thanks! On 14/06/07, Scott Thornton [EMAIL PROTECTED] wrote: Perhaps you could import it into MS Access (by hand) then into Oracle. I am a MS SQL user myself so I do not know what oracle tools there are available. grant [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 14/06/2007 9:53 am Hi y'All Here's the scenario: we have a csv file that contains 1.5 million rows and is provided by an external vendor. we need to either (a) parse the csv and insert into a DB, or (b) do lookups directly on the csv. We do not and cannot have SQLloader. The DB is ORA10g. The servers are old and under fairly high load all the time. We've tried splitting the csv into smaller chunks and inserting into the DB and also trying a SQL link straight into the CSV. Anything we try time's out and basically brings down the server. Who can shed light on the most efficient way to solve this problem? Thanks Grant br --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Hi Andrew, some of the smaller files we handle are in the order of 5 meg and larger files have been in the order of 12 meg ...we have had major issues with servers timing out on large files - CSV and the dreadful XLS files - depending on who created the files even became a factor i.e errors within the file (at least the Fed. Govt sent us XML files - in some cases 3 XML files to run in sequence each XML file in the order of 5-8 meg each without the server timing out) ... in our case a manual import wasn't a option - a daily or in some case twice daily import of data that updated several tables Rob Smartnumbers Auction site 2004 - 2006 Auctiontrader - 1999 - current Directwholesale - 2001 - current _ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, 14 June 2007 10:29 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Rob, I doubt CF will be able to handle a query of this magnitude that grant is looking at, but to split the file up into 1mg chunks could be viable. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Rob Wilson Sent: Thursday, 14 June 2007 10:16 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Hi Grant, we have used http://www.emerle.net/programming/display.cfm/t/cfx_text2query with success Cheers Rob _ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of grant Sent: Thursday, 14 June 2007 9:54 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Large CSV's and server timeouts Hi y'All Here's the scenario: we have a csv file that contains 1.5 million rows and is provided by an external vendor. we need to either (a) parse the csv and insert into a DB, or (b) do lookups directly on the csv. We do not and cannot have SQLloader. The DB is ORA10g. The servers are old and under fairly high load all the time. We've tried splitting the csv into smaller chunks and inserting into the DB and also trying a SQL link straight into the CSV. Anything we try time's out and basically brings down the server. Who can shed light on the most efficient way to solve this problem? Thanks Grant BR __ NOD32 2328 (20070613) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Could you set the file up as a TEXT datasource? Similar to if you were setting up a XLS file? [Windows] Administrator Tools ODBC Datasources Add it in there to use the TEST driver and maybe you can then set up a CF datasource to query they file directly. Not sure on performance but maybe that can work? Steve _ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, 14 June 2007 10:40 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Actually wouldn't say they are new functions... Unless I am missing something? But yes you can now loop over a file using cfloop, but the problem is that has to be in memory. And cffile can now process files line by line. However, all things aside when you deal with a large file CF is very slow when processing files. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Dale Fraser Sent: Thursday, 14 June 2007 10:36 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Not sure if this is an option. But CF8 has new file functions that do incremental reads also. Regards Dale Fraser http://dalefraser.blogspot.com From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, 14 June 2007 10:27 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Grant, That's why I thought I would add that there might be an application already out there, I can't be 100% sure but I have seen apps that have been able to manipulate CSV files (IIS Server log files in particular) but whether they will do what you are looking at doing then that I can't answer. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of grant Sent: Thursday, 14 June 2007 10:11 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Thanks Andrew. That's actually the path we're heading down (a custom java app); I thought I'd post here to ensure we're not re-inventing the wheel... Thanks! On 14/06/07, Scott Thornton [EMAIL PROTECTED] wrote: Perhaps you could import it into MS Access (by hand) then into Oracle. I am a MS SQL user myself so I do not know what oracle tools there are available. grant [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 14/06/2007 9:53 am Hi y'All Here's the scenario: we have a csv file that contains 1.5 million rows and is provided by an external vendor. we need to either (a) parse the csv and insert into a DB, or (b) do lookups directly on the csv. We do not and cannot have SQLloader. The DB is ORA10g. The servers are old and under fairly high load all the time. We've tried splitting the csv into smaller chunks and inserting into the DB and also trying a SQL link straight into the CSV. Anything we try time's out and basically brings down the server. Who can shed light on the most efficient way to solve this problem? Thanks Grant br --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
From Charlie A's presentation of Hidden Gems I got the impression that CF8 can loop over a file without loading it completely in to memory... I'll look in to it and let you know, unless someone else knows for sure already? Andrew Scott wrote: Actually wouldn't say they are new functions... Unless I am missing something? But yes you can now loop over a file using cfloop, but the problem is that has to be in memory. And cffile can now process files line by line. However, all things aside when you deal with a large file CF is very slow when processing files. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Dale Fraser Sent: Thursday, 14 June 2007 10:36 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Not sure if this is an option. But CF8 has new file functions that do incremental reads also. Regards Dale Fraser http://dalefraser.blogspot.com From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, 14 June 2007 10:27 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Grant, That's why I thought I would add that there might be an application already out there, I can't be 100% sure but I have seen apps that have been able to manipulate CSV files (IIS Server log files in particular) but whether they will do what you are looking at doing then that I can't answer. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of grant Sent: Thursday, 14 June 2007 10:11 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Thanks Andrew. That's actually the path we're heading down (a custom java app); I thought I'd post here to ensure we're not re-inventing the wheel... Thanks! On 14/06/07, Scott Thornton [EMAIL PROTECTED] wrote: Perhaps you could import it into MS Access (by hand) then into Oracle. I am a MS SQL user myself so I do not know what oracle tools there are available. grant [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 14/06/2007 9:53 am Hi y'All Here's the scenario: we have a csv file that contains 1.5 million rows and is provided by an external vendor. we need to either (a) parse the csv and insert into a DB, or (b) do lookups directly on the csv. We do not and cannot have SQLloader. The DB is ORA10g. The servers are old and under fairly high load all the time. We've tried splitting the csv into smaller chunks and inserting into the DB and also trying a SQL link straight into the CSV. Anything we try time's out and basically brings down the server. Who can shed light on the most efficient way to solve this problem? Thanks Grant br --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Cool, I haven't tried it since the good old CF 5.0 daysJ But I did find a difference in speed in the processing. But then again, I had to traverse over a month of these files. And after 5 days had gotten no where fast, till I bit the bullet wrote my C++ application ran it then had the files into a DB within 2-3 hours. I can tell you that CF MX 6.0+ will be faster, but not fast enough. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Rob Wilson Sent: Thursday, 14 June 2007 10:51 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Hi Andrew, some of the smaller files we handle are in the order of 5 meg and larger files have been in the order of 12 meg ...we have had major issues with servers timing out on large files - CSV and the dreadful XLS files - depending on who created the files even became a factor i.e errors within the file (at least the Fed. Govt sent us XML files - in some cases 3 XML files to run in sequence each XML file in the order of 5-8 meg each without the server timing out) ... in our case a manual import wasn't a option - a daily or in some case twice daily import of data that updated several tables Rob Smartnumbers Auction site 2004 - 2006 Auctiontrader - 1999 - current Directwholesale - 2001 - current _ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, 14 June 2007 10:29 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Rob, I doubt CF will be able to handle a query of this magnitude that grant is looking at, but to split the file up into 1mg chunks could be viable. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Rob Wilson Sent: Thursday, 14 June 2007 10:16 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Hi Grant, we have used http://www.emerle.net/programming/display.cfm/t/cfx_text2query with success Cheers Rob _ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of grant Sent: Thursday, 14 June 2007 9:54 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Large CSV's and server timeouts Hi y'All Here's the scenario: we have a csv file that contains 1.5 million rows and is provided by an external vendor. we need to either (a) parse the csv and insert into a DB, or (b) do lookups directly on the csv. We do not and cannot have SQLloader. The DB is ORA10g. The servers are old and under fairly high load all the time. We've tried splitting the csv into smaller chunks and inserting into the DB and also trying a SQL link straight into the CSV. Anything we try time's out and basically brings down the server. Who can shed light on the most efficient way to solve this problem? Thanks Grant BR NOD32 2328 (20070613) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Hmm... That might be a good question.. You could be right on that, never tried that new feature as of yet. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 10:54 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
After a quick check, I am thinking of CFLOOP. CFLOOP now has a file attritbute. In CF8 Docs: == Attribute: file (Optional) The absolute path and filename of the text file to read, one line at a time. This is helpful when reading large text files, because you can reuse the value of the index variable, which contains the current line of the file. When the loop completes, ColdFusion closes the file. == Usage: === cfloop file=c:\temp\simplefile.txt index=line cfoutput#line#/cfoutputbr /cfloop === roll on CF8! Andrew Scott wrote: Hmm... That might be a good question.. You could be right on that, never tried that new feature as of yet. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 10:54 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Doh, I thought you were talking about cfloop:-) Anyway, I am assuming that it loads it into memory to process. But really thinking about it, that would be a very hungry way of doing that new feature. So I hope I am wrong.. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 11:11 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts After a quick check, I am thinking of CFLOOP. CFLOOP now has a file attritbute. In CF8 Docs: == Attribute: file (Optional) The absolute path and filename of the text file to read, one line at a time. This is helpful when reading large text files, because you can reuse the value of the index variable, which contains the current line of the file. When the loop completes, ColdFusion closes the file. == Usage: === cfloop file=c:\temp\simplefile.txt index=line cfoutput#line#/cfoutputbr /cfloop === roll on CF8! Andrew Scott wrote: Hmm... That might be a good question.. You could be right on that, never tried that new feature as of yet. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 10:54 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
hmmm I guess I was reading that it doesn't load it in to memory, as the last line in the docs say ColdFusion closes the file. ...but you are right, it doesn't actually say it doesn't load the entire file... may be time for some testing later on to find out! Andrew Scott wrote: Doh, I thought you were talking about cfloop:-) Anyway, I am assuming that it loads it into memory to process. But really thinking about it, that would be a very hungry way of doing that new feature. So I hope I am wrong.. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 11:11 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts After a quick check, I am thinking of CFLOOP. CFLOOP now has a file attritbute. In CF8 Docs: == Attribute: file (Optional) The absolute path and filename of the text file to read, one line at a time. This is helpful when reading large text files, because you can reuse the value of the index variable, which contains the current line of the file. When the loop completes, ColdFusion closes the file. == Usage: === cfloop file=c:\temp\simplefile.txt index=line cfoutput#line#/cfoutputbr /cfloop === roll on CF8! Andrew Scott wrote: Hmm... That might be a good question.. You could be right on that, never tried that new feature as of yet. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 10:54 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
NDA peoples NDA... -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, 14 June 2007 11:17 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Doh, I thought you were talking about cfloop:-) Anyway, I am assuming that it loads it into memory to process. But really thinking about it, that would be a very hungry way of doing that new feature. So I hope I am wrong.. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 11:11 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts After a quick check, I am thinking of CFLOOP. CFLOOP now has a file attritbute. In CF8 Docs: == Attribute: file (Optional) The absolute path and filename of the text file to read, one line at a time. This is helpful when reading large text files, because you can reuse the value of the index variable, which contains the current line of the file. When the loop completes, ColdFusion closes the file. == Usage: === cfloop file=c:\temp\simplefile.txt index=line cfoutput#line#/cfoutputbr /cfloop === roll on CF8! Andrew Scott wrote: Hmm... That might be a good question.. You could be right on that, never tried that new feature as of yet. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 10:54 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~--- inline: winmail.dat
[cfaussie] Re: Large CSV's and server timeouts
NDA on the publicly available BETA and Docs? On Jun 14, 1:40 pm, Steve Onnis [EMAIL PROTECTED] wrote: NDA peoples NDA... -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, 14 June 2007 11:17 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Doh, I thought you were talking about cfloop:-) Anyway, I am assuming that it loads it into memory to process. But really thinking about it, that would be a very hungry way of doing that new feature. So I hope I am wrong.. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd.www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 11:11 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts After a quick check, I am thinking of CFLOOP. CFLOOP now has a file attritbute. In CF8 Docs: == Attribute: file (Optional) The absolute path and filename of the text file to read, one line at a time. This is helpful when reading large text files, because you can reuse the value of the index variable, which contains the current line of the file. When the loop completes, ColdFusion closes the file. == Usage: === cfloop file=c:\temp\simplefile.txt index=line cfoutput#line#/cfoutputbr /cfloop === roll on CF8! Andrew Scott wrote: Hmm... That might be a good question.. You could be right on that, never tried that new feature as of yet. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 10:54 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts winmail.dat 3KDownload --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Actually I would take it to mean that it doesn't load the entire file.. So that is good to know, but a quick test wouldn't hurt. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 11:24 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts hmmm I guess I was reading that it doesn't load it in to memory, as the last line in the docs say ColdFusion closes the file. ...but you are right, it doesn't actually say it doesn't load the entire file... may be time for some testing later on to find out! Andrew Scott wrote: Doh, I thought you were talking about cfloop:-) Anyway, I am assuming that it loads it into memory to process. But really thinking about it, that would be a very hungry way of doing that new feature. So I hope I am wrong.. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 11:11 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts After a quick check, I am thinking of CFLOOP. CFLOOP now has a file attritbute. In CF8 Docs: == Attribute: file (Optional) The absolute path and filename of the text file to read, one line at a time. This is helpful when reading large text files, because you can reuse the value of the index variable, which contains the current line of the file. When the loop completes, ColdFusion closes the file. == Usage: === cfloop file=c:\temp\simplefile.txt index=line cfoutput#line#/cfoutputbr /cfloop === roll on CF8! Andrew Scott wrote: Hmm... That might be a good question.. You could be right on that, never tried that new feature as of yet. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 10:54 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Steve. Coldfusion 8 is public Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Steve Onnis Sent: Thursday, 14 June 2007 11:40 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts NDA peoples NDA... -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, 14 June 2007 11:17 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Doh, I thought you were talking about cfloop:-) Anyway, I am assuming that it loads it into memory to process. But really thinking about it, that would be a very hungry way of doing that new feature. So I hope I am wrong.. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 11:11 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts After a quick check, I am thinking of CFLOOP. CFLOOP now has a file attritbute. In CF8 Docs: == Attribute: file (Optional) The absolute path and filename of the text file to read, one line at a time. This is helpful when reading large text files, because you can reuse the value of the index variable, which contains the current line of the file. When the loop completes, ColdFusion closes the file. == Usage: === cfloop file=c:\temp\simplefile.txt index=line cfoutput#line#/cfoutputbr /cfloop === roll on CF8! Andrew Scott wrote: Hmm... That might be a good question.. You could be right on that, never tried that new feature as of yet. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 10:54 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Hello, grant wrote: Here's the scenario: we have a csv file that contains 1.5 million rows and is provided by an external vendor. we need to either (a) parse the csv and insert into a DB, or (b) do lookups directly on the csv. Coming in a bit late on this. The fact that there are CFXs that do this has been mentioned, specifically the one to read a CSV into a query and we have tried that one ourselves. We had issues but we were doing non-standard things so we didn't blame the CFX. There is also a CFX that just reads a text file in line-by-line but it had bugs with some text types so we rolled our own. We use it to read log files, which can be massive, and it reads any file that is conventionally line-delimited (CF, LF or both) into a query object that you can then use as you feel fit. If I read your question directly you don't need to put the data in toto into a database, just be able to get stuff out of it. In which case a query object is just the thing. Out tag is going to be put in the public domain once our new website is up along with a few other useful goodies we have put together over the years but I am happy to flick it to you now if you wish. It can read an entire text file or start at a defined point so you can walk thru the file line-by-line if you wish. HTH Kym K PS, Somebody said CF is an interpreted language. It used to be but that was two (almost 3) versions ago. These days it is compiled and so is a lot faster. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
No,
There are new file tags, lots of them and they will help you.
FileClose, FileIsEOF, FileOpen, FileRead,
FileReadBinary, FileReadLine, FileWrite, and FileWriteLine.
Specifically look at
cfscript
myfile = FileOpen(c:\ColdFusionScorpio\wwwroot\test1.txt, read);
while(NOT FileisEOF(myfile))
{
x = FileReadLine(myfile); // read line
WriteOutput(#x#);
}
FileClose(myfile);
/cfscript
Which will read line by line and not exhaust memory
. The following file manipulation functions have also been added:
FileCopy,
FileDelete, FileMove, FileSetAccessMode, FileSetAttribute,
FileSetLastModified, and GetFileInfo.
Regards
Dale Fraser
http://dalefraser.blogspot.com
-Original Message-
From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf
Of Andrew Scott
Sent: Thursday, 14 June 2007 11:17 AM
To: cfaussie@googlegroups.com
Subject: [cfaussie] Re: Large CSV's and server timeouts
Doh,
I thought you were talking about cfloop:-) Anyway, I am assuming that it
loads it into memory to process. But really thinking about it, that would be
a very hungry way of doing that new feature. So I hope I am wrong..
Andrew Scott
Senior Coldfusion Developer
Aegeon Pty. Ltd.
www.aegeon.com.au
Phone: +613 8676 4223
Mobile: 0404 998 273
-Original Message-
From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf
Of David Harris
Sent: Thursday, 14 June 2007 11:11 AM
To: cfaussie
Subject: [cfaussie] Re: Large CSV's and server timeouts
After a quick check, I am thinking of CFLOOP.
CFLOOP now has a file attritbute.
In CF8 Docs:
==
Attribute: file (Optional)
The absolute path and filename of the text file to read, one
line at a time. This is helpful when reading large text files,
because you can reuse the value of the index variable,
which contains the current line of the file. When the loop
completes, ColdFusion closes the file.
==
Usage:
===
cfloop file=c:\temp\simplefile.txt index=line
cfoutput#line#/cfoutputbr
/cfloop
===
roll on CF8!
Andrew Scott wrote:
Hmm... That might be a good question..
You could be right on that, never tried that new feature as of yet.
Andrew Scott
Senior Coldfusion Developer
Aegeon Pty. Ltd.
www.aegeon.com.au
Phone: +613 8676 4223
Mobile: 0404 998 273
-Original Message-
From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On
Behalf
Of David Harris
Sent: Thursday, 14 June 2007 10:54 AM
To: cfaussie
Subject: [cfaussie] Re: Large CSV's and server timeouts
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaussie@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en
-~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
FileRead also has a buffer size so you can get bits at a time. Regards Dale Fraser http://dalefraser.blogspot.com -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, 14 June 2007 11:17 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Doh, I thought you were talking about cfloop:-) Anyway, I am assuming that it loads it into memory to process. But really thinking about it, that would be a very hungry way of doing that new feature. So I hope I am wrong.. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 11:11 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts After a quick check, I am thinking of CFLOOP. CFLOOP now has a file attritbute. In CF8 Docs: == Attribute: file (Optional) The absolute path and filename of the text file to read, one line at a time. This is helpful when reading large text files, because you can reuse the value of the index variable, which contains the current line of the file. When the loop completes, ColdFusion closes the file. == Usage: === cfloop file=c:\temp\simplefile.txt index=line cfoutput#line#/cfoutputbr /cfloop === roll on CF8! Andrew Scott wrote: Hmm... That might be a good question.. You could be right on that, never tried that new feature as of yet. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 10:54 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
Steve, You might have missed it, but CF8 went public beta :) Regards Dale Fraser http://dalefraser.blogspot.com -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Steve Onnis Sent: Thursday, 14 June 2007 11:40 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts NDA peoples NDA... -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, 14 June 2007 11:17 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Doh, I thought you were talking about cfloop:-) Anyway, I am assuming that it loads it into memory to process. But really thinking about it, that would be a very hungry way of doing that new feature. So I hope I am wrong.. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 11:11 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts After a quick check, I am thinking of CFLOOP. CFLOOP now has a file attritbute. In CF8 Docs: == Attribute: file (Optional) The absolute path and filename of the text file to read, one line at a time. This is helpful when reading large text files, because you can reuse the value of the index variable, which contains the current line of the file. When the loop completes, ColdFusion closes the file. == Usage: === cfloop file=c:\temp\simplefile.txt index=line cfoutput#line#/cfoutputbr /cfloop === roll on CF8! Andrew Scott wrote: Hmm... That might be a good question.. You could be right on that, never tried that new feature as of yet. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 10:54 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Large CSV's and server timeouts
that's all good stuff! working with files has been an issue with CF for a while now... Dale Fraser wrote: FileRead also has a buffer size so you can get bits at a time. Regards Dale Fraser http://dalefraser.blogspot.com -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Thursday, 14 June 2007 11:17 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Large CSV's and server timeouts Doh, I thought you were talking about cfloop:-) Anyway, I am assuming that it loads it into memory to process. But really thinking about it, that would be a very hungry way of doing that new feature. So I hope I am wrong.. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 11:11 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts After a quick check, I am thinking of CFLOOP. CFLOOP now has a file attritbute. In CF8 Docs: == Attribute: file (Optional) The absolute path and filename of the text file to read, one line at a time. This is helpful when reading large text files, because you can reuse the value of the index variable, which contains the current line of the file. When the loop completes, ColdFusion closes the file. == Usage: === cfloop file=c:\temp\simplefile.txt index=line cfoutput#line#/cfoutputbr /cfloop === roll on CF8! Andrew Scott wrote: Hmm... That might be a good question.. You could be right on that, never tried that new feature as of yet. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of David Harris Sent: Thursday, 14 June 2007 10:54 AM To: cfaussie Subject: [cfaussie] Re: Large CSV's and server timeouts --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
