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