Re: CSV ODBC driver under Windows 2008
Ok, I have more information about this problem. In the CSV file, if I replace all semicolons by commas, it works perfectly. The problem is that, either under W 7 or W 2008, it seems that one can set the delimiter and some other format parameters in odbcad32.exe, but they are not saved anywere, at least they are not saved in a place they can be retrieved and used correctly. The consequence is that the delimiter by default will alway be used. Under W 7, no problem, the delimiter by default seems to be the semicolon, so my file is compatible, but under W2008, the default delimiter seem to be the comma then the fields are not recognized. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356672 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CSV ODBC driver under Windows 2008
>>You might try passing delimiter=';' as an additional dsn parameter. BINGO! The problem is that the file Schema.ini is not properly updated by the 32 bit ODBC Administrator. In my W 7 file I see Format=Delimited(;) which is correct, but in the W 2008 file I have Format=CSVDelimited I set Delimited(;) in the W 2008 file and now it works like a charm. Thanks. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356675 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CSV ODBC driver under Windows 2008 (finaly)
I finaly found the problem: When you open the ODBC Administrator, you first select the database you want to update, you click on configure, you see the name of your datasource, fine, then you click Options, you still see the name of the datasource you are configuring, then you click Define format, you think you will define the format for the datasource you are working on... WRONG! You have to select your datasource again, otherwise you will uptade the . If this is not a bug, it is a bad flaw in design for the least. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356676 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CSV ODBC driver under Windows 2008
You might try passing delimiter=';' as an additional dsn parameter. Which odbc driver are you using in windows? The MS text file driver you can specify an ini file with the delimiter and other parameters. Although I have never tried using that. http://msdn.microsoft.com/en-us/library/windows/desktop/ms709353(v=vs.85).aspx Byron Mann Lead Engineer & Architect HostMySite.com On Aug 30, 2013 10:46 AM, <> wrote: > > Ok, I have more information about this problem. > > In the CSV file, if I replace all semicolons by commas, it works perfectly. > The problem is that, either under W 7 or W 2008, it seems that one can set > the delimiter and some other format parameters in odbcad32.exe, but they > are not saved anywere, at least they are not saved in a place they can be > retrieved and used correctly. The consequence is that the delimiter by > default will alway be used. > > Under W 7, no problem, the delimiter by default seems to be the semicolon, > so my file is compatible, but under W2008, the default delimiter seem to be > the comma then the fields are not recognized. > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356674 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CSV File Upload. best way?
You basically have a couple of options (there are obviously many more ways, these are the two approaches that I would take): 1) if it's a small file, just read it into memory and loop through the contents of the file 2) if it's a large file, set up a Microsoft Access database to use an external file (the .csv file) for its data. Performance will be MUCH better than read and parse with CF, and gives you the advantage of working with the data just like you would with any other recordset. I use this for a regular 35,000 record data import and it takes minutes compared to hours using the other approach. Pete On Wed, Mar 4, 2009 at 5:54 AM, Glyn Jackson wrote: > > Hi everyone, > > I have a CSV file I need to take the values out of and update a database > with them. Any suggestion or best ways to do this? > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320106 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CSV Import - Update with Commas in the Content
yes, you need to either change your export routine to enclose all 'fields' in double quotes, or change your export format to, say, tab-delimited file instead of comma-delimited. most databases have built-in routines and functions to automate import of data from text files, in which you can usually specify how your data is delimited and what values are enclosed with. you can keep cf completely out of the importing data routine if you are sure of the data quality. otherwise, you can parse the data in the file with cf and, depending on your business logic, either reject the import completely if the data does not conform to your requirements, or only import the rows that do conform, and notify the user of the rows that were not imported. hth Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Michael Grove wrote: > I am trying to figure out how to update a sql table using a CSV file that has > commas in the data fields. The long and Short of it, is that we have a SEO > company rewriting our keywords, meta descriptions and titles for our product > database. We were able to build a simple CSV export so that they could add > the new info. Now we need a way to import it back into the systm and update > all of the records based on the productID, wich is in the csv file. > > The developer we are using says it can be done, but we cannot have commas in > any of the fields. We can wrap the columns in double quotes on an export, > cant we do this on the import? > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314174 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
re: CSV Import - Update with Commas in the Content
I'm not sure I understand. Are you saying they are providing you data that has delimiters within the data, but the data is not quoted? Like: [EMAIL PROTECTED],Joe,Smith,Joe, John & Sons Inc. If so there is nothing you can do about that, they need to be properly quoting (with double quotes or some other identifier) data that contains the delimiter, like this: [EMAIL PROTECTED],Joe,Smith,"Joe, John & Sons Inc." Then you need a data import routine that can correctly handle that. Most of the ColdFusion CSV handling code I come across does not handle this very well. -Ryan From: Michael Grove <[EMAIL PROTECTED]> Sent: Monday, October 20, 2008 8:27 PM To: cf-talk Subject: CSV Import - Update with Commas in the Content I am trying to figure out how to update a sql table using a CSV file that has commas in the data fields. The long and Short of it, is that we have a SEO company rewriting our keywords, meta descriptions and titles for our product database. We were able to build a simple CSV export so that they could add the new info. Now we need a way to import it back into the systm and update all of the records based on the productID, wich is in the csv file. The developer we are using says it can be done, but we cannot have commas in any of the fields. We can wrap the columns in double quotes on an export, cant we do this on the import? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314171 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CSV Generation MEMORY SUCK
Thanks Larry. ~Brad -Original Message- From: Larry Lyons [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 04, 2008 1:34 PM To: CF-Talk Subject: Re: CSV Generation MEMORY SUCK Here are the results of your code with java.lang.runtime. Forgot to mention that the JVM is 1.5.0_15-b04. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306845 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CSV Generation MEMORY SUCK
Just ran the same code on Open BlueDragon. NThis test probably is not the equivalent of the previous tests, at home here I'm running this app on a MacBook (core duo 2.16 ghz with 2 gb RAM), running OSX 10.4 Tiger. J2SE 5. But the results are similar: Memory Before: 26 Megs string & string: 553776ms String Length: 65 Memory After: 45 Megs -- Increase of 19 Megs Memory Before: 32 Megs cfsavecontent: 65ms String Length: 1400010 Memory After: 39 Megs -- Increase of 8 Megs > Here are the results of your code with java.lang.runtime. Forgot to > mention that the JVM is 1.5.0_15-b04. > > Memory Before: 28 Megs > string & string: 99642ms > String Length: 65 > Memory After: 91 Megs -- Increase of 63 Megs > > Memory Before: 29 Megs > cfsavecontent: 63ms > String Length: 850003 > Memory After: 37 Megs -- Increase of 8 Megs > > Basically what this tells me is that you should use cfsavecontent > rather than concatenating strings. But I was not expecting such a > difference. > > larry > > > Here are the results of your code (again BD for J2EE running on > JBoss > > AS 4.22): > > > > string & string&: 33251ms > > String Length: 39 > > cfsavecontent: 62ms > > String Length: 570006 > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306844 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CSV Generation MEMORY SUCK
Here are the results of your code with java.lang.runtime. Forgot to mention that the JVM is 1.5.0_15-b04. Memory Before: 28 Megs string & string: 99642ms String Length: 65 Memory After: 91 Megs -- Increase of 63 Megs Memory Before: 29 Megs cfsavecontent: 63ms String Length: 850003 Memory After: 37 Megs -- Increase of 8 Megs Basically what this tells me is that you should use cfsavecontent rather than concatenating strings. But I was not expecting such a difference. larry > Here are the results of your code (again BD for J2EE running on JBoss > AS 4.22): > > string & string&: 33251ms > String Length: 39 > cfsavecontent: 62ms > String Length: 570006 > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306832 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
Here are the results of your code (again BD for J2EE running on JBoss AS 4.22): string & string&: 33251ms String Length: 39 cfsavecontent: 62ms String Length: 570006 I ran the test several times, mainly because the results for cfsavecontent looked so much like an outlier, but I got similar results. I'll have to dig up that code you posted, but in general since BlueDragon for J2EE sits on top of a java application server it can access the java.lang.runtime object no problem. When I get home tonight I'll run these tests again using open BlueDragon for J2EE, but I doubt there will be any differences. regards, larry >That's pretty cool, Larry. I was wondering about BD and Smith. >Will J2EE BD let you create the java.lang.runtime object to get memory >usage etc? If so, I would be interested in seeing the results of my >version of the test which reported the memory increase for each test. >(I posted the code yesterday. Let me know if the word-wraps trashed >it). > >~Brad > >= > >Rick, > >I thought I run your code test cfset vs cfsavecontent on a slightly >different platform - BlueDragon for J2EE running on JBoss AS 4.22 on an >XP Pox (intel core 2 duo with 2gig memory). Here are the results: > >cfset 145861ms : 488895 > >cfsavecontent 766ms : 488895 > >regards, >larry ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306831 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CSV Generation MEMORY SUCK
That's pretty cool, Larry. I was wondering about BD and Smith. Will J2EE BD let you create the java.lang.runtime object to get memory usage etc? If so, I would be interested in seeing the results of my version of the test which reported the memory increase for each test. (I posted the code yesterday. Let me know if the word-wraps trashed it). ~Brad = Rick, I thought I run your code test cfset vs cfsavecontent on a slightly different platform - BlueDragon for J2EE running on JBoss AS 4.22 on an XP Pox (intel core 2 duo with 2gig memory). Here are the results: cfset 145861ms : 488895 cfsavecontent 766ms : 488895 regards, larry ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306819 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CSV Generation MEMORY SUCK
> This whole discussion prompted two blog entries... > > Regarding the javaCSV library: > http://www.opensourcecf. com/1/2008/06/Ja> vaCSV-for-creating-large-CSV-and-other-delmiited-files-with-Coldfusion. > cfm > *or http://tinyurl.com/58o7ox* > ** > Regarding my cfsavecontent performance tests: > http://www.opensourcecf. > com/1/2008/06/cfsavecontent-vs-cfset-for-performance-improvement.cfm > *or http://tinyurl.com/6cafst* > > Rick Rick, I thought I run your code test cfset vs cfsavecontent on a slightly different platform - BlueDragon for J2EE running on JBoss AS 4.22 on an XP Pox (intel core 2 duo with 2gig memory). Here are the results: cfset 145861ms : 488895 cfsavecontent 766ms : 488895 regards, larry ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306818 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CSV Generation MEMORY SUCK
Who, that's weird-- this message came in my inbox late last night as a duplicate of something I sent yesterday afternoon... I wasn't even in front of a computer at 11:45 pm. :) ~Brad -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2008 11:45 AM To: CF-Talk Subject: RE: CSV Generation MEMORY SUCK (Sorry, I got a little CTRL-Enter happy and sent before I was ready...) Building up strings in cfsavecontent also concatenates to the result variable so the problem is the same. = Hmm, I don't think ... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306810 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
This whole discussion prompted two blog entries... Regarding the javaCSV library: http://www.opensourcecf.com/1/2008/06/JavaCSV-for-creating-large-CSV-and-other-delmiited-files-with-Coldfusion.cfm *or http://tinyurl.com/58o7ox* ** Regarding my cfsavecontent performance tests: http://www.opensourcecf.com/1/2008/06/cfsavecontent-vs-cfset-for-performance-improvement.cfm *or http://tinyurl.com/6cafst* Rick ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306809 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CSV Generation MEMORY SUCK
(Sorry, I got a little CTRL-Enter happy and sent before I was ready...) Building up strings in cfsavecontent also concatenates to the result variable so the problem is the same. = Hmm, I don't think you are correct Brian. I just whipped up a test of string concatenation. Please spare the "proper load test" flames. This is NOT a load test-- it is intended to make a process run long enough to capture a thread stack. Actually, in the context of large file generations I would call it quite appropriate. Concatenation "Hello World. " together 30 thousand times on CF 7.0.2 Ent (Win) shows a vast difference between using & and simply outputting it inside a cfsavecontent. The & definitely spent all its time doing a Java.lang.String.concat(). The cfsavecontent not only executed 211 times faster, I didn't see a single String.contat() happening. Here are the results: string & string: 17093ms String Length: 39 cfsavecontent: 125ms String Length: 39 And here is the code: String Length: #len(string1)# Hello World. String Length: #len(string2)# ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306789 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
Just a 411 I found a nice little tute on generating csv's using the StringBuffer class in ColdFusion http://www.stillnetstudios.com/2007/03/07/java-strings-in-coldfusion/ -- "The important thing in science is not so much to obtain new facts as to discover new ways of thinking about them." - Sir William Bragg ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306764 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
Just experience, since I've tried all three options (concatenation, cfsavecontent, and StringBuffer) and have had the first two generate out of memory errors while the StringBuffer worked correctly. So while cfsavecontent may indeed be faster and use less memory, I'm still pretty sure that the StringBuffer approach is better especially for very large strings (these were 100+ megabyte CSV files). On Tue, Jun 3, 2008 at 12:14 PM, Brad Wood <[EMAIL PROTECTED]> wrote: > Good to know. > > What is your source of this information? > > ~Brad > > > From: Brian Kotek [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 03, 2008 11:11 AM > > Building up strings in cfsavecontent also concatenates to the result > variable so the problem is the same. > > On Tue, Jun 3, 2008 at 11:16 AM, Brad Wood <[EMAIL PROTECTED]> > wrote: > > > I wonder what Java string objects are used when you create a large > > string by outputting inside a cfsavecontent. > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306751 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CSV Generation MEMORY SUCK
Dang closed source apps-- if only we could just go look at the code! :) ~Brad -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2008 2:26 PM To: CF-Talk Subject: Re: CSV Generation MEMORY SUCK Well, since we're all conducting our own little tests, here's MY test code: the cfset method took 64 seconds. The cfsavecontent method only takes 203ms. It has GOT to be using a stringbuffer then converting the result to a string at the end. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306732 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
Well, since we're all conducting our own little tests, here's MY test code: the cfset method took 64 seconds. The cfsavecontent method only takes 203ms. It has GOT to be using a stringbuffer then converting the result to a string at the end. #end-start#ms : #len(result)# #i# #end-start#ms : #len(result)# ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306731 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CSV Generation MEMORY SUCK
timed out after 15 min lol I find this very interesting in that my totally unscientific though process was: Since cfsavecontent is so damn easy to use it *must* be resource intensive. Go figure-- ColdFusion strikes again. If string.concat() creates a brand new string object in memory to hold the combination of the two original strings, then given a consistently average sized string being added each time I would expect memory consumption to be exponential. For instance, if you concatenated a string 1 byte in size 10 times over, you would consume 55 bytes of memory. 1+2+3+4+5+6+7+8+9+10=55 For the life of me I can't figure out what the Big-O notation would be for that though... ~Brad -Original Message- From: Gerald Guido [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2008 1:44 PM To: CF-Talk Subject: Re: CSV Generation MEMORY SUCK I did a million loops - I don't know what possessed me to do that. Memory was "measured" using task manager. Totally unscientific. I did a restart on the service before each trial. CF 8 developer 2 gig ram Java v. 1.6.0_01 cfsavecontent 2281 ms 192,356 k start 260,872K after 68.516 k difference concatenation using cfset 192,362 k start 580,952 k after 388,590 k difference G On Tue, Jun 3, 2008 at 1:55 PM, Brad Wood <[EMAIL PROTECTED]> wrote: > Ok, here are my memory usage stats on CF 7. Someone please correct me > if my code is wrong. It's a little messy, and I apologize for that. > > -- "The important thing in science is not so much to obtain new facts as to discover new ways of thinking about them." - Sir William Bragg ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306728 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
Wow, I just came back to this thread. REALLY makes me wonder how they're handling cfsavecontent! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306726 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
I did a million loops - I don't know what possessed me to do that. Memory was "measured" using task manager. Totally unscientific. I did a restart on the service before each trial. CF 8 developer 2 gig ram Java v. 1.6.0_01 cfsavecontent 2281 ms 192,356 k start 260,872K after 68.516 k difference concatenation using cfset timed out after 15 min 192,362 k start 580,952 k after 388,590 k difference I find this very interesting in that my totally unscientific though process was: Since cfsavecontent is so damn easy to use it *must* be resource intensive. G On Tue, Jun 3, 2008 at 1:55 PM, Brad Wood <[EMAIL PROTECTED]> wrote: > Ok, here are my memory usage stats on CF 7. Someone please correct me > if my code is wrong. It's a little messy, and I apologize for that. > > -- "The important thing in science is not so much to obtain new facts as to discover new ways of thinking about them." - Sir William Bragg ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306722 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CSV Generation MEMORY SUCK
Ok, here are my memory usage stats on CF 7. Someone please correct me if my code is wrong. It's a little messy, and I apologize for that. Memory Before: 83 Megs string & string: 52795ms String Length: 65 Memory After: 101 Megs -- Increase of 17 Megs Memory Before: 85 Megs cfsavecontent: 172ms String Length: 65 Memory After: 97 Megs -- Increase of 12 Megs As you can see, cfsavecontent used about 1/3 less memory that the other method. Not nearly as proportional savings the execution time... Wow-- here are the numbers from my CF8 box: Memory Before: 161 Megs string & string: 26530ms String Length: 65 Memory After: 195 Megs -- Increase of 35 Megs Memory Before: 158 Megs cfsavecontent: 47ms String Length: 65 Memory After: 165 Megs -- Increase of 7 Megs This time the cfsavecontent used 4/5ths less memory! Very interesting indeed... Of course, please understand there are many factors and JVM settings that go into this. I'm not trying to claim everyone else will get results like this. Here the latest version of my (slightly sloppy) code: Memory Before: #round(memory_before)# Megs String Length: #len(string1)# Memory After: #round(memory_after)# Megs -- Increase of #round(memory_after - memory_before)# Megs Memory Before: #round(memory_before)# Megs Hello World. String Length: #len(string2)# Memory After: #round(memory_after)# Megs -- Increase of #round(memory_after - memory_before)# Megs ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306715 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CSV Generation MEMORY SUCK
No, but I would like to. The problem is I'm not sure how to get any exact numbers. I have SeeFusion installed which will tell me the overall heap size of my JVM, but it might be difficult to nail down how much was used by one thread. Alternatively, there are the totalMemory() and maxMemory() methods in the runtime object available in java.lang.Runtime. I could run that before and after the code ran, but I'm not sure how garbage collection would affect that. Suggestions? ~Brad -Original Message- From: Gerald Guido [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2008 12:11 PM To: CF-Talk Subject: Re: CSV Generation MEMORY SUCK Did you compare the memory usage by chance? G On Tue, Jun 3, 2008 at 1:07 PM, Brad Wood <[EMAIL PROTECTED]> wrote: ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306714 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
Did you compare the memory usage by chance? G On Tue, Jun 3, 2008 at 1:07 PM, Brad Wood <[EMAIL PROTECTED]> wrote: > Update: I experienced the same behavior on CF 8 JVM 1.6 (Win). > Well, almost the same-- the CF8 server was actually faster overall. I > would like to point out it is actually a slower server too! > > string & string: 9141ms > String Length: 39 > > cfsavecontent: 31ms > String Length: 39 > > -Original Message- > From: Brad Wood > Sent: Tuesday, June 03, 2008 11:45 AM > To: Brad Wood; 'cf-talk@houseoffusion.com' > Subject: RE: CSV Generation MEMORY SUCK > > Here are the results: > string & string: 17093ms > String Length: 39 > > cfsavecontent: 125ms > String Length: 39 > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306711 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CSV Generation MEMORY SUCK
Update: I experienced the same behavior on CF 8 JVM 1.6 (Win). Well, almost the same-- the CF8 server was actually faster overall. I would like to point out it is actually a slower server too! string & string: 9141ms String Length: 39 cfsavecontent: 31ms String Length: 39 -Original Message- From: Brad Wood Sent: Tuesday, June 03, 2008 11:45 AM To: Brad Wood; 'cf-talk@houseoffusion.com' Subject: RE: CSV Generation MEMORY SUCK Here are the results: string & string: 17093ms String Length: 39 cfsavecontent: 125ms String Length: 39 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306710 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CSV Generation MEMORY SUCK
Hmm, I don't think you are correct Brian. I just whipped up a test of string concatenation. Please spare the "proper load test" flames. This is NOT a load test-- it is intended to make a process run long enough to capture a thread stack. Actually, in the context of large file generations I would call it quite appropriate. Concatenation "Hello World. " together 30 thousand times on CF 7.0.2 Ent (Win) shows a vast difference between using & and simply outputting it inside a cfsavecontent. The & definitely spent all its time doing a Java.lang.String.concat(). The cfsavecontent not only executed 211 times faster, I didn't see a single String.contat() happening. Here are the results: And here is the code: String Length: #len(string1)# Hello World. String Length: #len(string2)# ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306707 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CSV Generation MEMORY SUCK
" where a fair chunk of the CF9 team are hosting a BOF session :-)" That was a fun and ruckus BOF session at CF.Objective()! Wil Genovese ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306705 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CSV Generation MEMORY SUCK
Good to know. What is your source of this information? ~Brad From: Brian Kotek [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2008 11:11 AM Building up strings in cfsavecontent also concatenates to the result variable so the problem is the same. On Tue, Jun 3, 2008 at 11:16 AM, Brad Wood <[EMAIL PROTECTED]> wrote: > I wonder what Java string objects are used when you create a large > string by outputting inside a cfsavecontent. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306704 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
Building up strings in cfsavecontent also concatenates to the result variable so the problem is the same. On Tue, Jun 3, 2008 at 11:16 AM, Brad Wood <[EMAIL PROTECTED]> wrote: > I wonder what Java string objects are used when you create a large > string by outputting inside a cfsavecontent. > > I'm sure ColdFusion implements strings the way it does because it was > found to be the most efficient method for the majority of programming > needs. > > ~Brad > > -Original Message- > From: Brian Kotek [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 03, 2008 10:12 AM > To: CF-Talk > Subject: Re: CSV Generation MEMORY SUCK > > Probably because it can't know if that's what you actually want to do. > We > probably need a new function StringAppend or something that would be > able to > do this. Might be time to hit the wish list! ;-) > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306703 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CSV Generation MEMORY SUCK
On Tuesday 03 Jun 2008, Brian Kotek wrote: > Probably because it can't know if that's what you actually want to do. We > probably need a new function StringAppend or something that would be able > to do this. Might be time to hit the wish list! ;-) I'm leaving for Scotch on the Rocks in ~12 hours, where a fair chunk of the CF9 team are hosting a BOF session :-) -- Tom Chiverton This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306699 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CSV Generation MEMORY SUCK
I wonder what Java string objects are used when you create a large string by outputting inside a cfsavecontent. I'm sure ColdFusion implements strings the way it does because it was found to be the most efficient method for the majority of programming needs. ~Brad -Original Message- From: Brian Kotek [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 03, 2008 10:12 AM To: CF-Talk Subject: Re: CSV Generation MEMORY SUCK Probably because it can't know if that's what you actually want to do. We probably need a new function StringAppend or something that would be able to do this. Might be time to hit the wish list! ;-) ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306696 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
Probably because it can't know if that's what you actually want to do. We probably need a new function StringAppend or something that would be able to do this. Might be time to hit the wish list! ;-) On Tue, Jun 3, 2008 at 4:36 AM, Tom Chiverton <[EMAIL PROTECTED]> wrote: > On Monday 02 Jun 2008, Rick Root wrote: > > I found a nice little java class library called JavaCSV that handles all > > the file writing and dropped my time from 68 seconds to 18 seconds. That > > has potential! > > Why CF can't translate '&' to a StringBuffer append I'll never know... > > -- > Tom Chiverton > > > > This email is sent for and on behalf of Halliwells LLP. > > Halliwells LLP is a limited liability partnership registered in England and > Wales under registered number OC307980 whose registered office address is at > Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A > list of members is available for inspection at the registered office. Any > reference to a partner in relation to Halliwells LLP means a member of > Halliwells LLP. Regulated by The Solicitors Regulation Authority. > > CONFIDENTIALITY > > This email is intended only for the use of the addressee named above and > may be confidential or legally privileged. If you are not the addressee you > must not read it and must not use any information contained in nor copy it > nor inform any person other than Halliwells LLP or the addressee of its > existence or contents. If you have received this email in error please > delete it and notify Halliwells LLP IT Department on 0870 365 2500. > > For more information about Halliwells LLP visit www.halliwells.com. > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306695 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CSV Generation MEMORY SUCK
On Monday 02 Jun 2008, Rick Root wrote: > I found a nice little java class library called JavaCSV that handles all > the file writing and dropped my time from 68 seconds to 18 seconds. That > has potential! Why CF can't translate '&' to a StringBuffer append I'll never know... -- Tom Chiverton This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306685 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CSV Generation MEMORY SUCK
Don't forget to turn off debugging (or remove the 127.0.0.1 ip) -mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Monday, June 02, 2008 10:19 AM To: CF-Talk Subject: Re: CSV Generation MEMORY SUCK On Mon, Jun 2, 2008 at 10:50 AM, Gaulin, Mark <[EMAIL PROTECTED]> wrote: > > Also, test you page with the user's query but with the output part > (actually writing the file) commented out... If the page is still slow > and a huge memory hog then the file stuff above won't help much and > you'll have to look at running the query in java too, but I but you'll > get something by handling the file better. > I honestly don't think it's the file writing that's the problem. I just commented out the fileWrite() statements inside the tags that would write each line to the file, (the file still being opened with the "header" row being written to it... and it made zero difference at all in the length of time it took to complete. The query itself runs quite fast. Returns a lot of rows but isn't a complex query. Anyway, I put some cfoutput statements in my gateway (I'm calling it as a direct cfc call not a gateway for testing).. output now().gettime() to see the ms as the method call progresses. The query returns its results in less than 4 seconds. The process of generating the csv (around line 330-340 of the sample code I posted earlier) took 62 of the 68 seconds. And that was without actually WRITING the file. the GOOD news is that a TAB delimited file takes considerably less time (32 seconds vs. 68 seconds), cutting the "output time" from 62 seconds to 26 seconds. Which means the csvFormat() function is taking up a very large part of the processing time. This is my csvFormat() function: ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306628 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CSV Generation MEMORY SUCK
Rick, So... The file is a selection of columns and filter criteria - right? It always varies per user Your right - it's a sticky problem :) -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Monday, June 02, 2008 9:33 AM To: CF-Talk Subject: Re: CSV Generation MEMORY SUCK SQL Server 2005. I'm open to suggestion. This is part of an application that allows users to generate CSV files of their own based on their own criteria, so though I'm open to "non-CF" solutions, I'm not sure there really would be anyway except maybe a homegrown java class to handle the work and be more strict with memory consumption Rick On Mon, Jun 2, 2008 at 10:13 AM, Mark Kruger <[EMAIL PROTECTED]> wrote: > Rick, > > What's your DB platform? Are you sure there is not a better "non-cf" > way to do it? > > > Mark A. Kruger, CFG, MCSE > (402) 408-3733 ext 105 > www.cfwebtools.com > www.coldfusionmuse.com > www.necfug.com > > -Original Message- > From: Rick Root [mailto:[EMAIL PROTECTED] > Sent: Monday, June 02, 2008 9:04 AM > To: CF-Talk > Subject: CSV Generation MEMORY SUCK > > So I've got a problem with generating large csv files.. it's a memory suck. > > I do this in an event gateway so that these file drops are generated > "in the background"... here's the gateway code: > > http://cfm.pastebin.org/40043 > > The larger the file drop, the worse the memory suck is. A relatively > small drop of about 7200 rows and 138 columns (just over 1 million > pieces of > data) > took 68 seconds. In my production environment, I've estimated that I > can generate between 15,000 and 20,000 pieces of data per second using > the code above. > > The problem is this drop (which only generates a 5MB file) causes a > memory suck of about 100MB... > > Take a look at this output from the server monitor: > www.it.dev.duke.edu/public/temp.rtf > > It shows the memory graph generated from two file drops, at 9:38 and > 9:45 am... the first one spiked the memory from 70MB to 170MB...the > second one dropped the memory back to about 90MB and then spiked it to 140MB. > > Of course, this size drop is not what causes my concern, it's when > people are dropping 10x that amount.. say 80,000 rows at 130 columns. > Over 10 million pieces of data, would take nearly 9 minutes ASSUMING > you had no memory issues, which I would. Such a drop would basically > crash the instance. > > -- > Rick Root > New Brian Vander Ark Album, songs in the music player and cool behind > the scenes video at www.myspace.com/brianvanderark > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306627 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
On Mon, Jun 2, 2008 at 1:44 PM, Gerald Guido <[EMAIL PROTECTED]> wrote: > >> dropped my time from 68 seconds to 18 seconds > > Nice. Is that entirety of the code sans the query? Entirety. The query itself takes about 4 seconds to execute and return all its data. > >>> little java class library called JavaCSV that handles > > The the one from SourceForge? I am going to need something like this > shortly. > Yeah, that's the one. Rick ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306621 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CSV Generation MEMORY SUCK
>> dropped my time from 68 seconds to 18 seconds Nice. Is that entirety of the code sans the query? >>> little java class library called JavaCSV that handles The the one from SourceForge? I am going to need something like this shortly. G -- "The important thing in science is not so much to obtain new facts as to discover new ways of thinking about them." - Sir William Bragg ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306608 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CSV Generation MEMORY SUCK
I found a nice little java class library called JavaCSV that handles all the file writing and dropped my time from 68 seconds to 18 seconds. That has potential! It basically handles the writing of delimiters and the proper csv formatting.. so here's my code: ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306602 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
On Mon, Jun 2, 2008 at 12:34 PM, Brian Kotek <[EMAIL PROTECTED]> wrote: > The difference is that you have to use the StringBuffer for everything. > Since you aren't passing the StringBuffer into the CSVFormat method and I > don't see the code for that method, I assume it is still suffering from the > creation of large numbers of String instances. Try passing the StringBuffer > into CSVFormat and use it within the method to append the data. Now *THAT* I hadn't though of. Lemme give that a whirl. Rick ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306594 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
No, I opened it and saw that it was 400 lines long and didn't have time to go through it all. But sweeping through it quickly, the same advice applies. The difference is that you have to use the StringBuffer for everything. Since you aren't passing the StringBuffer into the CSVFormat method and I don't see the code for that method, I assume it is still suffering from the creation of large numbers of String instances. Try passing the StringBuffer into CSVFormat and use it within the method to append the data. On Mon, Jun 2, 2008 at 12:25 PM, Rick Root <[EMAIL PROTECTED]> wrote: > Didn't look at the code, eh? > > On Mon, Jun 2, 2008 at 12:07 PM, Brian Kotek <[EMAIL PROTECTED]> wrote: > > > Use a Java StringBuffer or StringBuilder. Concatenating large strings in > CF > > > > > > On Mon, Jun 2, 2008 at 10:03 AM, Rick Root <[EMAIL PROTECTED]> > > wrote: > > > > > > > > http://cfm.pastebin.org/40043 > > > > > > > > > -- > > Rick Root > > New Brian Vander Ark Album, songs in the music player and cool behind the > > scenes video at www.myspace.com/brianvanderark > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306592 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
On Mon, Jun 2, 2008 at 11:42 AM, Tom Chiverton <[EMAIL PROTECTED]> wrote: > On Monday 02 Jun 2008, Rick Root wrote: > > generating the csv (around line 330-340 of the sample code I posted > > earlier) took 62 of the 68 seconds. > > Why not output the file all at once, rather than a line at a time (scrap > lines > ~336 - just keeping .append()'ing to a StringBuffer till your done) ? > > I could do that but as I mentioned, the file writing is not the problem. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306591 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CSV Generation MEMORY SUCK
On Mon, Jun 2, 2008 at 11:40 AM, Gerald Guido <[EMAIL PROTECTED]> wrote: > >> "#Chr(34)##replace(arguments > > > > .str,chr(34),"#chr(34)##chr(34)#","ALL")##Chr(34)#" > > > There is your bottle neck. CF does not like string manipulation on a large > scale. I have tried to parsed large text files before only to watched my > dev > box just keel over. > This is only being done on a per field basis, so it's not a manipulation being done on a large scale. At least not on a large string. It's being done on the individual fields. I suspect that the largest string of data being dealt with by the csvFormat() function is 50 characters. -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306589 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CSV Generation MEMORY SUCK
Didn't look at the code, eh? On Mon, Jun 2, 2008 at 12:07 PM, Brian Kotek <[EMAIL PROTECTED]> wrote: > Use a Java StringBuffer or StringBuilder. Concatenating large strings in CF > > > On Mon, Jun 2, 2008 at 10:03 AM, Rick Root <[EMAIL PROTECTED]> > wrote: > > > > > http://cfm.pastebin.org/40043 > > > > > -- > Rick Root > New Brian Vander Ark Album, songs in the music player and cool behind the > scenes video at www.myspace.com/brianvanderark > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306587 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
Use a Java StringBuffer or StringBuilder. Concatenating large strings in CF is always a memory hog because every single concatenation creates a new String instance. Check RIAForge, there are CFC libraries that wrap using these Java classes for exactly this purpose. You'll find memory usage drops dramatically. On Mon, Jun 2, 2008 at 10:03 AM, Rick Root <[EMAIL PROTECTED]> wrote: > So I've got a problem with generating large csv files.. it's a memory suck. > > I do this in an event gateway so that these file drops are generated "in > the > background"... here's the gateway code: > > http://cfm.pastebin.org/40043 > > The larger the file drop, the worse the memory suck is. A relatively small > drop of about 7200 rows and 138 columns (just over 1 million pieces of > data) > took 68 seconds. In my production environment, I've estimated that I can > generate between 15,000 and 20,000 pieces of data per second using the code > above. > > The problem is this drop (which only generates a 5MB file) causes a memory > suck of about 100MB... > > Take a look at this output from the server monitor: > www.it.dev.duke.edu/public/temp.rtf > > It shows the memory graph generated from two file drops, at 9:38 and 9:45 > am... the first one spiked the memory from 70MB to 170MB...the second one > dropped the memory back to about 90MB and then spiked it to 140MB. > > Of course, this size drop is not what causes my concern, it's when people > are dropping 10x that amount.. say 80,000 rows at 130 columns. Over 10 > million pieces of data, would take nearly 9 minutes ASSUMING you had no > memory issues, which I would. Such a drop would basically crash the > instance. > > -- > Rick Root > New Brian Vander Ark Album, songs in the music player and cool behind the > scenes video at www.myspace.com/brianvanderark > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306583 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CSV Generation MEMORY SUCK
On Monday 02 Jun 2008, Rick Root wrote: > generating the csv (around line 330-340 of the sample code I posted > earlier) took 62 of the 68 seconds. Why not output the file all at once, rather than a line at a time (scrap lines ~336 - just keeping .append()'ing to a StringBuffer till your done) ? Also, have you benchmarked the EnquireLookupCFC (line 333) ? What does that do ? -- Tom Chiverton This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306580 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
>> "#Chr(34)##replace(arguments > > .str,chr(34),"#chr(34)##chr(34)#","ALL")##Chr(34)#" There is your bottle neck. CF does not like string manipulation on a large scale. I have tried to parsed large text files before only to watched my dev box just keel over. I see two options off the top of my head, let SQL server do the work or use Java for the string manipulation. Last time I had to parse a large text file like this I ended up writting an ActiveX script for DTS (long time ago) . G On Mon, Jun 2, 2008 at 11:19 AM, Rick Root <[EMAIL PROTECTED]> wrote: > On Mon, Jun 2, 2008 at 10:50 AM, Gaulin, Mark <[EMAIL PROTECTED]> > wrote: > > > > > Also, test you page with the user's query but with the output part > > (actually writing the file) commented out... If the page is still slow > > and a huge memory hog then the file stuff above won't help much and > > you'll have to look at running the query in java too, but I but you'll > > get something by handling the file better. > > > > I honestly don't think it's the file writing that's the problem. > > I just commented out the fileWrite() statements inside the tags > that would write each line to the file, (the file still being opened with > the "header" row being written to it... and it made zero difference at all > in the length of time it took to complete. > > The query itself runs quite fast. Returns a lot of rows but isn't a > complex > query. > > Anyway, I put some cfoutput statements in my gateway (I'm calling it as a > direct cfc call not a gateway for testing).. output now().gettime() to see > the ms as the method call progresses. > > The query returns its results in less than 4 seconds. The process of > generating the csv (around line 330-340 of the sample code I posted > earlier) > took 62 of the 68 seconds. > > And that was without actually WRITING the file. > > the GOOD news is that a TAB delimited file takes considerably less time (32 > seconds vs. 68 seconds), cutting the "output time" from 62 seconds to 26 > seconds. Which means the csvFormat() function is taking up a very large > part of the processing time. > > This is my csvFormat() function: > > returnType="string"> > > > > "#Chr(34)##replace(arguments.str,chr(34),"#chr(34)##chr(34)#","ALL")##Chr(34)#"> > > > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306579 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
On Mon, Jun 2, 2008 at 10:50 AM, Gaulin, Mark <[EMAIL PROTECTED]> wrote: > > Also, test you page with the user's query but with the output part > (actually writing the file) commented out... If the page is still slow > and a huge memory hog then the file stuff above won't help much and > you'll have to look at running the query in java too, but I but you'll > get something by handling the file better. > I honestly don't think it's the file writing that's the problem. I just commented out the fileWrite() statements inside the tags that would write each line to the file, (the file still being opened with the "header" row being written to it... and it made zero difference at all in the length of time it took to complete. The query itself runs quite fast. Returns a lot of rows but isn't a complex query. Anyway, I put some cfoutput statements in my gateway (I'm calling it as a direct cfc call not a gateway for testing).. output now().gettime() to see the ms as the method call progresses. The query returns its results in less than 4 seconds. The process of generating the csv (around line 330-340 of the sample code I posted earlier) took 62 of the 68 seconds. And that was without actually WRITING the file. the GOOD news is that a TAB delimited file takes considerably less time (32 seconds vs. 68 seconds), cutting the "output time" from 62 seconds to 26 seconds. Which means the csvFormat() function is taking up a very large part of the processing time. This is my csvFormat() function: ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306576 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CSV Generation MEMORY SUCK
We had a similar issue with an internal application and we made good
improvements using the standard java classes PrintStream,
FileOutputStream, and BufferedOutputStream to handle the writing to your
file.
Something like this (shown in java, so you'd have to wrap it properly
with cfscript):
PrintStream out = new PrintStream(new BufferedOutputStream(new
FileOutputStream("filename", true), bufferSize)); // pick a
decent-sized buffer.. Maybe 100k to start
This will let you do "out.print(...)" and "out.println(...)" from CF
that I think will be much more efficient than what CF can do. Be sure
to do "out.close()" within the page (so use try/catch to be sure that
the close happens).
Also, test you page with the user's query but with the output part
(actually writing the file) commented out... If the page is still slow
and a huge memory hog then the file stuff above won't help much and
you'll have to look at running the query in java too, but I but you'll
get something by handling the file better.
Thanks
Mark
-Original Message-
From: Rick Root [mailto:[EMAIL PROTECTED]
Sent: Monday, June 02, 2008 10:33 AM
To: CF-Talk
Subject: Re: CSV Generation MEMORY SUCK
SQL Server 2005.
I'm open to suggestion. This is part of an application that allows
users to generate CSV files of their own based on their own criteria, so
though I'm open to "non-CF" solutions, I'm not sure there really would
be anyway except maybe a homegrown java class to handle the work and be
more strict with memory consumption
Rick
On Mon, Jun 2, 2008 at 10:13 AM, Mark Kruger <[EMAIL PROTECTED]>
wrote:
> Rick,
>
> What's your DB platform? Are you sure there is not a better "non-cf"
> way to do it?
>
>
> Mark A. Kruger, CFG, MCSE
> (402) 408-3733 ext 105
> www.cfwebtools.com
> www.coldfusionmuse.com
> www.necfug.com
>
> -Original Message-
> From: Rick Root [mailto:[EMAIL PROTECTED]
> Sent: Monday, June 02, 2008 9:04 AM
> To: CF-Talk
> Subject: CSV Generation MEMORY SUCK
>
> So I've got a problem with generating large csv files.. it's a memory
suck.
>
> I do this in an event gateway so that these file drops are generated
> "in the background"... here's the gateway code:
>
> http://cfm.pastebin.org/40043
>
> The larger the file drop, the worse the memory suck is. A relatively
> small drop of about 7200 rows and 138 columns (just over 1 million
> pieces of
> data)
> took 68 seconds. In my production environment, I've estimated that I
> can generate between 15,000 and 20,000 pieces of data per second using
> the code above.
>
> The problem is this drop (which only generates a 5MB file) causes a
> memory suck of about 100MB...
>
> Take a look at this output from the server monitor:
> www.it.dev.duke.edu/public/temp.rtf
>
> It shows the memory graph generated from two file drops, at 9:38 and
> 9:45 am... the first one spiked the memory from 70MB to 170MB...the
> second one dropped the memory back to about 90MB and then spiked it to
140MB.
>
> Of course, this size drop is not what causes my concern, it's when
> people are dropping 10x that amount.. say 80,000 rows at 130 columns.
> Over 10 million pieces of data, would take nearly 9 minutes ASSUMING
> you had no memory issues, which I would. Such a drop would basically
> crash the instance.
>
> --
> Rick Root
> New Brian Vander Ark Album, songs in the music player and cool behind
> the scenes video at www.myspace.com/brianvanderark
>
>
>
>
>
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306570
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
I know when I had to do this at a previous job I used ArrayAppend to build each line in the CSV, but I see you are using the string buffer. I had no performance diffs at the time, so I just stayed with the CF solution. The one thing I would look at is not using list functions, but instead using Array functions and then one ArrayToList at the end. Also, make sure the queries being executed aren't intensive either. I found in our CSV generation, for every second the query took, it took one second on output so my resources were essentially 50/50 between query and output, Phil On Mon, Jun 2, 2008 at 10:33 AM, Rick Root <[EMAIL PROTECTED]> wrote: > SQL Server 2005. > > I'm open to suggestion. This is part of an application that allows users > to > generate CSV files of their own based on their own criteria, so though I'm > open to "non-CF" solutions, I'm not sure there really would be anyway > except > maybe a homegrown java class to handle the work and be more strict with > memory consumption > > Rick > > On Mon, Jun 2, 2008 at 10:13 AM, Mark Kruger <[EMAIL PROTECTED]> > wrote: > > > Rick, > > > > What's your DB platform? Are you sure there is not a better "non-cf" way > > to > > do it? > > > > > > Mark A. Kruger, CFG, MCSE > > (402) 408-3733 ext 105 > > www.cfwebtools.com > > www.coldfusionmuse.com > > www.necfug.com > > > > -Original Message- > > From: Rick Root [mailto:[EMAIL PROTECTED] > > Sent: Monday, June 02, 2008 9:04 AM > > To: CF-Talk > > Subject: CSV Generation MEMORY SUCK > > > > So I've got a problem with generating large csv files.. it's a memory > suck. > > > > I do this in an event gateway so that these file drops are generated "in > > the > > background"... here's the gateway code: > > > > http://cfm.pastebin.org/40043 > > > > The larger the file drop, the worse the memory suck is. A relatively > small > > drop of about 7200 rows and 138 columns (just over 1 million pieces of > > data) > > took 68 seconds. In my production environment, I've estimated that I can > > generate between 15,000 and 20,000 pieces of data per second using the > code > > above. > > > > The problem is this drop (which only generates a 5MB file) causes a > memory > > suck of about 100MB... > > > > Take a look at this output from the server monitor: > > www.it.dev.duke.edu/public/temp.rtf > > > > It shows the memory graph generated from two file drops, at 9:38 and 9:45 > > am... the first one spiked the memory from 70MB to 170MB...the second one > > dropped the memory back to about 90MB and then spiked it to 140MB. > > > > Of course, this size drop is not what causes my concern, it's when people > > are dropping 10x that amount.. say 80,000 rows at 130 columns. Over 10 > > million pieces of data, would take nearly 9 minutes ASSUMING you had no > > memory issues, which I would. Such a drop would basically crash the > > instance. > > > > -- > > Rick Root > > New Brian Vander Ark Album, songs in the music player and cool behind the > > scenes video at www.myspace.com/brianvanderark > > > > > > > > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306569 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Generation MEMORY SUCK
SQL Server 2005. I'm open to suggestion. This is part of an application that allows users to generate CSV files of their own based on their own criteria, so though I'm open to "non-CF" solutions, I'm not sure there really would be anyway except maybe a homegrown java class to handle the work and be more strict with memory consumption Rick On Mon, Jun 2, 2008 at 10:13 AM, Mark Kruger <[EMAIL PROTECTED]> wrote: > Rick, > > What's your DB platform? Are you sure there is not a better "non-cf" way > to > do it? > > > Mark A. Kruger, CFG, MCSE > (402) 408-3733 ext 105 > www.cfwebtools.com > www.coldfusionmuse.com > www.necfug.com > > -Original Message- > From: Rick Root [mailto:[EMAIL PROTECTED] > Sent: Monday, June 02, 2008 9:04 AM > To: CF-Talk > Subject: CSV Generation MEMORY SUCK > > So I've got a problem with generating large csv files.. it's a memory suck. > > I do this in an event gateway so that these file drops are generated "in > the > background"... here's the gateway code: > > http://cfm.pastebin.org/40043 > > The larger the file drop, the worse the memory suck is. A relatively small > drop of about 7200 rows and 138 columns (just over 1 million pieces of > data) > took 68 seconds. In my production environment, I've estimated that I can > generate between 15,000 and 20,000 pieces of data per second using the code > above. > > The problem is this drop (which only generates a 5MB file) causes a memory > suck of about 100MB... > > Take a look at this output from the server monitor: > www.it.dev.duke.edu/public/temp.rtf > > It shows the memory graph generated from two file drops, at 9:38 and 9:45 > am... the first one spiked the memory from 70MB to 170MB...the second one > dropped the memory back to about 90MB and then spiked it to 140MB. > > Of course, this size drop is not what causes my concern, it's when people > are dropping 10x that amount.. say 80,000 rows at 130 columns. Over 10 > million pieces of data, would take nearly 9 minutes ASSUMING you had no > memory issues, which I would. Such a drop would basically crash the > instance. > > -- > Rick Root > New Brian Vander Ark Album, songs in the music player and cool behind the > scenes video at www.myspace.com/brianvanderark > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306567 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CSV Generation MEMORY SUCK
Rick, What's your DB platform? Are you sure there is not a better "non-cf" way to do it? Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Monday, June 02, 2008 9:04 AM To: CF-Talk Subject: CSV Generation MEMORY SUCK So I've got a problem with generating large csv files.. it's a memory suck. I do this in an event gateway so that these file drops are generated "in the background"... here's the gateway code: http://cfm.pastebin.org/40043 The larger the file drop, the worse the memory suck is. A relatively small drop of about 7200 rows and 138 columns (just over 1 million pieces of data) took 68 seconds. In my production environment, I've estimated that I can generate between 15,000 and 20,000 pieces of data per second using the code above. The problem is this drop (which only generates a 5MB file) causes a memory suck of about 100MB... Take a look at this output from the server monitor: www.it.dev.duke.edu/public/temp.rtf It shows the memory graph generated from two file drops, at 9:38 and 9:45 am... the first one spiked the memory from 70MB to 170MB...the second one dropped the memory back to about 90MB and then spiked it to 140MB. Of course, this size drop is not what causes my concern, it's when people are dropping 10x that amount.. say 80,000 rows at 130 columns. Over 10 million pieces of data, would take nearly 9 minutes ASSUMING you had no memory issues, which I would. Such a drop would basically crash the instance. -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306563 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CSV and SQL
That might do it - I've never done it but I believe you can tell SQL Server to return an XML file so it might be possible - I'll do some more research but thought someone on the list might know the answer off the top of their head. Thanks for the tip ++ Kevin Parker Web Services Consultant WorkCover Corporation p: 08 8233 2548 m: 0418 806 166 e: [EMAIL PROTECTED] w: www.workcover.com ++ -Original Message- From: Larry White [mailto:[EMAIL PROTECTED] Sent: Wednesday, 29 June 2005 4:06 AM To: CF-Talk Subject: CSV and SQL You could do something like Select Firstname + ',' + Lastname + Char(13) as OneLine Then loop thru the query and concantenate the resultset >Is it possible to structure your SQL query (target SQL Server) so that ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:210834 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CSV file and com.Ostermiller.util.ExcelCSVParser
Yes, I know and it works except if the empty fields are at the of the
line and then not all the time. You can try it yourself. Export your
contacts from outlook to a csv file and try to import it using the
code that I have posted. You will see that it will complain that
certain lines (array returned) have less than 92 elements (the number
of fields exported by outlook).
I got around it by putting a number of try catch error trapping when importing
Thanks
Victor
PS Maybe I will try to
On Apr 6, 2005 11:02 PM, Jon Gunnip <[EMAIL PROTECTED]> wrote:
> Victor,
>
> The docs at
> http://ostermiller.org/utils/doc/com/Ostermiller/util/ExcelCSVParser.html
> explicitly state that the parser should work as you expect:
>
>
> Empty fields are returned as as String of length zero: "". The
> following line has three empty fields and three non-empty fields in
> it. There is an empty field on each end, and one in the middle. One
> token is returned as a space.
>
> ,second,, ,fifth,
>
>
> A few things I might try if I were you:
> 1) try and reproduce the problem with as simple an example as possible
> (e.g. Parser.parse("a,,b") )
> 2) write some simple java to try to reporoduce a simple example
> 3) if both of the above still reproduce the broblem, you could ask mr.
> ostermiller to fix the problem and maybe you can workaround it by
> using a regex on the empty string to add a "rare" string to empty
> cells prior to parsing and then remove it after parsing
> 4) if you can't reproduce the problem with a simple string, keep
> adding to it until it looks more like your CSV and fails.
>
> Good luck. Sorry I couldn't be of more help. I'd be interested to
> hear what you find.
> Jon
>
> On Apr 4, 2005 6:10 PM, Victor Moore <[EMAIL PROTECTED]> wrote:
> > Hi Jon,
> > I'm happy with this utility , except this little problem.
> > Bellow is the code:
> >
> >
> > parser = createObject("java", "com.Ostermiller.util.ExcelCSVParser") ;
> > fileInfo = parser.parse(fileContents) ;
> > noFields = arrayLen (fileInfo);
> > for (i = 2; i lte noFields; i = i + 1)
> > {
> > lineArray = fileInfo [i];
> > initSize = arrayLen (lineArray);
> >if (initSize lt 30)
> > {
> > msg = "Error...;
> > }
> > }
> >
> >
> > I know that there are 30 fields in the file. If I open the file in
> > Excel I can see all of them there, but some of them have no value and
> > this is why the checking bombs.
> > If I run a replace all in excel and replace the empty fields with a
> > space everything is OK and the file parsing works as expected.
> >
> >
>
>
~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201962
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CSV file and com.Ostermiller.util.ExcelCSVParser
Victor,
The docs at
http://ostermiller.org/utils/doc/com/Ostermiller/util/ExcelCSVParser.html
explicitly state that the parser should work as you expect:
Empty fields are returned as as String of length zero: "". The
following line has three empty fields and three non-empty fields in
it. There is an empty field on each end, and one in the middle. One
token is returned as a space.
,second,, ,fifth,
A few things I might try if I were you:
1) try and reproduce the problem with as simple an example as possible
(e.g. Parser.parse("a,,b") )
2) write some simple java to try to reporoduce a simple example
3) if both of the above still reproduce the broblem, you could ask mr.
ostermiller to fix the problem and maybe you can workaround it by
using a regex on the empty string to add a "rare" string to empty
cells prior to parsing and then remove it after parsing
4) if you can't reproduce the problem with a simple string, keep
adding to it until it looks more like your CSV and fails.
Good luck. Sorry I couldn't be of more help. I'd be interested to
hear what you find.
Jon
On Apr 4, 2005 6:10 PM, Victor Moore <[EMAIL PROTECTED]> wrote:
> Hi Jon,
> I'm happy with this utility , except this little problem.
> Bellow is the code:
>
>
> parser = createObject("java", "com.Ostermiller.util.ExcelCSVParser") ;
> fileInfo = parser.parse(fileContents) ;
> noFields = arrayLen (fileInfo);
> for (i = 2; i lte noFields; i = i + 1)
> {
> lineArray = fileInfo [i];
> initSize = arrayLen (lineArray);
>if (initSize lt 30)
> {
> msg = "Error...;
> }
> }
>
>
> I know that there are 30 fields in the file. If I open the file in
> Excel I can see all of them there, but some of them have no value and
> this is why the checking bombs.
> If I run a replace all in excel and replace the empty fields with a
> space everything is OK and the file parsing works as expected.
>
>
~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201816
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CSV file and com.Ostermiller.util.ExcelCSVParser
Hi Jon,
I'm happy with this utility , except this little problem.
Bellow is the code:
parser = createObject("java", "com.Ostermiller.util.ExcelCSVParser") ;
fileInfo = parser.parse(fileContents) ;
noFields = arrayLen (fileInfo);
for (i = 2; i lte noFields; i = i + 1)
{
lineArray = fileInfo [i];
initSize = arrayLen (lineArray);
if (initSize lt 30)
{
msg = "Error...;
}
}
I know that there are 30 fields in the file. If I open the file in
Excel I can see all of them there, but some of them have no value and
this is why the checking bombs.
If I run a replace all in excel and replace the empty fields with a
space everything is OK and the file parsing works as expected.
~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201457
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CSV file and com.Ostermiller.util.ExcelCSVParser
Victor, I'm using the ostermiller CSVWriter, and I am very happy with it. I'm having trouble understanding exactly what your problem is. Can you share some relevant code? Jon On Apr 4, 2005 12:58 PM, Victor Moore <[EMAIL PROTECTED]> wrote: > Actually it's a little bit more complicated than this. > The com.Ostermiller.util.ExcelCSVParser utility returns a Java array > which is has a Vector type and not the coldfusion array type. > Unfortunately (and something that I cannot explain) some lines that > have empty fields are not pick up. > I need something to dynamically resize the java array (the java array > doesn't have a resize feature to force ) or cast it to a cf array > type. > > thanks > Victor > > > On Apr 4, 2005 10:36 AM, Kerry <[EMAIL PROTECTED]> wrote: > > this will probably be because CF ignores empty list elements. > > there might be some kind of a split function on cflib.org > > > > > > -Original Message- > > From: Victor Moore [mailto:[EMAIL PROTECTED] > > Sent: 04 April 2005 15:14 > > To: CF-Talk > > Subject: CSV file and com.Ostermiller.util.ExcelCSVParser > > > > I am using com.Ostermiller.util.ExcelCSVParser with great succes to > > parse csv files. There is only one small problem that I can't figure > > out.If there are any empty columns in the file they are not picked up > > and as a result the parsing process is failing. > > > > The work around is to open the file in excel and run a replace all > > (for empty string with a space). Unfortunately this is used by end > > users and for some reason they don't do it ... > > > > Is any way to do it programmatically? > > > > Thanks > > Victor > > > > > > ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201450 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CSV file and com.Ostermiller.util.ExcelCSVParser
Actually it's a little bit more complicated than this. The com.Ostermiller.util.ExcelCSVParser utility returns a Java array which is has a Vector type and not the coldfusion array type. Unfortunately (and something that I cannot explain) some lines that have empty fields are not pick up. I need something to dynamically resize the java array (the java array doesn't have a resize feature to force ) or cast it to a cf array type. thanks Victor On Apr 4, 2005 10:36 AM, Kerry <[EMAIL PROTECTED]> wrote: > this will probably be because CF ignores empty list elements. > there might be some kind of a split function on cflib.org > > > -Original Message- > From: Victor Moore [mailto:[EMAIL PROTECTED] > Sent: 04 April 2005 15:14 > To: CF-Talk > Subject: CSV file and com.Ostermiller.util.ExcelCSVParser > > I am using com.Ostermiller.util.ExcelCSVParser with great succes to > parse csv files. There is only one small problem that I can't figure > out.If there are any empty columns in the file they are not picked up > and as a result the parsing process is failing. > > The work around is to open the file in excel and run a replace all > (for empty string with a space). Unfortunately this is used by end > users and for some reason they don't do it ... > > Is any way to do it programmatically? > > Thanks > Victor > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201417 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: CSV file and com.Ostermiller.util.ExcelCSVParser
this will probably be because CF ignores empty list elements. there might be some kind of a split function on cflib.org -Original Message- From: Victor Moore [mailto:[EMAIL PROTECTED] Sent: 04 April 2005 15:14 To: CF-Talk Subject: CSV file and com.Ostermiller.util.ExcelCSVParser I am using com.Ostermiller.util.ExcelCSVParser with great succes to parse csv files. There is only one small problem that I can't figure out.If there are any empty columns in the file they are not picked up and as a result the parsing process is failing. The work around is to open the file in excel and run a replace all (for empty string with a space). Unfortunately this is used by end users and for some reason they don't do it ... Is any way to do it programmatically? Thanks Victor ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201351 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CSV import into DB
I just got done finishing a project that waits around at 15-minute intervals and pulls in 10k+ line csv files for import into a cf db I used Paul Vernon's CFX_pop3 to speed up the processing (*dramatically*) of the 4-5mb file attachments, and Ryan Emerle's new java cfx_text2query to read in the data. Another huge jump forward in speed. Then I did the query loop you describe, but the time that step takes up is negligible compared to the cffile read and interpretation. I found that loop took up only a small fraction of my total job time when running in about 16000 records from a 4.7mb csv file. DTS would have been nice but it wasn't a sql server job. -- --Matt Robertson-- President, Janitor MSB Designs, Inc. mysecretbase.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CSV import into DB
Sorry, Here is the Code as I attached it before.
cellpadding="0" STYLE="Border-Color: 00; Border-Style: solid;
Border-Width: 0px;">
Select File to Upload
method="post" name="UploadForm" enctype="multipart/form-data">
maxlength="100">
#GetDirectoryFromPath(GetCurrentTemplatePath())#>
destination="#variables.path#" nameconflict="OVERWRITE">
file="#variables.path#\#cffile.ClientFileName#.#cffile.ClientFileExt#"
variable="variables.FileContent">
Form.Delims)#>
#Replace(variables.FileContent, ",,", ", ,", "all")#>
#Replace(variables.FileContent, ",,", ", ,", "all")#>
#ListToArray(variables.FileContent, Form.Delims)#>
#ArrayLen(variables.FileContentLines)#>
#ListToArray(variables.FieldNameList, ",")#>
to="#variables.FieldQnty#" step="1">
Contains "Corp">
Contains "SCHLNumber">
Contains "SCHLName">
Contains "LADDRESS">
Contains "LCITY">
Contains "STATE">
Contains "LZIP">
Contains "PHONE">
Contains "FAX">
to="#variables.FileContentLength#" step="1">
#ListToArray(FileContentLines[variables.FileProcess], ",")#>
LEN(Variables.ColData1) - 2)#>
LEN(Variables.ColData2) - 2)#>
LEN(Variables.ColData3) - 2)#>
LEN(Variables.ColData4) - 2)#>
LEN(Variables.ColData5) - 2)#>
LEN(Variables.ColData6) - 2)#>
LEN(Variables.ColData7) - 2)#>
LEN(Variables.ColData8) - 2)#>
LEN(Variables.ColData9) - 2)#>
Insert into schoolbuildings(CorpNumber,
SchoolNumber, SchoolName, SchoolAddress, SchoolCity, SchoolState,
SchoolZipCode, SchoolPhone, SchoolFax)
Values('#variables.COLData1#',
'#variables.COLData2#', '#variables.COLData3#', '#variables.COLData4#',
'#variables.COLData5#', '#variables.COLData6#', '#variables.COLData7#',
'#variables.COLData8#', '#variables.COLData9#')
At 07:12 AM 10/15/2004, you wrote:
>Here is how I do it through Coldfusion and have not had any problems with
>it. Basically I ask the user to sleect their file through a Form Object,
>and The CFFile to Upload this file and then to read the file uploaded. Next
>I create Objects and Reach each line into an Array. I then have a section
>to read the first line of the array and set variables for the specific
>header fields that I want to import. Last I loop over the Length of the
>array and Insert it into the datasource/table that I need it to go into.
>
>At 06:33 AM 10/15/2004, you wrote:
> >My question exactly! Especially since the DB isnt on the same server
> >(as they generally shouldnt be!)
> >
> >MD
> >
> >--
> >[Todays Threads]
> >[This Message]
> >[Subscription]
> >[
> Fast
> >Unsubscribe] [User Settings]
> >[
> &business=donations%40houseoffusion.com&undefined_quantity=&cmd=_xclick>Donations
>
> >and Support]
> >
> >--
> >
> >[]
> >
>
>--
>[]
>
>--
>[Todays Threads]
>[This Message]
>[Subscription]
>[Fast
>Unsubscribe] [User Settings]
>[Donations
>and Support]
>
>--
>
>[]
>
--
[]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: CSV import into DB
You can use xp_cmdshell from the SQL server, or you can also load DTSRun.exe onto the web server(I can't remember how, but it is not difficult to find out how). DTSRun.exe can reside on any server, and access any other SQL server that allows remote connections. I have written DTS packages that reside on my web server, and pull information from a SQL server at a different location, and then insert that data into another database on a third server. That's one of the wonderful things about DTS! On Fri, 15 Oct 2004 13:33:18 +0200, Mark Drew <[EMAIL PROTECTED]> wrote: > My question exactly! Especially since the DB isnt on the same server > (as they generally shouldnt be!) > > MD > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CSV import into DB
Here is how I do it through Coldfusion and have not had any problems with it. Basically I ask the user to sleect their file through a Form Object, and The CFFile to Upload this file and then to read the file uploaded. Next I create Objects and Reach each line into an Array. I then have a section to read the first line of the array and set variables for the specific header fields that I want to import. Last I loop over the Length of the array and Insert it into the datasource/table that I need it to go into. At 06:33 AM 10/15/2004, you wrote: >My question exactly! Especially since the DB isnt on the same server >(as they generally shouldnt be!) > >MD > >-- >[Todays Threads] >[This Message] >[Subscription] >[Fast >Unsubscribe] [User Settings] >[Donations >and Support] > >-- > >[] > -- [] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: CSV import into DB
You can run a DTS via T-SQL using dtsrun and master..xp_cmdshell. What we do is copy the file from the server uploading to the SQL box, then run the DTS against that filethe file can be of any name as we use global variables and some VB inside of the DTS to get the connection properties etc... _ From: Mark Drew [mailto:[EMAIL PROTECTED] Sent: 15 October 2004 12:33 To: CF-Talk Subject: Re: CSV import into DB My question exactly! Especially since the DB isnt on the same server (as they generally shouldnt be!) MD _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: CSV import into DB
| From: Scott Stroz [mailto:[EMAIL PROTECTED] | You can use to run DTSRun.exe. Is it possible to run it through t-sql? ### This message has been scanned by F-Secure Anti-Virus for Microsoft Exchange. For more information, connect to http://www.F-Secure.com/ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CSV import into DB
My question exactly! Especially since the DB isnt on the same server (as they generally shouldnt be!) MD [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CSV import into DB
You can use to run DTSRun.exe. If you search the SQL server Help, you can find out more info on the syntax to use DTSRun from command line. On Fri, 15 Oct 2004 13:26:50 +0200, Hugo Ahlenius <[EMAIL PROTECTED]> wrote: > How does one execute a DTS package from CF? > > -- > Hugo Ahlenius > > - > Hugo Ahlenius E-Mail: [EMAIL PROTECTED] > Project Officer Phone:+46 8 230460 > UNEP GRID-Arendal Fax: +46 8 230441 > Stockholm Office Mobile: +46 733 467111 > WWW: http://www.grida.no > - > > > > | -Original Message- > | From: Robertson-Ravo, Neil (RX) > | [mailto:[EMAIL PROTECTED] > | Sent: Friday, October 15, 2004 13:14 > | To: CF-Talk > | Subject: RE: CSV import into DB > | > | DTS > > > | > | > | > | _ > | > | From: Mark Drew [mailto:[EMAIL PROTECTED] > | Sent: 15 October 2004 11:48 > | To: CF-Talk > | Subject: CSV import into DB > | > | > | > | I am doing an automated insert of a CSV file into a MS SQL database. > | I am sure this has been done a million times, so I was > | wondering if there is a better way that upload CSV, loop > | through each line and insert each line one at a time into the > | db. Is there a better batch import method? > | > | > | -- > | Mark Drew > | > | coldfusion and cfeclipse blogged: > | http://cybersonic.blogspot.com/ > | > | _ > | > | > | > | > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: CSV import into DB
How does one execute a DTS package from CF? -- Hugo Ahlenius - Hugo Ahlenius E-Mail: [EMAIL PROTECTED] Project Officer Phone:+46 8 230460 UNEP GRID-Arendal Fax: +46 8 230441 Stockholm Office Mobile: +46 733 467111 WWW: http://www.grida.no - | -Original Message- | From: Robertson-Ravo, Neil (RX) | [mailto:[EMAIL PROTECTED] | Sent: Friday, October 15, 2004 13:14 | To: CF-Talk | Subject: RE: CSV import into DB | | DTS | | | | _ | | From: Mark Drew [mailto:[EMAIL PROTECTED] | Sent: 15 October 2004 11:48 | To: CF-Talk | Subject: CSV import into DB | | | | I am doing an automated insert of a CSV file into a MS SQL database. | I am sure this has been done a million times, so I was | wondering if there is a better way that upload CSV, loop | through each line and insert each line one at a time into the | db. Is there a better batch import method? | | | -- | Mark Drew | | coldfusion and cfeclipse blogged: | http://cybersonic.blogspot.com/ | | _ | | | | [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: CSV import into DB
DTS _ From: Mark Drew [mailto:[EMAIL PROTECTED] Sent: 15 October 2004 11:48 To: CF-Talk Subject: CSV import into DB I am doing an automated insert of a CSV file into a MS SQL database. I am sure this has been done a million times, so I was wondering if there is a better way that upload CSV, loop through each line and insert each line one at a time into the db. Is there a better batch import method? -- Mark Drew coldfusion and cfeclipse blogged: http://cybersonic.blogspot.com/ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CSV import into DB
Thanks for that this will not be a one off but a way of populating a table remotely using CSV files via an upload. Seems to me that the SQL language is very limited in the INSERT INTO department. MD On Fri, 15 Oct 2004 12:01:15 +0100, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Mark > > If you have Enterprise manager (and this is a one off task) you can run the > dts wizard which allows you to > > Insert data into and from CSV, excels and numerous other file formats. > > HTH > > Kola > > _ > > From: Mark Drew [mailto:[EMAIL PROTECTED] > Sent: 15 October 2004 10:48 > To: CF-Talk > Subject: CSV import into DB > > > > > I am doing an automated insert of a CSV file into a MS SQL database. > I am sure this has been done a million times, so I was wondering if > there is a better way that upload CSV, loop through each line and > insert each line one at a time into the db. Is there a better batch > import method? > > -- > Mark Drew > > coldfusion and cfeclipse blogged: > http://cybersonic.blogspot.com/ > > _ > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: CSV import into DB
Mark If you have Enterprise manager (and this is a one off task) you can run the dts wizard which allows you to Insert data into and from CSV, excels and numerous other file formats. HTH Kola _ From: Mark Drew [mailto:[EMAIL PROTECTED] Sent: 15 October 2004 10:48 To: CF-Talk Subject: CSV import into DB I am doing an automated insert of a CSV file into a MS SQL database. I am sure this has been done a million times, so I was wondering if there is a better way that upload CSV, loop through each line and insert each line one at a time into the db. Is there a better batch import method? -- Mark Drew coldfusion and cfeclipse blogged: http://cybersonic.blogspot.com/ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: .csv import for CFHTTP but not CFFILE?
You could also try the QueryToCsv UDF. http://cflib.org/udf.cfm?ID=556 --- Ryan Emerle <[EMAIL PROTECTED]> wrote: > Check out: > http://www.emerle.net/programming/display.cfm/t/cfx_text2query > (it's free) > > "CFX_Text2Query works better than other methods because it can handle > empty fields and fields with line breaks.." > > "One alternative, using CFHTTP, requires the file to be > web-accessible, and cannot handle empty fields OR line breaks. This > often results in the "Incorrect number of columns in row." error and a > lot of headaches." > > > On Wed, 29 Sep 2004 18:56:47 -0400, Anders Green <[EMAIL PROTECTED]> > wrote: > > Interesting. Seems that CFHTTP can turn a .csv file > > into a query easily. But CFFILE can't. > > > > So I can pull the file from another server and handle > > it easily, but not when it's on my own machine? > > Weird. > > > > Ok, I guess I could copy the file to the web root, > > CFHTTP it from myself, then delete it, but that > > seems a little roundabout. > > > > Or, read it in, parse it, etcetera. Certainly doable, > > but not as oh-look-its-done-already easy. I have to > > do that anyway for a different file that's certainly > > NOT csv, and once would be just fine. :) > > > > Anyone want to jump in here? > > > > Cheers! > > Anders > > +===+ > > |Anders Green Email: [EMAIL PROTECTED] | > > | Home: 919.303.0218 | > > |Off Road Rally Racing Team: http://linaracing.com/ | > > +===+ > > > > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: .csv import for CFHTTP but not CFFILE?
Check out: http://www.emerle.net/programming/display.cfm/t/cfx_text2query (it's free) "CFX_Text2Query works better than other methods because it can handle empty fields and fields with line breaks.." "One alternative, using CFHTTP, requires the file to be web-accessible, and cannot handle empty fields OR line breaks. This often results in the "Incorrect number of columns in row." error and a lot of headaches." On Wed, 29 Sep 2004 18:56:47 -0400, Anders Green <[EMAIL PROTECTED]> wrote: > Interesting. Seems that CFHTTP can turn a .csv file > into a query easily. But CFFILE can't. > > So I can pull the file from another server and handle > it easily, but not when it's on my own machine? > Weird. > > Ok, I guess I could copy the file to the web root, > CFHTTP it from myself, then delete it, but that > seems a little roundabout. > > Or, read it in, parse it, etcetera. Certainly doable, > but not as oh-look-its-done-already easy. I have to > do that anyway for a different file that's certainly > NOT csv, and once would be just fine. :) > > Anyone want to jump in here? > > Cheers! > Anders > +===+ > |Anders Green Email: [EMAIL PROTECTED] | > | Home: 919.303.0218 | > |Off Road Rally Racing Team: http://linaracing.com/ | > +===+ > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: .csv import for CFHTTP but not CFFILE?
You're 100% correct that it's totally backwards and foolish that it's possible to only convert a CSV to a recordset from remote resources. It is totally unclear why MM hasn't exposed that as standalone functionality, but it's been that was since the dawn of time with no change. cheers, barneyb On Wed, 29 Sep 2004 18:56:47 -0400, Anders Green <[EMAIL PROTECTED]> wrote: > Interesting. Seems that CFHTTP can turn a .csv file > into a query easily. But CFFILE can't. > > So I can pull the file from another server and handle > it easily, but not when it's on my own machine? > Weird. > > Ok, I guess I could copy the file to the web root, > CFHTTP it from myself, then delete it, but that > seems a little roundabout. > > Or, read it in, parse it, etcetera. Certainly doable, > but not as oh-look-its-done-already easy. I have to > do that anyway for a different file that's certainly > NOT csv, and once would be just fine. :) > > Anyone want to jump in here? > > Cheers! > Anders -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com I currently have 4 GMail invites for the taking [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CSV to Query
Not to forget querySim: there's the original tag which you can get from http://www.halhelms.com/index.cfm?fuseaction=code.detail or the UDF version from http://www.cflib.org/udf.cfm?ID=255
Both do pretty much the same thing - take some formatted text and return a recordset.
As to which to use, its much of a muchness - I think the UDF is slightly faster until the amount of data being passed in reaches a certain size, after which performance deteriorates faster than the tag, but I haven't looked at either for years, so that would have been testing on CF5
Cheers
Bert
From: Barney Boisvert [mailto:[EMAIL PROTECTED]
Sent: 25 August 2004 17:14
To: CF-Talk
Subject: Re: CSV to Query
Here's a second vote for ostermiller's CSV parser. It's java, so it
requires a touch of Java knowlesge, but it's very easy to use, as
Marc's demo illustrates, and quite fast.
cheers,
barneyb
On Wed, 25 Aug 2004 10:20:29 -0400, Marc Campeau <[EMAIL PROTECTED]> wrote:
> > > > Anybody have a UDF for this? I need it sharpish, I can write it myself
> > > > but if someone else has done this.. whooho!
>
> I use a Java library (ExcelCSVParser) from www.Ostermiller.com. You
> can find some other great utils there too.
>
> This is the code I use to parse a CSV file... it creates an array not
> a query though.
>
>
>
> f = createObject( "java", "java.io.FileInputStream" );
> f.init("#REQUEST.DOCUMENT_DIRECTORY#\#File.ServerFile#");
>
> parser = createObject( "java", "com.Ostermiller.util.ExcelCSVParser" );
> parser.init( f );
> csvArr = parser.getAllValues();
> parser.close();
> f.close();
>
>
>
> --
--
Barney Boisvert
[EMAIL PROTECTED]
360.319.6145
http://www.barneyb.com
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: CSV to Query
Oops, forgot to send this! Dick That looks like a date time field. I never thought much about it, but I don't think that cfhttp can determine that the field is a datetime -- I think it simply creates a char field. So, you can't do a QofQ on a datetime value as only text comparisons are valid. A possible solution is to: 1) loop through the query 2) convert each text representation of date time into a valid datetime object 3) perform your comparison 4) build a new query with a row for each match HTH Dick > The file is something like > Part,PartDesc,OrderRef,Qty,orderdate > XX,A descriptoin of an Item,OrderRefno222,1,14/05/2004 15:20 > > you get the idea > > It is the date that kills me! > > MD > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CSV to Query
Here's a second vote for ostermiller's CSV parser. It's java, so it
requires a touch of Java knowlesge, but it's very easy to use, as
Marc's demo illustrates, and quite fast.
cheers,
barneyb
On Wed, 25 Aug 2004 10:20:29 -0400, Marc Campeau <[EMAIL PROTECTED]> wrote:
> > > > Anybody have a UDF for this? I need it sharpish, I can write it myself
> > > > but if someone else has done this.. whooho!
>
> I use a Java library (ExcelCSVParser) from www.Ostermiller.com. You
> can find some other great utils there too.
>
> This is the code I use to parse a CSV file... it creates an array not
> a query though.
>
>
>
> f = createObject( "java", "java.io.FileInputStream" );
> f.init("#REQUEST.DOCUMENT_DIRECTORY#\#File.ServerFile#");
>
> parser = createObject( "java", "com.Ostermiller.util.ExcelCSVParser" );
> parser.init( f );
> csvArr = parser.getAllValues();
> parser.close();
> f.close();
>
>
>
> --
--
Barney Boisvert
[EMAIL PROTECTED]
360.319.6145
http://www.barneyb.com
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: CSV to Query
The file is something like
Part,PartDesc,OrderRef,Qty,orderdate
XX,A descriptoin of an Item,OrderRefno222,1,14/05/2004 15:20
you get the idea
It is the date that kills me!
MD
On Wed, 25 Aug 2004 10:20:29 -0400, Marc Campeau <[EMAIL PROTECTED]> wrote:
> > > > Anybody have a UDF for this? I need it sharpish, I can write it myself
> > > > but if someone else has done this.. whooho!
>
> I use a Java library (ExcelCSVParser) from www.Ostermiller.com. You
> can find some other great utils there too.
>
> This is the code I use to parse a CSV file... it creates an array not
> a query though.
>
>
>
> f = createObject( "java", "java.io.FileInputStream" );
> f.init("#REQUEST.DOCUMENT_DIRECTORY#\#File.ServerFile#");
>
> parser = createObject( "java", "com.Ostermiller.util.ExcelCSVParser" );
> parser.init( f );
> csvArr = parser.getAllValues();
> parser.close();
> f.close();
>
>
>
> --
> Marc
>
>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: CSV to Query
> > > Anybody have a UDF for this? I need it sharpish, I can write it myself
> > > but if someone else has done this.. whooho!
I use a Java library (ExcelCSVParser) from www.Ostermiller.com. You
can find some other great utils there too.
This is the code I use to parse a CSV file... it creates an array not
a query though.
f = createObject( "java", "java.io.FileInputStream" );
f.init("#REQUEST.DOCUMENT_DIRECTORY#\#File.ServerFile#");
parser = createObject( "java", "com.Ostermiller.util.ExcelCSVParser" );
parser.init( f );
csvArr = parser.getAllValues();
parser.close();
f.close();
--
Marc
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: CSV to Query
Can we see a few lines of your CSV file? Dick On Aug 25, 2004, at 7:03 AM, Mark Drew wrote: > I have looked at this.. there is one small problem > > one of the columns is a date so I am not sure how dbtype= query > handles it but the results seem a bit odd > > > textqualifier=""> > > > SELECT * > FROM info > WHERE orderdate < > value="#DateFormat(Now(), "dd/mm/")#"> > > > On Wed, 25 Aug 2004 06:35:57 -0700, Dick Applebaum > <[EMAIL PROTECTED]> wrote: > > Have a look at cfhttp > > > > HTH > > > > Dick > > > > On Aug 25, 2004, at 5:56 AM, Mark Drew wrote: > > > > > Anybody have a UDF for this? I need it sharpish, I can write it > myself > > > but if someone else has done this.. whooho! [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CSV to Query
I have looked at this.. there is one small problem one of the columns is a date so I am not sure how dbtype= query handles it but the results seem a bit odd SELECT * FROM info WHERE orderdate < value="#DateFormat(Now(), "dd/mm/")#"> On Wed, 25 Aug 2004 06:35:57 -0700, Dick Applebaum <[EMAIL PROTECTED]> wrote: > Have a look at cfhttp > > HTH > > Dick > > On Aug 25, 2004, at 5:56 AM, Mark Drew wrote: > > > Anybody have a UDF for this? I need it sharpish, I can write it myself > > but if someone else has done this.. whooho! > > > > Much apreciated > > > > -- > > Mark Drew > > mailto:[EMAIL PROTECTED] > > blog:http://cybersonic.blogspot.com/ > > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CSV to Query
Have a look at cfhttp HTH Dick On Aug 25, 2004, at 5:56 AM, Mark Drew wrote: > Anybody have a UDF for this? I need it sharpish, I can write it myself > but if someone else has done this.. whooho! > > Much apreciated > > -- > Mark Drew > mailto:[EMAIL PROTECTED] > blog:http://cybersonic.blogspot.com/ > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: CSV to Query
I don't know if there's anything on cflib.org, but if you have access to DRK3, the DFA API does this (and a ton more). ~Simon Simon Horwith CTO, eTRILOGY ltd. Member of Team Macromedia Macromedia Certified Master Instructor Blog - http://www.horwith.com > > Anybody have a UDF for this? I need it sharpish, I can write it myself > but if someone else has done this.. whooho! > > Much apreciated > > -- > Mark Drew > mailto:[EMAIL PROTECTED] > blog:http://cybersonic.blogspot.com/ > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: CSV HELL
This function will parse a single line of Excel CSV if that's any use to
you.
"")>
reFind("""(([^""]|)*)""($|,)", line, pos, true)>
arrayAppend(arrLine, replace(mid(line, foundCell.pos[2], foundCell.len[2]),
"", , "all"))>
+ foundCell.len[1]>
false>
reFind("[^,]+", line, pos, true)>
arrayAppend(arrLine, mid(line, foundCell.pos[1], foundCell.len[1]))>
+ foundCell.len[1] + 1>
false>
_
From: Rich Ziade [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 23 June 2004 12:15 a.m.
To: CF-Talk
Subject: CSV HELL
Hi all:
I'm writing a little function that parses a CSV file into a query (I know
this has been done before, but I need it to specifically handle Excel csv
exports). Here's the snag:
Whenever Excel sees a comma or a quote in one of the cells, it wraps it in
quotes. If it doesn't find either, it doesn't. So for example, here's a row:
This is red, "This is sorta ""red""", "This is blue, really"
Now, the above is 3 columns in Excel. The 2nd column has quotes around 'red'
so Excel wraps the whole thing in quotes and escapes the quotes around the
word red by doubling them. This is relatively easy to handle.
The doosy is the third column. Excel found a comma so it wrapped it in
quotes (which is fine). The problem is, listgetat and other list functions
are pretty useless to me because it thinks a fourth column exists
(containing: really").
I'm guessing I can probably go down some sort of regular _expression_ hell to
do this, but is there an easier way?
Thanks,
Rich
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: CSV HELL
Rich, i have a custom tag to parse CSV files. Perhaps you can give it a shot: http://www.cftagstore.com/tags/csv2query.cfm Try the example here: http://www.masrizal.com/product/custom%20tag/csv2query/docs%20%26%20examples/csv2query_example.cfm Copy paste your CSV files into the textarea, and see if it works. Regards, Rizal At 07:15 PM 6/22/2004, you wrote: >Hi all: > > >I'm writing a little function that parses a CSV file into a query (I know >this has been done before, but I need it to specifically handle Excel csv >exports). Here's the snag: > > >Whenever Excel sees a comma or a quote in one of the cells, it wraps it in >quotes. If it doesn't find either, it doesn't. So for example, here's a row: > > >This is red, "This is sorta ""red""", "This is blue, really" > > >Now, the above is 3 columns in Excel. The 2nd column has quotes around 'red' >so Excel wraps the whole thing in quotes and escapes the quotes around the >word red by doubling them. This is relatively easy to handle. > > >The doosy is the third column. Excel found a comma so it wrapped it in >quotes (which is fine). The problem is, listgetat and other list functions >are pretty useless to me because it thinks a fourth column exists >(containing: really"). > > >I'm guessing I can probably go down some sort of regular _expression_ hell to >do this, but is there an easier way? > > >Thanks, >Rich > >-- >[Todays >Threads] >[This >Message] [Subscription] >[Fast >Unsubscribe] >[User >Settings] >[Donations >and Support] > >-- > >20b1685.jpg > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: CSV HELL
"Normal" CSV files use backslashes to escape quotes that are inside fields:
"this is \"one\" field"
While Excel uses doubling:
"this is ""one"" field"
Cheers,
barneyb
> -Original Message-
> From: Rick Root [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, June 22, 2004 10:29 AM
> To: CF-Talk
> Subject: Re: CSV HELL
>
> Barney Boisvert wrote:
>
> > There's a really nice set of utility classes available at
> > http://www.ostermiller.org/utils/CSV.html that I've used
> with great success
> > for parsing CSV files. It takes care of all the nastiness
> with quotes in
> > Excel CSV files. It also has classes for handling "normal"
> CSV files.
> > Little more work than dropping in a UDF, but it's blazing fast.
>
> You guys are all using the word "normal" wrong here.. Excel produces
> perfectly normal CSV files (amazingly)... Unfortunately, "normal" csv
> files can be rather complex.
>
> Perl has some modules similar to the java classes above that
> handle true
> CSV parsing, and if you expect to ever get a CSV generated by Excel,
> Access, or any variety of other applications, you should
> consider using
> such classes.
>
> You could probably do it like this:
>
>
>
> csvinput = CreateObject("java", "java.io.StringReader");
> csvinput.init(csvdata);
> csvparse =
> CreateObject("java","com.Ostermiller.util.CSVParser");
> results = csvparse.parse(csvinput)
> // results = 2 dimensional array of values
>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: CSV HELL
Barney Boisvert wrote:
> There's a really nice set of utility classes available at
> http://www.ostermiller.org/utils/CSV.html that I've used with great success
> for parsing CSV files. It takes care of all the nastiness with quotes in
> Excel CSV files. It also has classes for handling "normal" CSV files.
> Little more work than dropping in a UDF, but it's blazing fast.
You guys are all using the word "normal" wrong here.. Excel produces
perfectly normal CSV files (amazingly)... Unfortunately, "normal" csv
files can be rather complex.
Perl has some modules similar to the java classes above that handle true
CSV parsing, and if you expect to ever get a CSV generated by Excel,
Access, or any variety of other applications, you should consider using
such classes.
You could probably do it like this:
csvinput = CreateObject("java", "java.io.StringReader");
csvinput.init(csvdata);
csvparse = CreateObject("java","com.Ostermiller.util.CSVParser");
results = csvparse.parse(csvinput)
// results = 2 dimensional array of values
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
RE: CSV HELL
There's a really nice set of utility classes available at
http://www.ostermiller.org/utils/CSV.html that I've used with great success
for parsing CSV files. It takes care of all the nastiness with quotes in
Excel CSV files. It also has classes for handling "normal" CSV files.
Little more work than dropping in a UDF, but it's blazing fast.
Cheers,
barneyb
> -Original Message-
> From: Pascal Peters [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, June 22, 2004 6:29 AM
> To: CF-Talk
> Subject: RE: CSV HELL
>
> This is the regexp hell. There is no error handling for wrong csv. It
> also doesn't handle CR/LF in a quoted value. It's MX, but with some
> minor modifications it should work on CF5. But hey, I only had 20' to
> write it.
>
> I think you can use cfhttp to do it too.
>
> Pascal
>
>
> a,b,c
> This is red, "This is sorta ""red""", "This is blue, really"
> 1,2,3
>
>
>
> function CsvToQuery(str){
> var columnlist = "";
> var line = "";
> var i = 1;
> var j = 0;
> var startrow = 1;
> var start = 1;
> var val = "";
> var stTmp = StructNew();
> var qReturn = "";
> // if the columnlist isn't specified, use the first line in the
> csv as columnlist
> if(ArrayLen(arguments) GT 1){
> columnlist = arguments[2];
> }
> else{
> columnlist = ListFirst(str,chr(13)&chr(10));
> startrow = 2;
> }
>
> qReturn = QueryNew(columnlist);
> for(i=startrow;i LE ListLen(str,chr(13)&chr(10));i=i+1){
> line = ListGetAt(str,i,chr(13)&chr(10));
> j = 0;
> QueryAddRow(qReturn);
> start = 1;
> while(true){
> stTmp = REFind("([^,""]*|[
> \t]*""(?:""""|[^""])*""[ \t]*)(,|$)",line,start,true);
> if(stTmp.pos[1]){
> j=j+1;
> if(stTmp.len[2]){
> val =
> Mid(line,stTmp.pos[2],stTmp.len[2]);
> if(REFind("^[
> \t]*""((""""|[^""])*)""[ \t]*$",val)){
> val = REReplace(val,"^[
> \t]*""((""""|[^""])*)""[ \t]*$","\1");
> val =
> Replace(val,"""""","""","all");
> }
> }
> else val = "";
>
> QuerySetCell(qReturn,ListGetAt(columnlist,j),val);
> start = stTmp.pos[1]+stTmp.len[1];
> }
> else break;
> }
> }
> return qReturn;
> }
>
>
>
>
> > -Original Message-
> > From: Rich Ziade [mailto:[EMAIL PROTECTED]
> > Sent: dinsdag 22 juni 2004 14:15
> > To: CF-Talk
> > Subject: CSV HELL
> >
> > I'm guessing I can probably go down some sort of regular
> > _expression_ hell to do this, but is there an easier way?
> >
> > Thanks,
> > Rich
> >
>
>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
RE: CSV HELL
This is the regexp hell. There is no error handling for wrong csv. It
also doesn't handle CR/LF in a quoted value. It's MX, but with some
minor modifications it should work on CF5. But hey, I only had 20' to
write it.
I think you can use cfhttp to do it too.
Pascal
a,b,c
This is red, "This is sorta ""red""", "This is blue, really"
1,2,3
function CsvToQuery(str){
var columnlist = "";
var line = "";
var i = 1;
var j = 0;
var startrow = 1;
var start = 1;
var val = "";
var stTmp = StructNew();
var qReturn = "";
// if the columnlist isn't specified, use the first line in the
csv as columnlist
if(ArrayLen(arguments) GT 1){
columnlist = arguments[2];
}
else{
columnlist = ListFirst(str,chr(13)&chr(10));
startrow = 2;
}
qReturn = QueryNew(columnlist);
for(i=startrow;i LE ListLen(str,chr(13)&chr(10));i=i+1){
line = ListGetAt(str,i,chr(13)&chr(10));
j = 0;
QueryAddRow(qReturn);
start = 1;
while(true){
stTmp = REFind("([^,""]*|[
\t]*""(?:|[^""])*""[ \t]*)(,|$)",line,start,true);
if(stTmp.pos[1]){
j=j+1;
if(stTmp.len[2]){
val =
Mid(line,stTmp.pos[2],stTmp.len[2]);
if(REFind("^[
\t]*""((|[^""])*)""[ \t]*$",val)){
val = REReplace(val,"^[
\t]*""((|[^""])*)""[ \t]*$","\1");
val =
Replace(val,"",,"all");
}
}
else val = "";
QuerySetCell(qReturn,ListGetAt(columnlist,j),val);
start = stTmp.pos[1]+stTmp.len[1];
}
else break;
}
}
return qReturn;
}
> -Original Message-
> From: Rich Ziade [mailto:[EMAIL PROTECTED]
> Sent: dinsdag 22 juni 2004 14:15
> To: CF-Talk
> Subject: CSV HELL
>
> I'm guessing I can probably go down some sort of regular
> _expression_ hell to do this, but is there an easier way?
>
> Thanks,
> Rich
>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
RE: CSV HELL
Can you have the CSV created using the pipe ( | ) or double pipes as the separator instead of the comma? Then just define the pipe symbol as the delimiter for listGetAt(). HTH -Original Message- From: Rich Ziade [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 22, 2004 8:15 AM To: CF-Talk Subject: CSV HELL Hi all: I'm writing a little function that parses a CSV file into a query (I know this has been done before, but I need it to specifically handle Excel csv exports). Here's the snag: Whenever Excel sees a comma or a quote in one of the cells, it wraps it in quotes. If it doesn't find either, it doesn't. So for example, here's a row: This is red, "This is sorta ""red""", "This is blue, really" Now, the above is 3 columns in Excel. The 2nd column has quotes around 'red' so Excel wraps the whole thing in quotes and escapes the quotes around the word red by doubling them. This is relatively easy to handle. The doosy is the third column. Excel found a comma so it wrapped it in quotes (which is fine). The problem is, listgetat and other list functions are pretty useless to me because it thinks a fourth column exists (containing: really"). I'm guessing I can probably go down some sort of regular _expression_ hell to do this, but is there an easier way? Thanks, Rich _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: CSV File Format Specification
Hi Brook, There isn't really a proper standard for CSV files, it's not like a JPEG or MP3 or anything. But, having said that, I've never seen a CSV file with text-qualified fieldnames. And the text qualifier should only be used on text-format fields - unless the ID maps to a DB field that is in a text format, you don't need the quotes around it. Hope that helps, Alistair Alistair Davidson Senior Technical Developer Headshift.com Smarter, Simpler, Social [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: CSV File Format Specification
I believe you only have to text-qualify strings that contain commas or newlines, but that text-qualifying extra fields is acceptable. If you check out http://ostermiller.org/utils/CSV.html there's a little java library that deals with CSV files, both excel-generated and 'normal'. We've used it with great success. Cheers, barneyb > -Original Message- > From: Brook Davies [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 26, 2004 11:18 AM > To: CF-Talk > Subject: SOT: CSV File Format Specification > > Does anyone know where I can find the specs for a CSV file. > In particular I > want to know if the fieldnames in the first row of the file should be > qualified with the choosen text qualifier. So If I had a CSV > file with > these options: > > 1. Delimiter: Comma > 2. Text Qualifier: Single Quote > 3. Fieldnames in First Row : TRUE > > Which of these examples is correct: > > ID,name,address,sex > '1','Bob','44 Terraw Street','m' > '2','Jane','55 Water','f' > > or > > 'ID','name','address','sex' > '1','Bob','44 Terraw Street','m' > '2','Jane','55 Water','f' > > Thank you for your insights! > > Brook Davies > logiforms.com > > > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: CSV Upload Note
However, it is the CSV file format output by Peachtree, which is what I need
to interface with and represents real data input by Users. So I am stuck
with my "work around". I was hoping for an easier and faster solution.
Andy
-Original Message-
From: jon hall [mailto:[EMAIL PROTECTED]
Sent: Friday, June 27, 2003 5:23 PM
To: CF-Talk
Subject: Re: CSV Upload Note
If you were using a the correct custom delimiter for your file
...that's quite a bug, and quite incredible that it has gone
undetected for all these years.
If you left the DSN on the default of CSV delimited...expect crazy
things like that to happen, because the example you give is _not_ a
valid csv file.
--
jon
mailto:[EMAIL PROTECTED]
Friday, June 27, 2003, 5:06:46 PM, you wrote:
AO> I've tested using OBDC to upload CSV files into queries and have found
that
AO> they do not handle the following situation properly:
AO> Small Heart, with ""Red" decorations
AO> is translated as
AO> Small Heart, with "Red
AO> and
AO> Small Heart, with PINK "decorations"
AO> becomes
AO> Small Heart, with PINK
AO> The following code handles this correctly:
AO>
AO>
AO> aryCSV = ArrayNew(1);
AO> // Only do stuff if something passed
AO> if (len(lstCSV) GT 0) {
AO>// First, add a comma onto the end so that the search for ", and not
"",
AO> always finds something unless bad CSV string.
AO>// Then, replace any ",," with ", ," so that individual array
elements will
AO> be found
AO>lstWork = Trim(lstCSV) & ",";
AO>lstWork = replace(lstWork, ",", ", ", "ALL");
AO>// Then loop thru string, parsing off to next comma and checking if
AO> complete field
AO>// Note that if the field has imbedded quotes, this routine will
create bad
AO> data.
AO>// For example, [This is a test, "test"] will come through as ["This
is a
AO> test, ""test""]
AO>// and will be parsed into two fields, [This is a test, "] and
["test"].
AO> The calling routine will
AO>// need to handle the possibility that more fields will be returned
then
AO> expected.
AO>Do {
AO> // If Line starts with quote, next field is next quote+comma that is
not
AO> part of a quote+quote+comma
AO> if (left(lstWork, 1) EQ '"') {
AO> tmpStr = Right(lstWork, len(lstWork)-1);
AO> EndPos = REFInd('([^"]",) | ("*"",)', tmpStr);
AO> // If nothing found, look for "", ending
AO> if (EndPos EQ 0) {
AO> EndPos=REFInd('([^"]",) | (["]*["",])', tmpStr);
AO> EndPos=REFInd('("",)', Mid(tmpStr, EndPos+1, len(tmpStr)))+EndPos;
AO> }
AO> // If still nothing found, Error. Stop work immediately
AO> if (EndPos EQ 0) {
AO> lstwork = "";
AO> NextField = "";
AO> }
AO> Else {
AO> NextField = mid(trim(lstWork), 2, EndPos);
AO> if (len(lstWork)-(EndPos+2) EQ 0)
AO>lstwork = "";
AO> else
AO>lstWork = ltrim(right(lstWork, len(lstWork)-(EndPos+3)));
AO> }
AO> }
AO> else if (left(lstwork, 1) EQ ",") {
AO> lstWork = ltrim(right(lstWork, len(lstWork)-1));
AO> NextField = " ";
AO> }
AO> else {
AO> NextField = Trim(ListFirst(lstWork, ","));
AO> lstWork = LTrim(ListDeleteAt(lstWork, 1, ","));
AO> }
AO> //Append the new field, converting CSV's double quotes to single
quotes
AO> ArrayAppend(aryCSV, Replace(NextField, '""', '"', "all"));
AO> //writeoutput(",[#lstwork#], [#NextField#]");
AO>}
AO>while (len(lstWork) GT 1);
AO> }
AO>
AO>
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Host with the leader in ColdFusion hosting.
Voted #1 ColdFusion host by CF Developers.
Offering shared and dedicated hosting options.
www.cfxhosting.com/default.cfm?redirect=10481
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Upload Note
If you were using a the correct custom delimiter for your file
...that's quite a bug, and quite incredible that it has gone
undetected for all these years.
If you left the DSN on the default of CSV delimited...expect crazy
things like that to happen, because the example you give is _not_ a
valid csv file.
--
jon
mailto:[EMAIL PROTECTED]
Friday, June 27, 2003, 5:06:46 PM, you wrote:
AO> I've tested using OBDC to upload CSV files into queries and have found that
AO> they do not handle the following situation properly:
AO> Small Heart, with ""Red" decorations
AO> is translated as
AO> Small Heart, with "Red
AO> and
AO> Small Heart, with PINK "decorations"
AO> becomes
AO> Small Heart, with PINK
AO> The following code handles this correctly:
AO>
AO>
AO> aryCSV = ArrayNew(1);
AO> // Only do stuff if something passed
AO> if (len(lstCSV) GT 0) {
AO>// First, add a comma onto the end so that the search for ", and not "",
AO> always finds something unless bad CSV string.
AO>// Then, replace any ",," with ", ," so that individual array elements will
AO> be found
AO>lstWork = Trim(lstCSV) & ",";
AO>lstWork = replace(lstWork, ",", ", ", "ALL");
AO>// Then loop thru string, parsing off to next comma and checking if
AO> complete field
AO>// Note that if the field has imbedded quotes, this routine will create bad
AO> data.
AO>// For example, [This is a test, "test"] will come through as ["This is a
AO> test, ""test""]
AO>// and will be parsed into two fields, [This is a test, "] and ["test"].
AO> The calling routine will
AO>// need to handle the possibility that more fields will be returned then
AO> expected.
AO>Do {
AO> // If Line starts with quote, next field is next quote+comma that is not
AO> part of a quote+quote+comma
AO> if (left(lstWork, 1) EQ '"') {
AO> tmpStr = Right(lstWork, len(lstWork)-1);
AO> EndPos = REFInd('([^"]",) | ("*"",)', tmpStr);
AO> // If nothing found, look for "", ending
AO> if (EndPos EQ 0) {
AO> EndPos=REFInd('([^"]",) | (["]*["",])', tmpStr);
AO> EndPos=REFInd('("",)', Mid(tmpStr, EndPos+1, len(tmpStr)))+EndPos;
AO> }
AO> // If still nothing found, Error. Stop work immediately
AO> if (EndPos EQ 0) {
AO> lstwork = "";
AO> NextField = "";
AO> }
AO> Else {
AO> NextField = mid(trim(lstWork), 2, EndPos);
AO> if (len(lstWork)-(EndPos+2) EQ 0)
AO>lstwork = "";
AO> else
AO>lstWork = ltrim(right(lstWork, len(lstWork)-(EndPos+3)));
AO> }
AO> }
AO> else if (left(lstwork, 1) EQ ",") {
AO> lstWork = ltrim(right(lstWork, len(lstWork)-1));
AO> NextField = " ";
AO> }
AO> else {
AO> NextField = Trim(ListFirst(lstWork, ","));
AO> lstWork = LTrim(ListDeleteAt(lstWork, 1, ","));
AO> }
AO> //Append the new field, converting CSV's double quotes to single quotes
AO> ArrayAppend(aryCSV, Replace(NextField, '""', '"', "all"));
AO> //writeoutput(",[#lstwork#], [#NextField#]");
AO>}
AO>while (len(lstWork) GT 1);
AO> }
AO>
AO>
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CSV Question
Everything sounds correct. The only other thing I did was uncheck maintain connections, which shouldn't affect whether or not it works. Does your schema.ini look normal, and is it in the right dir? -- mailto:[EMAIL PROTECTED] Friday, June 27, 2003, 12:16:12 AM, you wrote: AO> Here is what I've done so far: AO> 1. Set up ODBC System DSN called TextSource that uses Microsoft Text AO> Driver, AO> pointed to my Upload directory, and for simplicity in this discussion, AO> defined AO> the fields contained in my csv. AO> 2. Went into MX Administrator and created CSVFile Data Source defined as an AO> ODBC Socket, and selected TexttSource as the ODBC DSN. AO> I get this error when I hit submit: AO> a.. Connection verification failed for data source: CSVFile AO> []java.sql.SQLException: SQLException occurred in JDBCPool while AO> attempting AO> to connect, please check your username, password, URL, and other AO> connectivity AO> info. AO> The root cause was that: java.sql.SQLException: SQLException occurred in AO> JDBCPool while attempting to connect, please check your username, password, AO> URL, and other connectivity info. AO> What am I doing wrong? I've assigned User being System, and I don't have AO> passwords on this test box. AO> -Original Message- AO> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] AO> Sent: Thursday, June 26, 2003 10:55 PM AO> To: CF-Talk AO> Subject: Re: CSV Question AO> Forgive the asp site...but it's got screenshots :) AO> http://www.c-sharpcorner.com/database/Connect/ConnectODBCText.asp AO> A couple things to add to what the above site says. AO> An ini file (schema.ini in example) will be created in the same AO> directory as the csv after the dsn is created. Take a look at it..it's AO> fairly self-explanatory. The built in CF ini functions will let you AO> access and change it around, and add new text files for it. You only AO> need to create one ini file for an nearly unlimited number of csv files in AO> the same directory. AO> After you create the dsn in windows, create an odbc socket dsn in CF AO> pointing to the text dsn you created. That's "textsource" in my AO> example. AO> The schema.ini for the app I used this code in looks like this, just AO> a lot bigger... AO> [0927TPE-1.txt] AO> ColNameHeader=False AO> Format=CSVDelimited AO> MaxScanRows=0 AO> CharacterSet=OEM AO> Col1=CUSTNAME Char Width 255 AO> Col2=REPNAME Char Width 255 AO> Col3=CUSTEMAIL Char Width 255 AO> [100402me-111.txt] AO> ColNameHeader=False AO> Format=CSVDelimited AO> MaxScanRows=0 AO> CharacterSet=OEM AO> Col1=CUSTNAME Char Width 255 AO> Col2=REPNAME Char Width 255 AO> Col3=CUSTEMAIL Char Width 255 AO> ... AO> -- AO> mailto:[EMAIL PROTECTED] AO> Thursday, June 26, 2003, 11:14:31 PM, you wrote: AO>> Jon, AO>> I am trying to use your code to translate a CSV file into a query. You AO>> reference datasource="textsource". What is textsource? AO>> Andy AO>> -Original Message- AO>> From: jon hall [mailto:[EMAIL PROTECTED] AO>> Sent: Wednesday, January 29, 2003 7:12 PM AO>> To: CF-Talk AO>> Subject: Re: New problem with csv... argghhh AO>> By editing the schema.ini file you can add a text datasource with CF's AO>> ini file functions. That way you can dynamically add dsn's when the AO>> end user uploads the file. Take a look at the format of the schema.ini AO>> file that is created, it's pretty straightforward. AO>> The fact that these files are very large, would be a reason to not AO>> write a manual parsing routine. Microsoft (or someone they bought :)) AO>> already wrote a very good and fast csv parser into the ODBC Text AO>> driver...no need to recreate the wheel imo. I wrote quite a few csv AO>> imports using CF manually and there is still a indentation the size of AO>> my forehead on my desk because of those sleepless nights :) AO>> If you were working with SQL Server, using DTS to do the import would be AO> a AO>> great way as well. Just wanted to throw some options your way. AO>> This is a cffunction that takes the filename after is has been AO>> uploaded, and creates the Text DSN, and returns the query. It could be AO>> easily modified it work in previous versions of CF though. AO>> AO>> AO>> AO>> > "ColNameHeader"))) EQ 0> AO>> arguments.filename, AO>> "ColNameHeader", "False")> AO>> arguments.filename, AO>> "Format", "CSVDelimited")> AO>> arguments.filename, "MaxSca
RE: CSV Question
Here is what I've done so far: 1. Set up ODBC System DSN called TextSource that uses Microsoft Text Driver, pointed to my Upload directory, and for simplicity in this discussion, defined the fields contained in my csv. 2. Went into MX Administrator and created CSVFile Data Source defined as an ODBC Socket, and selected TexttSource as the ODBC DSN. I get this error when I hit submit: a.. Connection verification failed for data source: CSVFile []java.sql.SQLException: SQLException occurred in JDBCPool while attempting to connect, please check your username, password, URL, and other connectivity info. The root cause was that: java.sql.SQLException: SQLException occurred in JDBCPool while attempting to connect, please check your username, password, URL, and other connectivity info. What am I doing wrong? I've assigned User being System, and I don't have passwords on this test box. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 26, 2003 10:55 PM To: CF-Talk Subject: Re: CSV Question Forgive the asp site...but it's got screenshots :) http://www.c-sharpcorner.com/database/Connect/ConnectODBCText.asp A couple things to add to what the above site says. An ini file (schema.ini in example) will be created in the same directory as the csv after the dsn is created. Take a look at it..it's fairly self-explanatory. The built in CF ini functions will let you access and change it around, and add new text files for it. You only need to create one ini file for an nearly unlimited number of csv files in the same directory. After you create the dsn in windows, create an odbc socket dsn in CF pointing to the text dsn you created. That's "textsource" in my example. The schema.ini for the app I used this code in looks like this, just a lot bigger... [0927TPE-1.txt] ColNameHeader=False Format=CSVDelimited MaxScanRows=0 CharacterSet=OEM Col1=CUSTNAME Char Width 255 Col2=REPNAME Char Width 255 Col3=CUSTEMAIL Char Width 255 [100402me-111.txt] ColNameHeader=False Format=CSVDelimited MaxScanRows=0 CharacterSet=OEM Col1=CUSTNAME Char Width 255 Col2=REPNAME Char Width 255 Col3=CUSTEMAIL Char Width 255 ... -- mailto:[EMAIL PROTECTED] Thursday, June 26, 2003, 11:14:31 PM, you wrote: AO> Jon, AO> I am trying to use your code to translate a CSV file into a query. You AO> reference datasource="textsource". What is textsource? AO> Andy AO> -Original Message- AO> From: jon hall [mailto:[EMAIL PROTECTED] AO> Sent: Wednesday, January 29, 2003 7:12 PM AO> To: CF-Talk AO> Subject: Re: New problem with csv... argghhh AO> By editing the schema.ini file you can add a text datasource with CF's AO> ini file functions. That way you can dynamically add dsn's when the AO> end user uploads the file. Take a look at the format of the schema.ini AO> file that is created, it's pretty straightforward. AO> The fact that these files are very large, would be a reason to not AO> write a manual parsing routine. Microsoft (or someone they bought :)) AO> already wrote a very good and fast csv parser into the ODBC Text AO> driver...no need to recreate the wheel imo. I wrote quite a few csv AO> imports using CF manually and there is still a indentation the size of AO> my forehead on my desk because of those sleepless nights :) AO> If you were working with SQL Server, using DTS to do the import would be a AO> great way as well. Just wanted to throw some options your way. AO> This is a cffunction that takes the filename after is has been AO> uploaded, and creates the Text DSN, and returns the query. It could be AO> easily modified it work in previous versions of CF though. AO> AO> AO> AO> "ColNameHeader"))) EQ 0> AO> "ColNameHeader", "False")> AO> "Format", "CSVDelimited")> AO> > AO> "CharacterSet", "OEM")> AO> "Col1", "CUSTNAME Char Width 255")> AO> "Col2", "REPNAME Char Width 255")> AO> "Col3", "CUSTEMAIL Char Width 255")> AO> AO> AO> SELECT * AO> FROM [#arguments.filename#] AO> AO> AO> AO> -- AO> jon AO> mailto:[EMAIL PROTECTED] AO> Tuesday, January 28, 2003, 7:27:05 PM, you wrote: RAB>> At 06:11 PM 1/29/03 -0500, you wrote: >>>Create a ODBC Text Datasource to the file one the machine and then >>>just use cfquery to turn it into a query...which you can loop over or RAB>> THe problem is it's a multi-thousand record product table from multiple RAB>> manufacturers and new prices
Re: CSV Question
Forgive the asp site...but it's got screenshots :) http://www.c-sharpcorner.com/database/Connect/ConnectODBCText.asp A couple things to add to what the above site says. An ini file (schema.ini in example) will be created in the same directory as the csv after the dsn is created. Take a look at it..it's fairly self-explanatory. The built in CF ini functions will let you access and change it around, and add new text files for it. You only need to create one ini file for an nearly unlimited number of csv files in the same directory. After you create the dsn in windows, create an odbc socket dsn in CF pointing to the text dsn you created. That's "textsource" in my example. The schema.ini for the app I used this code in looks like this, just a lot bigger... [0927TPE-1.txt] ColNameHeader=False Format=CSVDelimited MaxScanRows=0 CharacterSet=OEM Col1=CUSTNAME Char Width 255 Col2=REPNAME Char Width 255 Col3=CUSTEMAIL Char Width 255 [100402me-111.txt] ColNameHeader=False Format=CSVDelimited MaxScanRows=0 CharacterSet=OEM Col1=CUSTNAME Char Width 255 Col2=REPNAME Char Width 255 Col3=CUSTEMAIL Char Width 255 ... -- mailto:[EMAIL PROTECTED] Thursday, June 26, 2003, 11:14:31 PM, you wrote: AO> Jon, AO> I am trying to use your code to translate a CSV file into a query. You AO> reference datasource="textsource". What is textsource? AO> Andy AO> -Original Message- AO> From: jon hall [mailto:[EMAIL PROTECTED] AO> Sent: Wednesday, January 29, 2003 7:12 PM AO> To: CF-Talk AO> Subject: Re: New problem with csv... argghhh AO> By editing the schema.ini file you can add a text datasource with CF's AO> ini file functions. That way you can dynamically add dsn's when the AO> end user uploads the file. Take a look at the format of the schema.ini AO> file that is created, it's pretty straightforward. AO> The fact that these files are very large, would be a reason to not AO> write a manual parsing routine. Microsoft (or someone they bought :)) AO> already wrote a very good and fast csv parser into the ODBC Text AO> driver...no need to recreate the wheel imo. I wrote quite a few csv AO> imports using CF manually and there is still a indentation the size of AO> my forehead on my desk because of those sleepless nights :) AO> If you were working with SQL Server, using DTS to do the import would be a AO> great way as well. Just wanted to throw some options your way. AO> This is a cffunction that takes the filename after is has been AO> uploaded, and creates the Text DSN, and returns the query. It could be AO> easily modified it work in previous versions of CF though. AO> AO> AO> AO> "ColNameHeader"))) EQ 0> AO> "ColNameHeader", "False")> AO> "Format", "CSVDelimited")> AO> > AO> "CharacterSet", "OEM")> AO> "Col1", "CUSTNAME Char Width 255")> AO> "Col2", "REPNAME Char Width 255")> AO> "Col3", "CUSTEMAIL Char Width 255")> AO> AO> AO> SELECT * AO> FROM [#arguments.filename#] AO> AO> AO> AO> -- AO> jon AO> mailto:[EMAIL PROTECTED] AO> Tuesday, January 28, 2003, 7:27:05 PM, you wrote: RAB>> At 06:11 PM 1/29/03 -0500, you wrote: >>>Create a ODBC Text Datasource to the file one the machine and then >>>just use cfquery to turn it into a query...which you can loop over or RAB>> THe problem is it's a multi-thousand record product table from multiple RAB>> manufacturers and new prices come out all the time - so the client AO> needs a RAB>> way to batch change specific product records... If I create an ODBC RAB>> connection, wouldn't I need to shut it off for every time the Text RAB>> datasource is changed? RAB>> AO> ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: csv files
Actually, for large files, jon hall is correct... Use DTS, not CF. CF is good for samller recordsets. Eventually the http request will time out or something... And end users get sick of waiting. Alternatively, and if you hafta use cf, inside the statement, I'd use to create some kind of feedback for the end user about completion... Although cfflush slows things down, the feedback to the end user is quite valuable and can keep them waiting instead of hitting refresh... Before cfflush, we'd fake it with an animaged gif or something... That worked well too... -Original Message- From: Tony Schreiber [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 12:44 PM To: CF-Talk Subject: RE: csv files However, do NOT do that for very large (50,000+ records) reports. Write each line at a time. It may be counter-intuitive, but apparently, appending data to a variables takes exponentially longer as the variable grows bigger... > don't do a CFFILE ACTION="append", that'll be slow like nothing else. > Build the entire file in memory, and then write it with a single > CFFILE. If your memory won't handle it, then break it down into > chunks (maybe a few hundred or a thousand records each), and write > those chunks to the file, rather than each record. > > --- > Barney Boisvert, Senior Development Engineer > AudienceCentral (formerly PIER System, Inc.) > [EMAIL PROTECTED] voice : 360.756.8080 x12 > fax : 360.647.5351 > > www.audiencecentral.com > > > -Original Message- > > From: Costas Piliotis [mailto:[EMAIL PROTECTED] > > Sent: Thursday, June 05, 2003 11:47 AM > > To: CF-Talk > > Subject: RE: csv files > > > > > > Here. Convert query and filename as you like: > > > > > > select branchid, branchname from tblbranches > > > > > > > > > > > > > > > > > > > output="#myvar#"> > > > > > > -Original Message- > > From: Michael Tangorre [mailto:[EMAIL PROTECTED] > > Sent: Thursday, June 05, 2003 11:31 AM > > To: CF-Talk > > Subject: csv files > > > > > > Shoot me dead if this has been asked and answered, but pelase > > respond nonetheless so that my replacement can jump right in: > > > > what is the easiest way to create a csv file from a query? What I > > have is a custom tag that takes a runs a query and need to genenrate > > a csv file and then save it to the server using cffile. > > > > > > TIA, > > > > Mike > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: csv files
Hence the batching. Do a thousand at a time. Best of both worlds. --- Barney Boisvert, Senior Development Engineer AudienceCentral (formerly PIER System, Inc.) [EMAIL PROTECTED] voice : 360.756.8080 x12 fax : 360.647.5351 www.audiencecentral.com > -Original Message- > From: Tony Schreiber [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 05, 2003 12:44 PM > To: CF-Talk > Subject: RE: csv files > > > However, do NOT do that for very large (50,000+ records) reports. Write > each line at a time. It may be counter-intuitive, but apparently, > appending data to a variables takes exponentially longer as the variable > grows bigger... > > > don't do a CFFILE ACTION="append", that'll be slow like nothing > else. Build > > the entire file in memory, and then write it with a single > CFFILE. If your > > memory won't handle it, then break it down into chunks (maybe a > few hundred > > or a thousand records each), and write those chunks to the > file, rather than > > each record. > > > > --- > > Barney Boisvert, Senior Development Engineer > > AudienceCentral (formerly PIER System, Inc.) > > [EMAIL PROTECTED] > > voice : 360.756.8080 x12 > > fax : 360.647.5351 > > > > www.audiencecentral.com > > > > > -Original Message- > > > From: Costas Piliotis [mailto:[EMAIL PROTECTED] > > > Sent: Thursday, June 05, 2003 11:47 AM > > > To: CF-Talk > > > Subject: RE: csv files > > > > > > > > > Here. Convert query and filename as you like: > > > > > > > > > select branchid, branchname from tblbranches > > > > > > > > > > > > > > > > > > > > > > > > > > >> > output="#myvar#"> > > > > > > > > > -Original Message- > > > From: Michael Tangorre [mailto:[EMAIL PROTECTED] > > > Sent: Thursday, June 05, 2003 11:31 AM > > > To: CF-Talk > > > Subject: csv files > > > > > > > > > Shoot me dead if this has been asked and answered, but pelase respond > > > nonetheless so that my replacement can jump right in: > > > > > > what is the easiest way to create a csv file from a query? What I > > > have is a > > > custom tag that takes a runs a query and need to genenrate a > csv file and > > > then save it to the server using cffile. > > > > > > > > > TIA, > > > > > > Mike > > > > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
