awk is a splendid Unix scripting language for processing text files. The version included in most Linux distros is GNU awk, or gawk for short. I like it for pulling data from ordered data sets, such as text lists and CSV exports from spreadsheets. awk sees each line in a file as a separate record, and each item in a line as a separate field, which makes it possible to slice and dice your files in all kinds of flexible ways. The classic way to illustrate this is with /etc/passwd; this example prints the whole contents:

$ awk '{ print $0 }' /etc/passwd 
root:x:0:0:root:/root:/bin/bash
daemon:x:1:1:daemon:/usr/sbin:/bin/sh
bin:x:2:2:bin:/bin:/bin/sh
sys:x:3:3:sys:/dev:/bin/sh

print with no options means “print the whole line”, and $0 mean “whole line”, so omitting $0 gives the same result. Now suppose you want just a list of usernames and UIDs:/etc/passwd is perfect for parsing with awk, because its data fields are delimited with colons. So all you do is count from the left starting with 1 to number the fields you want, and then extract the usernames and UIDs like this:

$ awk -F":" '{ print $1 " " $3 }' /etc/passwd 
root 0
daemon 1
bin 2
sys 3

-F defines your field separator, and " " inserts a space. What if you want the UIDs listed first? Easy peasey, just move the variables around like this:

$ awk -F":" '{ print $3 " " $1 }' /etc/passwd

Now let’s look at a real-life example of using awk to migrate a big wad of financial data into GnuCash.

Importing Data into GnuCash

GnuCash is one of the crown jewels of FOSS, a brilliant and powerful accounting program that runs on Linux, Mac, and Windows. You can import QIF and OFX files, but there is only partial support for CSV imports. Which is a shame, because CSV (comma-separated values) makes a good universal format for moving financial data from one program to another, and many financial applications support CSV import and export.

Let’s say you have this giant spreadsheet containing years of financial data, and you decide it’s time to put it in a proper accounting program. Or you want to move from a different accounting program into GnuCash, and it doesn’t support QIF or OFX, but it does support CSV export. You could retype all your data, or you could put your ace Linux scripting skills to work. The workflow is to first create a good clean CSV file, convert that to QIF, and then import the QIF into GnuCash. GnuCash is very picky about having a perfectly clean QIF file with no errors, so we’ll make sure to have that.

Make sure you work from a copy of your source file! Don’t muck up your original!

I’ll keep this example simple and use only the following QIF fields:

• D – date
• P – payee
• M – memo
• T – amount
• N – check number, or any notation in the check number field
• L – category, which corresponds to GnuCash accounts
• ^ – end of record

We’ll also need to specify the account type in the QIF file header, like these examples:

!Type:Bank  
!Type:Cash  
!Type:CCard  
!Type:Invst

The QIF specification supports a lot more items, and you can see the full details here. If this page ever disappears just do a search for “qif spec” as it is abundantly documented.

Spreadsheets are fab for making mass changes, like date and number formats, so take a look at your CSV in a spreadsheet before trying to convert it to QIF. Make sure that your withdrawals have a minus sign, like -33.72, and don’t use dollar signs. Deposits can have a plus sign if you prefer, but it’s not required. All of your withdrawals and deposits need to be in a single column. This is what my final trimmed-down CSV export looks like:

11/03/2008  Copy Junction  Copy of building codes	-33.72	8732  Supplies	
11/03/2008  Home Depot	Trowel	-17.05	8734  Tools	
11/03/2008  Dewalt Service Center	Charger for Drill  -75.85  8735	Tools	
11/04/2008  Building Supply  Margin trowel  -13.23  8736  Tools	
11/05/2008  Jane Smith   invoice #5843	8,500.00	   dep   income:contracting

If there is even a single error anywhere in the QIF file the GnuCash import will fail. Some errors I’ve run into are multiple minus signs, like –33.72, extra decimal points, and incorrect date formatting. awk won’t care but GnuCash will. Alrighty then, let’s convert our nice CSV into a QIF file:

$ ( echo ‘!Type:Bank’; cat exportfile.csv | awk -F $’t’ ‘{ print “D” $1; print “P” $2; print “M” $3; print “T” $4; print “N” $5; print “L” $6; print “^”; }’ ) > importfile.qif

And the result looks like this, with withdrawals indicated by minus signs and deposits with unsigned values:

!Type:Bank
D03/25/2008
PJane Smith
M invoice #4657
T4000.00
Ndep
Lincome:contracting
^
D04/02/2008
PFirst Bank of Money
MCheck Order
T-21.44
NACH
Lbank fees
^
D05/15/2008
PPretty Designs
MDesign Services
T-500.00
N8922
LContract Services
^

Note how to specify a tab field delimiter by using the normal ASCII escape sequence for horizontal tabs, t. If all goes well you’ll have a good error-free QIF file to import into GnuCash. You can import this into a blank new GnuCash book, or into an existing book, and you’ll map the QIF categories to GnuCash accounts.

Find Blocks of Text

awk is wonderful for finding blocks of text, and you might give it a try when grep isn’t picking up exactly what you want. For example, you can snag the complete lspci output for a single device:

$ lspci -v | awk '/VGA/,/^$/'
01:00.0 VGA compatible controller: NVIDIA Corporation G98 [GeForce 8400 GS] (rev a1) (prog-if 00 [VGA controller])
        Subsystem: Micro-Star International Co., Ltd. Device 1162
        Flags: bus master, fast devsel, latency 0, IRQ 18
        Memory at fd000000 (32-bit, non-prefetchable) [size=16M]
        Memory at d0000000 (64-bit, prefetchable) [size=256M]
        Memory at fa000000 (64-bit, non-prefetchable) [size=32M]
        I/O ports at dc00 [size=128]
        [virtual] Expansion ROM at fe9e0000 [disabled] [size=128K]
        Capabilities: 
        Kernel driver in use: nvidia
        Kernel modules: nvidia_current, nouveau, nvidiafb

The caret, ^, is a regular expression anchor that matches the start of a string, and $ matches the end, so in this example /^$/ looks for the line breaks at the beginning and end of the text block. This is a great trick for extracting specific blocks from large configuration files with spaces between sections, like this example from sshd_config :

$ awk '/X11Forwarding/, /^$/' /etc/ssh/sshd_config 
X11Forwarding yes
X11DisplayOffset 10
PrintMotd no
PrintLastLog yes
TCPKeepAlive yes
#UseLogin no

Remove Duplicates Without Sorting

The sort and uniq commands are the ones we usually turn to for finding and removing duplicate entries in a file. But maybe you don’t want your source file sorted or changed, so this is where trusty awk comes to the rescue by extracting the unique records and storing them in a new file:

$ awk '!x[$0]++' filewithdupes > newfile

Your original file won’t be changed, and the new file will have only unique entries all nicely in order.

man awk contains complete documentation of options, and to get the most out of awk (or any Unix/Linux command) you need a good grasp of regular expressions, and for this I recommend the wonderful book Mastering Regular Expressions. If you want to pick the most useful thing of all to learn, learn regular expressions, because most programming languages and Linux/Unix commands rely on regular expressions.