StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I didn't use <code>awk</code> alone, but if I understood the gist of what you're asking correctly, I think this long one-liner should do it...</p> <pre><code>join -t, -a 1 -a 2 -o 1.1 2.1 1.2 2.2 file1.csv file2.csv | awk -F, '{ if ( $3 == $4 ) var = "\"Match\""; else var = "\"Unmatch\"" ; print $1","$2","var }' | sed -e '1d' -e 's/^,/"",/' -e 's/,$/,"" /' -e 's/,,/,"",/g' </code></pre> <p>Description: </p> <ul> <li>The <code>join</code> portion takes the two CSV files, joins them on the first column (default behavior of <code>join</code>) and outputs all four fields (<code>-o 1.1 2.1 1.2 2.2</code>), making sure to include rows that are unmatched for both files (<code>-a 1 -a 2</code>). </li> <li>The <code>awk</code> portion takes that output and replaces combination of the 3rd and 4th columns to either <code>"Match"</code> or <code>"Unmatch"</code> based on if they do in fact match or not. I had to make an assumption on this behavior based on your example. </li> <li>The <code>sed</code> portion deletes the "no","loc" header from the output (<code>-e '1d'</code>) and replaces empty fields with open-close quote marks (<code>-e 's/^,/"",/' -e 's/,$/,""/' -e 's/,,/,"",/g'</code>). This last part might not be necessary for you.</li> </ul> <p>EDIT: As tripleee points out, the above fails if the two initial files are unsorted. Here's an updated command to fix that. It punts the header line and sorts each file before passing them to join...</p> <pre><code>join -t, -a 1 -a 2 -o 1.1 2.1 1.2 2.2 <( sed 1d file1.csv | sort ) <( sed 1d file2.csv | sort ) | awk -F, '{ if ( $3 == $4 ) var = "\"Match\""; else var = "\"Unmatch\"" ; print $1","$2","var }' | sed -e 's/^,/"",/' -e 's/,$/,""/' -e 's/,,/,"",/g' </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload