Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You can achieve this by controlling the formatting of the old/new/unchanged lines in GNU <code>diff</code> output:</p> <pre><code>diff --new-line-format="" --unchanged-line-format="" file1 file2 </code></pre> <p>The input files <em>should be sorted</em> for this to work. With <code>bash</code> (and <code>zsh</code>) you can sort in-place with process substitution <code>&lt;( )</code>:</p> <pre><code>diff --new-line-format="" --unchanged-line-format="" &lt;(sort file1) &lt;(sort file2) </code></pre> <p>In the above <em>new</em> and <em>unchanged</em> lines are suppressed, so only <em>changed</em> (i.e. removed lines in your case) are output. You may also use a few <code>diff</code> options that other solutions don't offer, such as <code>-i</code> to ignore case, or various whitespace options (<code>-E</code>, <code>-b</code>, <code>-v</code> etc) for less strict matching.</p> <hr> <p><strong>Explanation</strong></p> <p>The options <code>--new-line-format</code>, <code>--old-line-format</code> and <code>--unchanged-line-format</code> let you control the way <code>diff</code> formats the differences, similar to <code>printf</code> format specifiers. These options format <em>new</em> (added), <em>old</em> (removed) and <em>unchanged</em> lines respectively. Setting one to empty "" prevents output of that kind of line.</p> <p>If you are familiar with <em>unified diff</em> format, you can partly recreate it with:</p> <pre><code>diff --old-line-format="-%L" --unchanged-line-format=" %L" \ --new-line-format="+%L" file1 file2 </code></pre> <p>The <code>%L</code> specifier is the line in question, and we prefix each with "+" "-" or " ", like <code>diff -u</code> (note that it only outputs differences, it lacks the <code>---</code> <code>+++</code> and <code>@@</code> lines at the top of each grouped change). You can also use this to do other useful things like <a href="https://unix.stackexchange.com/questions/34874/diff-output-line-numbers">number each line</a> with <code>%dn</code>.</p> <hr> <p>The <code>diff</code> method (along with other suggestions <code>comm</code> and <code>join</code>) only produce the expected output with <em>sorted</em> input, though you can use <code>&lt;(sort ...)</code> to sort in place. Here's a simple <code>awk</code> (nawk) script (inspired by the scripts linked-to in Konsolebox's answer) which accepts arbitrarily ordered input files, <em>and</em> outputs the missing lines in the order they occur in file1.</p> <pre class="lang-pl prettyprint-override"><code># output lines in file1 that are not in file2 BEGIN { FS="" } # preserve whitespace (NR==FNR) { ll1[FNR]=$0; nl1=FNR; } # file1, index by lineno (NR!=FNR) { ss2[$0]++; } # file2, index by string END { for (ll=1; ll&lt;=nl1; ll++) if (!(ll1[ll] in ss2)) print ll1[ll] } </code></pre> <p>This stores the entire contents of file1 line by line in a line-number indexed array <code>ll1[]</code>, and the entire contents of file2 line by line in a line-content indexed associative array <code>ss2[]</code>. After both files are read, iterate over <code>ll1</code> and use the <code>in</code> operator to determine if the line in file1 is present in file2. (This will have have different output to the <code>diff</code> method if there are duplicates.)</p> <p>In the event that the files are sufficiently large that storing them both causes a memory problem, you can trade CPU for memory by storing only file1 and deleting matches along the way as file2 is read.</p> <pre class="lang-pl prettyprint-override"><code>BEGIN { FS="" } (NR==FNR) { # file1, index by lineno and string ll1[FNR]=$0; ss1[$0]=FNR; nl1=FNR; } (NR!=FNR) { # file2 if ($0 in ss1) { delete ll1[ss1[$0]]; delete ss1[$0]; } } END { for (ll=1; ll&lt;=nl1; ll++) if (ll in ll1) print ll1[ll] } </code></pre> <p>The above stores the entire contents of file1 in two arrays, one indexed by line number <code>ll1[]</code>, one indexed by line content <code>ss1[]</code>. Then as file2 is read, each matching line is deleted from <code>ll1[]</code> and <code>ss1[]</code>. At the end the remaining lines from file1 are output, preserving the original order.</p> <p>In this case, with the problem as stated, you can also <em>divide and conquer</em> using GNU <code>split</code> (filtering is a GNU extension), repeated runs with chunks of file1 and reading file2 completely each time:</p> <pre><code>split -l 20000 --filter='gawk -f linesnotin.awk - file2' &lt; file1 </code></pre> <p>Note the use and placement of <code>-</code> meaning <code>stdin</code> on the <code>gawk</code> command line. This is provided by <code>split</code> from file1 in chunks of 20000 line per-invocation.</p> <p>For users on non-GNU systems, there is almost certainly a GNU coreutils package you can obtain, including on OSX as part of the <a href="https://developer.apple.com/xcode/features/" rel="noreferrer">Apple Xcode</a> tools which provides GNU <code>diff</code>, <code>awk</code>, though only a POSIX/BSD <code>split</code> rather than a GNU version.</p>
    singulars
    1. This table or related slice is empty.
    1. POFast way of finding lines in one file that are not in another?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USmr.spuratic
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POFast way of finding lines in one file that are not in another?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThis does exactly what I need, in a tiny fraction of the time taken by the enormous grep. Thanks!
      singulars
      1. PO
      1. USNiels2000
    2. COFound this [gnu manpage](http://www.gnu.org/software/diffutils/manual/html_node/Line-Formats.html)
      singulars
      1. PO
      1. USJuto
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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