Search, sort, then output into csv format

Hi richsark,

Q1. When you say:
   "...to a matching  txt record on the 4th row ( text*)"
do you mean this?:
   "...to a matching  txt record on the 4th column ( text*)"

Q2. What is the expected output for your sample input data?

tel2

...and a 3rd question:

Q3. How can you match the DNS Name to the text* column?  The DNS Name seems to contain things like:
    dcdiag-test-record.corp.sark.com
while the text* column seems to contain things like:
    31f8d425a979fff1cbc064fedc6af8cd7f
so they won't match.
Do you mean the DNS name should be matched against column 2, i.e. _new_fqdn,text*?

Hi tel 2:

Q1. When you say:
   "...to a matching  txt record on the 4th row ( text*)"

Yes

Q2. What is the expected output for your sample input data?

Same as file 2

header-txtrecord,fqdn*,_new_fqdn,text*,_new_text,comment,disabled,ttl,view,EA-Site
txtrecord,dcdiag-test-record.corp.sark.com,,31f8d425a979fff1cbc064fedc6af8cd7f,,,FALSE,3600,Internal-RPZ,
txtrecord,omniviewwebprd.corp.sark.com,,00a151deec7b2c49fcb106daab945e376f,,,FALSE,3600,Internal-RPZ,
txtrecord,zbr4185224.ocr,,00444e8f6a21e53453f0e11a6d6f1e9f67,,,FALSE,3600,Internal-RPZ,

Select allOpen in new window

@Bill Prew
I will try your awk too

Q4. If the expected output is the same as one of the input files, then why do you need a script?  You already have what you need.

Please also answer Q3 in my 2nd post.

Hi. The reason is I have a short list in my file one. Which i need it matched to the file 2 which has the master list per say. Once all the matching is done spit out a modified version of all the matches that looks like file 2.

In that case, the output will not be the same as file 2.  It will have less records, so the sample output data you provided is incorrect.  Please correct it.  I suggest you always provide sample input data and matching expected output data in your original post, when you ask questions in future, so experts don't have to guess, and can confirm they have understood your requirements by looking at the output data, and comparing the output of their proposed solutions with it.  This can also help experts to get past any errors you've made in your specifications, like those I have raised.  So, you may get your correct solution faster, and it can save a lot of time for experts who are volunteering their precious time.  So, please answer Q3 again.

And I'm still waiting for your answer to Q4.

Q5. Do you have the Unix/Linux "join" command on your Mac?

Assuming the answers to my questions are what I'm guessing they are, and assuming you have the "join" command on your Mac, this should work:

#!/bin/bash

head -n1 DNS-record.csv >DNS-record.csv.srt    # Store header
tail -n+2 DNS-record.csv | sort -t, -k1,1 >>DNS-record.csv.srt    # Append sort of all except header
head -n1 Global-text.csv >Global-text.csv.srt    # Store header
tail -n+2 Global-text.csv | sort -t, -k2,2 >>Global-text.csv.srt    # Append sort of all except header
# Join on 1st file field 2 & 2nd file field 1, outputing columns 1-10 of 1st file only
join --header -t, -12 -21 -o1.1,1.2,1.3,1.4,1.5,1.6,1.7,1.8,1.9,1.10 Global-text.csv.srt DNS-record.csv.srt >output.csv
rm DNS-record.csv.srt Global-text.csv.srt    # Optional clean up

Select allOpen in new window

Here's the contents of output.csv after running the above:

header-txtrecord,fqdn*,_new_fqdn,text*,_new_text,comment,disabled,ttl,view,EA-Site
txtrecord,dcdiag-test-record.corp.sark.com,,31f8d425a979fff1cbc064fedc6af8cd7f,,,FALSE,3600,Internal-RPZ,
txtrecord,omniviewwebprd.corp.sark.com,,00a151deec7b2c49fcb106daab945e376f,,,FALSE,3600,Internal-RPZ,

Select allOpen in new window

But Bill's should basically do the same, and might even be slightly more efficient because his doesn't need to do any sorting, creation of temporary files, etc.

Thanks guys as always for your expertise

Welcome, glad that was useful.

---

»bp