1. Build an SQLite DB from the file of 120,000 records.
No problem at all. This will take <1 second for a file with 120K lines.

1.5) Index the DB. This step is not to be under estimated and was not mentioned.

2. Perform 5000 SQL queries -- ...Ooooh...I did not say what kind of queries nor how many...But essentially, yes you can do a lot of these things per second, provided that you have indexed the DB in step 1.5 correctly. Some seconds will be required for this step.

--Steps 3,4,5 are super trivial reformatting steps...

3. Convert all the results records, from each of the 5000 queries, back to CSV records.

4. Write them out to the new file.

5. (Delete the SQLite DB you created!)

Update:

Creation of a DB with 266,551 lines from >600+ source files took 78 seconds.
Fancy indexing took another 20 seconds.
Basically 100 seconds and I'm "ready to roll" with 1/4 million lines.

I have a whimp "Prescott" machine. This is more than a decade old.

In reply to Re^3: Best way to match a hash with large CSV file by Marshall
in thread Best way to match a hash with large CSV file by alphavax

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.