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 On 10/4/2010 14:27, Cox, Ed wrote: > To all, > Thanks for all the suggestions thus far. Unfortunately most are not viable > for what I need. I am handling large > extracts that can exceed well over 100k rows and multiple tabs from multiple > queries. The data are complex > medical terms, lab units, etc with all manner of data weirdness coming from > hospitals - including fun stuff like > unbalanced quotations. (That last plays havoc with the clip-board/paste > method I currently use.) Also, unfortunately > I am stuck having to use Excell due to MS being the supported platform in the > virtual server environment I am > constrained to due to patient privacy issues. > > To Bill, > I have tried playing with wdooo unsuccessfully. Unless there is some > language feature required in the scripts that > are not supported in j602 then I am seeing what appears to be some coding > issue in the wdooo script. (I tried > j701beta but did not make it very far.) In wdooo.ijs it was complaining > about (row 172): > VTSTR=: ;<@cut;._2 (0 : 0) > > > > Not knowing anything about what was going on I took a wild stab and replaced > it with: > > VTSTR=: ;<;._2 (0 : 0) > > > > Then I tried it and it died on this line in msexcel.ijs: > > 'me base temp'=: olecreate__p 'Excel.Application' > > > > When I tried olecreate__p 'Excel.Application' all by itself, it only > returned two values and not 3 as the calling line is > > looking for. I do note that an Excel process is being invoked, so at least > it is trying to do something. > > > > So at this point I am stuck. I don't know if any of that is enough to > suggest a solution to you or not. I realize I can't > > expect to go through tons of troubleshooting like this with you but hope > maybe there is just enough here to get to a > > solution if we are lucky enough that there are no othere issue but these. > > > > -Ed Cox > > > > _________________________________________________ > > 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 > ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
