>Ed, > >I generated a random data set with 150,000 rows and 4 columns. Two of the >columns were random integers. Two of the columns were random text strings >ranging from 0-19 characters long. They included both alpha cases, digits and >" >-." > >I then used 3 of the methods mentioned in this thread to transfer the data set >from J to Excel using non-macro OLE. I realize this does not fully duplicate >some of the data content issues you described, but perhaps it comes close to >the >data volume you are interested in. > >1. "Brute Force" > >I looped over the complete data set and sent each item to Excel, one at a time. > >This took 11.5 minutes. > >2. "Rows then texttocolumns" > >I formatted each row with ';' delimiters, sent each row to Excel, then used a >single texttocolumns call from J. > >This took 3 minutes and 20 seconds. > >3. "csv" > >I wrote out the data set with writecsv and then imported it using > >xlcmd 'wb opentext "',(1!:43''),'\bigdata.csv"' > >This took 17 seconds. > >All of these tests were done on a rather wimpy notebook, with Win7-64, 2 gig >of >memory, j6.02-32 and Excel 2010. > >-- >David Mitchell
Hi David, I appreciate all the help being directly my way. At the risk of appearing very picky I should point out that I already do have a working solution using the xlutil.ijs and the clip-board/paste approach - and it works very fast. It works faster than the original manual method we were using, which is a base requirement. Also, it would not make sense to migrate from a fast solution to one that is much slower. So I would say that eliminates the first 2 methods you suggest. I have toyed with the 3rd option, but I am VERY paranoid about the various ways that Excel finds to mess up data imported into it. A tab delimited file might work. But the other problem with this approach is that such imports always start the data at cell A1 and I need the ability to use a template file that has certain fields prepopulated and that expects the imported data to be able to start at any designated cell. So I am sure that if I tried hard enough that I could brute force a solution that uses an imported file and then various OLE or jmacro commands to move that data to a desired location. But it starts to become a very unelegant solution very fast. So that is why I am really hoping to find a true OLE automation solution. As a last resort I might try to figure out how to directly write a XLSX file, which might be the best overall solution. I hope this helps clarify my requirements a bit. -Ed _________________________________________________ This message is for the designated recipient only and may contain privileged, proprietary or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. Dansk - Deutsch - Espanol - Francais - Italiano - Japanese - Nederlands - Norsk - Portuguese Svenska: www.carefusion.com/legal/email ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
