I'm trying to load some data I download from my real estate system. They have split out the features in a separate file. Here are my 2 files:
File1.txt (~25,000 records)
File2.txt (~1,300 records)
I need to do a couple of thing which I have done with awk. Combine fields 1, 2, and 3 and extract 4, 5, 6, and 7 for mysql insert. Problem is I need to change the values of field 6 and 7 to their matching value in file2.txt
So my insert looks like this now
INSERT INTO DB_TABLE VALUES ('123 Main St', 'City', 'State', '1701,1708', '800,801');
but it needs to be:
INSERT INTO DB_TABLE VALUES ('123 Main St', 'City', 'State', '1 Car, Door Opener', 'City Sewer, City Water');
I think I can do it easily with a multi-dim array but I don't know how to read in file2 in the same awk. I could load these into the database and do it but I have about 25,000 records in file1.txt nightly and I think it would be much faster just do this in some shell scripting.
Any ideas? I guess another option would be python, perl, c, etc. I thought maybe sed on the output file, but I still have 2 files to read.