TWO FILES

[gcotterl]

I have a TEXTPAD file containing 3,000 rows and another TEXTPAD file containing 1.2 million rows.

There is an "identifier" in positions 1 thru 11 in each row in both files.

How can I extract, from the larger file, only the rows with the same identifiers as in the smaller file?

[talleyrand]

Everybody should know the drill by now...
Install Python and you're good to go.

Copy this code and paste into Textpad.
Save it to something like C:\sandbox\gcotterl.py
Update smallFile and largeFile's values

C:\>python \sandbox\gcotterl.py

You can also run it straight out of Texpad. Set the command to wherever you installed Python (probably C:\python23\python.exe)

Code: Select all

def compare(smallFile, bigFile):

   #create a list that will hold the identifiers
   master = []

   #Iterate through smallFile, puting the first 10 characters into our list
   for l in file(smallFile, 'r').readlines():
      master.append(l[0:10])

   for l in file(bigFile, 'r').readlines():
      if l[0:10] in master:
         print l

def main():
   smallFile = r'C:\\sandbox\\foo.txt'  #replace this with the path to the small file
   bigFile = r'C:\\sandbox\\bigOne.txt' #replace this with the path to the large file
   #I've heard of people having issues with spaces in the path names.  If so, use the
   # 8.3 compatible path name  (try dir /x)
   compare(smallFile, bigFile)

if __name__ == '__main__':
   main()

Python might chew on some memory (jumped up about 150MB) while it's running that function but seeing as how I happen to be running some Python that manipulates quite a few files between .75M and 1.5M records, it's surprisingly fast. I was able to do all my magic on a file of .735M (read each line, manipulate and write to a new file) in 2 minutes in 10 seconds. By the way, bulk loading into SQL server only took a minute after that versus the 33 minutes it took to perform row level inserts for each record---glad I looked into that!

If that doesn't trip your trigger, you could try something like defining ODBC text data sources and the run an inner join between them. I think Excel has some query tools as an add on. OpenOffice probably does too. There's also Access (ick) and other smaller databases that would accomplish it quite nicely. The SQL would be something like

Code: Select all

SELECT
    BIG.*
FROM
    BIG_TABLE BIG
    INNER JOIN 
        SMALL_TABLE SMALL
        ON SMALL.primary_key = BIG.primary_key