AnsweredAssumed Answered

vrf Masses of data from Excel

Question asked by antti.suhonen on Jun 11, 2004
Hi,

     Erm, talked about getting data *from* excel ;D (Who needs stuffing
it???)

     Anywhoo, wanted to do that in VEE instead of formatting a 30kcell
range, see my customer has a SW that produces this XLS from DB and in a
single column there lurks a description, limits and sequential data below
(won't like having multiple objects pointing to subranges either). So,
formatting the whole column would scrap it. Hey, these are results overnight
of...hmmm...lessee...a test called "98927184642177217727721"...pardon me
while I bang me head to desk...

     So I ended up making a transfer array with:
transM=strPosChar(ary,",")+1, then getting string part before the comma,
adding '.' if necessary and finally pasting string part after the comma:
strFromLen(ary,0,transM-1) + clipUpper(transM,1)*"." +
strFromThru(ary,transM,strLen(ary))...Seems to work adequtely; Getting ~15k
results, saving as, closing, reading with 'from file' and deleting tempfile
takes 0.2557s altogheter and formatting commas and empty results out 0.162
more. Think I can live with that ;D
     Now I just have to follow Shawn's recommendation...weekend ahoy.

     Have nice one, all of you. Great place, this VRF.

BR,
Antti
     
     

> -----Original Message-----
> From: Shawn Fessenden [mailto:shawn@testech-ltd.com]
> Sent: 11. keskuuta 2004 14:08
> To: VRF
> Subject: [vrf] RE: Masses of data from Excel... FW: arbot
>
>
> This is the original message. The attachment transfers a test
> array to an
> Excel Range whose shape varies to demonstrate what goes
> where. The reverse
> operation applies equally. An Excel Range of whatever shape can be
> transferred to VEE as efficiently as possible (considering
> IDispatch) using
> an array.
>
> If the shape of the VEE array is not going to match the shape
> of the Excel
> Range, that's when there's trouble. The best thing to do is
> modify the shape
> of the Excel Range to match that necessary in VEE (i.e. when
> the data is
> entered in the sheet). The second best thing to do is to read
> the Range as
> it is and use VEE's array transformations to modify the shape.
>
> This covers only the first of the methods I mentioned:
> transferring data
> over IDispatch. I'll see if I can find 50M of data somewhere
> and try some
> stuff out. I'm pretty sure the largest data sets I have are a
> lot smaller
> though.
>
> JUST RECEIVED NEXT MESSAGE ON VRF:
>
> > just were working on through-file trick
>
> Ok, then that's settled
>
> > my locales are using comma as decimal separator and that
> > does some funny stuff
>
> Ah yes. That would be a problem! You know, I think there's a
> way to override
> that with Format. I'm not real fond of Format, but it
> definitely comes in
> handy in lots of situations. There are some Excel wizards
> around here. Greg
> Wale is one of 'em. I'm pretty sure you can select an entire
> range and apply
> a custom format in an instant.
>
> > my head is not as it used to be ;D
>
> Oh yeah! Brain mush. I recommend some alcohol (dosage varies)
> & music, and
> some dancing if you go in for that sort of thing. Follow with
> sleep and
> you'll be good as new.
>
> ---
>
> >From        = Shawn Fessenden
> Date        = 12/25/2003 10:04:32 PM
> Subject     = [vrf] RE: VEE to Excel
> MessageID   = 0000000011C77702B4B2CB4A9FD20D99053FF730244F2E00
> Attachments = Excel.vee (4.6KB), footer.txt (530B)
>
> > the order of the rows are mix up.
>
> When you enter data cell by cell, the ActiveCell gets the
> datum transferred.
> In the VEE example Excel97a.vee, they're explicitly
> addressing a destination
> cell (singular) with the Cells method of the Worksheet object
> (which returns
> a Range object).
>
> When transferring an array (a much more efficient data
> transfer mechanism
> considering the IDispatch transport medium), data are transferred in
> row-major order and the shape of the destination Range
> controls what goes
> where. In the attached example, different Range shapes
> produce different
> results according to the size and shape of the source array vs. the
> destination Range.
>
> [NOTE: 06/11/2004 - I don't know if the reverse is true: if
> you define a VEE
> array with a certain size and shape and read an Excel Range
> into it, I have
> no idea what goes where or if it would even work. I tend to think it
> wouldn't work in the first place.]
> -SHAWN-
>



Please note: This e-mail may contain privileged and confidential information
and is intended solely for the addressee. If you are not the intended
recipient or an employee responsible for delivering this message to the
intended recipient, you are hereby notified that any review, publication,
dissemination, distribution, copying or use of the information in this
message is strictly prohibited. If you have received this e-mail by error,
please notify the sender immediately by replying to the message and
subsequently delete it from your system.

E-mail is susceptible to data corruption, interception, unauthorized
amendment, tampering and viruses. We only send and receive e-mail messages
on the basis that we are not liable for any such corruption, interception,
amendment, tampering, viruses, any other reprehensible acts or any
consequences of the aforementioned.


---
You are currently subscribed to vrf as: rsb@soco.agilent.com
To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com".
To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com".
To send messages to this mailing list,  email "vrf@agilent.com". 
If you need help with the mailing list send a message to "owner-vrf@it.lists.it.agilent.com".

Outcomes