Re:Elaborate on the file Excel Help...VB 6.0 if necessary...


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Re:Elaborate on the file Excel Help...VB 6.0 if necessary...

  1. #1
    Bill Guest

    Re:Elaborate on the file Excel Help...VB 6.0 if necessary...


    Patrick;

    I'll try an elaborate. I have a fixed length text file emailed to me. I open
    the file with Ms Word. Shrink the font, clean things up a bit and print,
    where we have to manually go through a 200 page report everyday to
    grab a few numbers...Anyhow the file is organized by stocks and option symbols.
    There could be 1 or 100 lines of data. But the "key" is that
    at the end of every "block" of symbols is last 1 to 5 records whose string
    begins with an asterick. It looks like this:

    7 records 5 with beginning astericks:

    2LC00 VAN JAN 03 70.000 C 45 .........etc etc
    2LC00 VAN JAN 03 75.000 C 45 .........etc etc
    2LC00 VAN JAN 03 80.000 C 45 .........etc etc
    ** TOTAL ** 300* 30*
    ** TOTAL LONG SIDE VS. SHORT SIDE**
    ** STOCK**
    ** UNSTL**
    ** NET STK**
    (PHLX) -71/ 1537

    or it might look like this

    5 records 3 with beginning astericks:

    2LC00 VAN JAN 03 70.000 C 45 .........etc etc
    2LC00 VAN JAN 03 75.000 C 45 .........etc etc
    2LC00 VAN JAN 03 80.000 C 45 .........etc etc
    ** TOTAL ** 300* 30*
    ** TOTAL LONG SIDE VS. SHORT SIDE**
    ** STOCK** AOL
    (PCX ) -4571/ 1537

    I need nothing in the whole report except to print a report , or the results
    of our filtering the last few lines.
    I need the report to print out
    a) The account number: (2LC00 in this case)
    b) The Stock symbol: ( AOL in this case)
    c) The Exchange: ( PCX in this case)
    d) The numbers in the record: ( -4571 / 1537 )

    If you have any more questions, or would like to recieve a sample of the
    text file let me know..


    Thanks for taking so much time out to help me :>)

    Bill














    "Patrick Marshall" <nospam@nospam.com> wrote:
    >
    >Hi Bill,
    >
    >Can you elaborate on what you are trying to do? When you say "generate

    a
    >report..." do you mean that you are trying to break up the text file into
    >blocks bordered by the marker row (CBOE) -1745/ -124 ? If so, this can

    easily
    >be done with VB (or you can automate Excel to do it to, but I don't think
    >it would be much simpler).
    >
    >For example:
    >
    >const kEndToken1 ="(CBOE) -1745/ -124"
    >const kEndToken2 ="(AMEX) -1745/ -124"
    >const kEndToken3 ="(PHLX) -1745/ -124"
    >
    >dim FileNumInt as integer
    >dim BlockStrArr() as string 'one block for report
    >dim SizeOfBlockStrArrLng as long
    >dim s as string
    >
    >fileNumInt = freefile()
    >open "c:\testInpt.txt" for input as fileNumInt
    >
    >'NOTE use instr with vbBinaryCompare since it is MUCH faster than
    >'the default text compare (MUST be sure that your end tokens are
    >'always same case
    >do while not fileNumInt.eof
    > line input #fileNumInt, s
    > redim preserve BlockStrArr(SizeOfBlockStrArrLng)
    > BlockStrArr(SizeOfBlockStrArr) = s
    > SizeOfBlockStrArrLng = SizeOfBlockStrArrLng + 1
    > if instr(1,s,kEndToken1,vbBinaryCompare) > 0 then
    > 'insert your subroutine here to generate report
    > GenerateBlockRept BlockStrArr(), SizeOfBlockStrArr, kEndToken1
    > SizeOfBlockStrArrLng = 0
    > redim BlockStrArr(0) 'blank out block for next run
    > elseif instr(1,s,kEndToken2,vbBinaryCompare) > 0 then
    > 'insert your subroutine here to generate report
    > GenerateBlockRept BlockStrArr(), SizeOfBlockStrArr, kEndToken2
    > SizeOfBlockStrArrLng = 0
    > redim BlockStrArr(0) 'blank out block for next run
    > 'elseif kendtoken3 etc.
    > end if
    >
    >loop
    >
    >close #FileNumInt
    >
    >"Bill" <uliv@eqtc.com> wrote:
    >>
    >>Sirs or Madam;
    >>
    >>I have a HUGE fixed length text file to work with. I need to ready each

    >line
    >>until I “see” the last line in each group.In this case the string is:
    >>
    >>(CBOE) -1745/ -124 or
    >>(AMEX) -1745/ -124” or
    >>(PHLX) -1745/ -124”
    >>
    >>I need to write / print a report wherever it appears.
    >>
    >>Rather than write a VB program to do it, ( It seems rather complicated

    for
    >>this newbie)Does anyone know how to use Excel to sort, or filter each row
    >>for this information?
    >>
    >>Excel seems to do most of the work, but VB would be cooler...
    >>
    >>Thanks in advance;
    >>
    >>
    >>Bill
    >>
    >>
    >>

    >



  2. #2
    Patrick Marshall Guest

    Re:Elaborate on the file Excel Help...VB 6.0 if necessary...


    Hi Bill,

    The code I posted should work partially for what you have detailed. What
    is required is further processing to extract the report itself from each
    "block" of data (now represented as a variable dimension string array, BlockStrArr()).
    Also, since I now realize that the digits are a count in your block "tokens",
    your tokens should be something like:

    const kEndToken1 ="(CBOE)"
    const kEndToken2 ="(AMEX)"
    const kEndToken3 ="(PHLX)"

    or your could read these out of a table and assign them at run time.

    Once you have each block separated into BlockStrArr() with a size of
    SizeOfBlockStrArrLng you can create a subroutine which takes these as parameters
    and parse the required report data from this block. You might as well keep
    it in the array form and do array manipulation to start from the bottom elements
    and work your way upwards, e.g. the pertinent info in your report is clustered
    in the bottom 10 or 15 lines and can be parsed.

    >2LC00 VAN JAN 03 70.000 C 45 .........etc etc
    >2LC00 VAN JAN 03 75.000 C 45 .........etc etc
    >2LC00 VAN JAN 03 80.000 C 45 .........etc etc
    >** TOTAL ** 300* 30*
    >** TOTAL LONG SIDE VS. SHORT SIDE**
    >** STOCK**
    >** UNSTL**
    >** NET STK**
    > (PHLX) -71/ 1537


    >a) The account number: (2LC00 in this case)
    >b) The Stock symbol: ( AOL in this case)
    >c) The Exchange: ( PCX in this case)
    >d) The numbers in the record: ( -4571 / 1537 )


    These values seem to be available in the last block of lines, and you should
    be able to extract this data by counting columns. Just remember to work
    BACKWARDS in the array, the last element contains the (PHLX) and counts,
    by working backwards you can get the stock symbol.

    Very generally, you can use mid$ to extract a substring from an array element,
    e.g. s = mid$(BlockStrArr(SizeOfBlockStrArrLng-4),5,10) 'extracts a five
    character string from the array element 4 from the BOTTOM starting at character
    5. You can use instr to find specific positions of character strings (as
    keys to column location) and val to convert padded values such as " 141
    " to number 141.

    With a little bit of fiddling, you should be able to extract the data you
    need. Sorry I can't be more specific, but it is quite dependant on the column
    spacing on the data (it would help if you had the format specification from
    the sender of the document).

    Please note, I added a correction to the code below, so use that instead

    <Patrick>

    "Bill" <uliv@eqtc.com> wrote:
    >
    >Patrick;
    >
    >I'll try an elaborate. I have a fixed length text file emailed to me. I

    open
    >the file with Ms Word. Shrink the font, clean things up a bit and print,
    >where we have to manually go through a 200 page report everyday to
    >grab a few numbers...Anyhow the file is organized by stocks and option symbols.
    >There could be 1 or 100 lines of data. But the "key" is that
    >at the end of every "block" of symbols is last 1 to 5 records whose string
    >begins with an asterick. It looks like this:
    >
    >7 records 5 with beginning astericks:
    >
    >2LC00 VAN JAN 03 70.000 C 45 .........etc etc
    >2LC00 VAN JAN 03 75.000 C 45 .........etc etc
    >2LC00 VAN JAN 03 80.000 C 45 .........etc etc
    >** TOTAL ** 300* 30*
    >** TOTAL LONG SIDE VS. SHORT SIDE**
    >** STOCK**
    >** UNSTL**
    >** NET STK**
    > (PHLX) -71/ 1537
    >
    >or it might look like this
    >
    >5 records 3 with beginning astericks:
    >
    >2LC00 VAN JAN 03 70.000 C 45 .........etc etc
    >2LC00 VAN JAN 03 75.000 C 45 .........etc etc
    >2LC00 VAN JAN 03 80.000 C 45 .........etc etc
    >** TOTAL ** 300* 30*
    >** TOTAL LONG SIDE VS. SHORT SIDE**
    >** STOCK** AOL
    > (PCX ) -4571/ 1537
    >
    >I need nothing in the whole report except to print a report , or the results
    >of our filtering the last few lines.
    >I need the report to print out
    >a) The account number: (2LC00 in this case)
    >b) The Stock symbol: ( AOL in this case)
    >c) The Exchange: ( PCX in this case)
    >d) The numbers in the record: ( -4571 / 1537 )
    >
    >If you have any more questions, or would like to recieve a sample of the
    >text file let me know..
    >
    >
    >Thanks for taking so much time out to help me :>)
    >
    >Bill
    >
    >
    >
    >"Patrick Marshall" <nospam@nospam.com> wrote:
    >>
    >>Hi Bill,
    >>
    >>Can you elaborate on what you are trying to do? When you say "generate

    >a
    >>report..." do you mean that you are trying to break up the text file into
    >>blocks bordered by the marker row (CBOE) -1745/ -124 ? If so, this can

    >easily
    >>be done with VB (or you can automate Excel to do it to, but I don't think
    >>it would be much simpler).
    >>
    >>For example:
    >>
    >>const kEndToken1 ="(CBOE) -1745/ -124"
    >>const kEndToken2 ="(AMEX) -1745/ -124"
    >>const kEndToken3 ="(PHLX) -1745/ -124"
    >>
    >>dim FileNumInt as integer
    >>dim BlockStrArr() as string 'one block for report
    >>dim SizeOfBlockStrArrLng as long
    >>dim s as string
    >>
    >>fileNumInt = freefile()
    >>open "c:\testInpt.txt" for input as fileNumInt
    >>
    >>'NOTE use instr with vbBinaryCompare since it is MUCH faster than
    >>'the default text compare (MUST be sure that your end tokens are
    >>'always same case
    >>do while not eof(fileNumInt) '''''''''''''corrected
    >> line input #fileNumInt, s
    >> redim preserve BlockStrArr(SizeOfBlockStrArrLng)
    >> BlockStrArr(SizeOfBlockStrArr) = s
    >> SizeOfBlockStrArrLng = SizeOfBlockStrArrLng + 1
    >> if instr(1,s,kEndToken1,vbBinaryCompare) > 0 then
    >> 'insert your subroutine here to generate report
    >> GenerateBlockRept BlockStrArr(), SizeOfBlockStrArr, kEndToken1
    >> SizeOfBlockStrArrLng = 0
    >> redim BlockStrArr(0) 'blank out block for next run
    >> elseif instr(1,s,kEndToken2,vbBinaryCompare) > 0 then
    >> 'insert your subroutine here to generate report
    >> GenerateBlockRept BlockStrArr(), SizeOfBlockStrArr, kEndToken2
    >> SizeOfBlockStrArrLng = 0
    >> redim BlockStrArr(0) 'blank out block for next run
    >> 'elseif kendtoken3 etc.
    >> end if
    >>
    >>loop
    >>
    >>close #FileNumInt
    >>



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center