Using Emacs to find and copy relevant database records

Question

I have reduced a large text file to a 1.9GB size that Microsoft Access can handle, but only as a linked file. To permit full Access operations - to import the relevant data into Access - I need to extract, from that file, only the potentially relevant records.

It has been suggested that the extraction process may have to be done by hand (i.e., through the use of code that proceeds line-by-line). I am wondering whether Emacs could provide a solution. This thought arises from the belief that I could have scripted WordPerfect for DOS to do it, back when I knew how to do that - though I suspect WP would gag on a 1.9GB file.

Each line in the 1.9GB file contains three tab-delimited fields. Example:

03d32aab-6041-49e8-8fc7-8af091b005d5    Seek Text A3    11
ee8029d3-7f4a-4132-8fda-12c5327a47e9    Seek Text A1    11

The list of items to extract consists of sorted data matching field 2. Example:

Seek Text A2
Seek Text A3

In that example, Emacs would look for an occurrence of "Seek Text A2" in the 1.9GB file, but would not find it. But it would find "Seek Text A3," and would therefore copy the relevant line (i.e., "03d32aab-6041-49e8-8fc7-8af091b005d5 Seek Text A3 11") to a separate file - which, at the end, would be small enough to import into Access.

I realize this could be slow in Emacs. I welcome suggestions for a faster way. But I don't mind letting the machine spend a day at it, if there is a reasonable Emacs solution.

I think my relevant Emacs skill only ever extended to simple macro recording (i.e., remember what I type). This instance seems to call for if-then logic. When I search for guidance in that, I find myself looking at material on Lisp programming. At present, that's a bit over my head.

Answer 1

While, as others have pointed out, other tools might be more suitable for this job, it can certainly be done with Emacs. 1.9GB is not an unreasonably large file size for Emacs to handle on a somewhat modern computer.

Using Emacs Lisp

There is a built-in command copy-matching-lines which takes you some of the way, but I believe the below custom command would be faster for your purposes:

(defun my-copy-matching-lines (small-infile big-infile outfile)
  (interactive
   (list (read-file-name "Small file containing list of items to extract:\n")
         (read-file-name "Large text file:\n")
         (read-file-name "Output file:\n")))
  (save-excursion
    (find-file small-infile)
    (let ((match-strings (split-string
                          (string-trim
                           (buffer-substring-no-properties
                            (point-min) (point-max)))
                          "\n")))
      (while match-strings
        (let (line)
          (find-file big-infile)
          (goto-char (point-min))
           (when (search-forward
                 (concat "\t" (pop match-strings) "\t")
                 nil t)
            (setq line (buffer-substring-no-properties
                        (line-beginning-position)
                        (line-end-position)))
            (find-file outfile)
            (insert line "\n")))))
    (find-file outfile)
    (save-buffer)))

(call-interactively #'my-copy-matching-lines)

To use this, copy the code above into a file with an .el extension, like mylisp.el and open it in Emacs. You should now have a menu bar item called "Emacs-Lisp". Click it to reaveal a sub-menu with an entry called "Evaluate Buffer", which you should click. You will be asked for a file path/name three times:

1. The first time, navigate to the presumably smaller file containing the list of items to extract. On my system I now have to, confusingly, press save and confirm that I want to replace the file (it still works as expected otherwise).
2. The second time, navigate to the large 1.9GB file, and
3. the third time input a new file to save the output. (You might additionally have to confirm that you really want to open the 1.9GB large file.)

The code I shared makes some assumptions and might have to be adapted to fit your exact use case:

• It assumes that the list of items to extract contains one item per line, with "\n" as linebreak character, and should exactly match field two. A partial match would not return anything, since the field separators, which are assumed to be tabs "\t", are included in the search string. You might want to remove the "\t"'s above to allow partial matches, or replace them with spaces " " if the file doesn't contain actual tabs.
• It only returns the line of the first match per item, so if there are more matches these won't appear in the output file.

Using keyboard macros

An alternative, if you want to avoid writing Emacs Lisp, is to record a keyboard macro. Type M-x kmacro-start-macro-or-insert-counter or <f3> to start recording a macro and M-x kmacro-end-or-call-macro or <f4> to stop. M stands for the meta key (and C for control).

Doing so will record a macro in the "Emacs command language". The macro given below will do almost the same thing as the Lisp code above (it doesn't loop), assuming you have the three files as described above open in Emacs, with names "insmall.tsv", "inbig.tsv", and "outfile.tsv", and that you are visiting the "insmall.tsv" file when calling the macro:

C-SPC                   ;; set-mark-command
C-e                     ;; move-end-of-line
s-c                     ;; ns-copy-including-secondary
C-a                     ;; move-beginning-of-line
C-x b                   ;; switch-to-buffer
inbig.tsv               ;; self-insert-command * 9
RET                     ;; newline
M-<                     ;; beginning-of-buffer
<<copy-matching-lines>> ;; copy-matching-lines
TAB                     ;; indent-for-tab-command
s-v                     ;; yank
TAB                     ;; indent-for-tab-command
RET                     ;; newline
C-x b                   ;; switch-to-buffer
outfile.tsv             ;; self-insert-command * 11
RET                     ;; newline
s-v                     ;; yank
C-x b                   ;; switch-to-buffer
insmall.tsv             ;; self-insert-command * 11
RET                     ;; newline
C-n                     ;; next-line

To use this you can record a macro replicating the above yourself (note that <<copy-matching-lines>> stands for M-x copy-matching-lines). Alternatively you can just record a shorter non-empty macro (so that there is something to edit), then call M-x kmacro-edit-macro or C-x C-k RET, paste the above macro, and press C-c C-c to finish. You can now call your macro N times by typing C-u N M-x kmacro-end-or-call-macro or C-u N <f4> when your cursor is on the line at which you want to start in "insmall.tsv".

Answer 2

I am sharing my solution mainly for the comparison with AWK (see comments under NickD's answer), but also because it takes a slightly different approach than the solution by @orgtre:

This solution 'iterates' over the lines, and on each line checks for a match with any items in the sorted-data:

(defun get-database-records (file sorted-data destination)
  (let ((target-buf (get-buffer-create "results"))
        (data (with-temp-buffer
                (insert-file-contents-literally sorted-data)
                (split-string (buffer-string) "\n" t))))
    (with-temp-buffer
      (insert-file-contents-literally file)
      (while (not (eobp))
        (let* ((line (thing-at-point 'line))
               (fields (split-string line "\t" t)))
          (when (member (cadr fields) data)
            (princ line target-buf)))
        (forward-line)))
    (with-current-buffer target-buf
      (write-file destination))))

I also benchmarked NickD's solution and compared it to my Emacs solution. For that I ran both solutions on a file of 20 million (+ 2) lines (a file of 1.1GB). AWK finished the job in 28 seconds vs Emacs in exactly 3 minutes (180 seconds).

Here I/we only used sorted data of 2 items. I guess when using a larger set of sorted data the (relative) difference becomes even larger.

So I guess NickD is totally right when he mentions that it makes more sense to ask this question on the right 'forum' (this is no criticism, just I think Nick is right).

Answer 3

Using Emacs for this problem is just not a good way to go about it. You can learn enough about awk in a few hours to deal with such problems and the advantage is a short program that runs relatively fast and can deal with large files, since it really only has to keep one line of the file in memory at any one time.

For example, here is a complete awk script to do what you want:

BEGIN { matchlist["Seek Text A2"] = ""; matchlist["Seek Text A3"] = ""; FS="\t"; }
      { if ($2 in matchlist) print $0; }

It consists of one line of initialization (remember all the matching patterns in the matchlist associative array and set the Field Separator to a TAB) and one line of processing which is implicitly applied to every line of the input file (see if field 2 is in the matchlist array and if it is, print the line). You run it like this:

awk -f script.awk -- input.txt

where script.awk contains the two-line script above and input.txt is your 1.9GB file. I duplicated your two-line input file a number of times to produce a 2GB file and then ran the above script: on my machine, it took 48 seconds to go through the whole file (and the output is half the input file's size: just the A3 lines). How long would the macro in the answer above take to go through the whole file?

I hope you stop asking such questions here: since you can run these tools on your box, the appropriate place is the Unix & Linux SE and the appropriate tool is awk (or perhaps perl or python or sed or some more specialized tool that already exists). Use the proper tool for the job.