No announcement yet.

Extract data between two lines in a text file with vba

  • Filter
  • Time
  • Show
Clear All
new posts

  • Extract data between two lines in a text file with vba

    Dear Experts,

    I have unsuccesfully tried for a while to come out with a code that enables me to open files in any given line other than have always to start from the first one and go down until finding a tag.

    I need that piece of code because I'm dealing with 20MB files with thousands of lines. I have a code that does what I want but it takes ages when the data I want to retrieve is close to the end of the file.

    I had a look and haven't find any code that allows me to pass onto a variable data from a range of lines in a text file.

    I'm attaching a text file example to show the structure of my data. Every block of data comprises three subblocks and blocks are separated by a blank line. In order to being able to identify blocks and subblocks I'm,at present, tagging with "class(j),tag(i)" the beginning and end of everysubblock.

    May code populates 3 variables (one for each subblock) for any block (tag) defined by the user.But as I said it takes ages when it has to look for the tag 2000 in 50 files.

    any help would be much appreciate it

    Please let me know if clarification on my data is needed
    Many thanks,
    Attached Files

  • #2
    Re: Extract data between two lines in a text file with vba


    If I understand your post correctly, your desire is to be able to open a text file and jump directly to the Nth line, bypassing all of the reads and I/O currently required, and end up where that line is the one that corresponds to specific tag and class criteria...correct?

    If so, given that input file is a text file, I am pretty sure you won't be able to find a direct, code-based solution since the input is essentially a sequentially organized series of bytes. I am not an expert in VB/VBA or Excel, but I am an expert programmer/analyst in general. I believe what you are wanting to do will require a more sophisticated data organization, i.e. some sort of indexing or hashing organization which implies using a hierarchical or relational data base management system. There might be one or two other ways to skin your particular cat, but it would mean changing how your main input file(s) are generated. Are you more or less in control of this part of the process?

    Of course this site IS chock full of Excel/VB/VBA experts, so if they feel I have steered you wrongly suggesting there is not a way to do this with your current approach, I am sure they will chime in.


    • #3
      Re: Extract data between two lines in a text file with vba

      I'd agree it would be better in a database but at a pinch you could probably open it using Jet into an ADO recordset and then use Find operations to jump to the tag you want and read fom there.
      Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why


      • #4

        Re: Extract data between two lines in a text file with vba

        Thanks both of you for your feedback.

        AA040371 you are right about what I try to do. I do have control in how the text file is generated but so far creating a text file is the only solution I found to another problem I have, but I'll start another thread to solve that.

        rory, I'll have a look at what you propose but I really donīt understand how ADO recordset works. anyway, would that imply to "transfrom" the text file into a recordset or generate a recordset instead of the text file?