awk Tutorial, Part 2

Every now and then, as a programmer, you get data from two sources that have to be merged together. That is actually one of the standard tasks in my job. awk is very flexible for this kind of work and thereby saves me a lot of effort.
Often you also get the CSV data in varying quality and format. Because CSV (Comma Separated Values) is not just CSV. In the first part of my mini-tutorial we had a tab-separated CSV (occasionally also called TSV).
Besides tabs, semicolons are common too. I favour two consecutive pipes (||); since they really do occur very rarely in text, they are ideally suited.
As we have already learned how to change the field separator in awk, the different formats are no problem. This time, as an example, I want to make the product database of the Copyleftstore (plug, plug) fit for Froogle. To the products in one text file I want to add the links to their images and their product pages from another text file.
Merging two sources requires somewhat more elaborate code and the use of the BEGIN and END clauses, which I already introduced in the first part.
In the BEGIN clause we load the product catalogue that we have defined in the variable source.

BEGIN {
  #Artikelstamm laden

  #Abbruch wenn Quellenangabe fehlt
  if (source == "") {
    print "Es ist keine Quelle angegeben";
    exit;
  }

  #Erste Zeile aus Quelle laden
  eof = (getline line < source );

  #Zeile für Zeile Datei durchlaufen
  while (eof > 0) {

    #Input aus Datei aufsplitten in temporäres Array
    split(line, product, “\t”);

    #Array-Index inkrementieren
    catalog_count++;

    #Array füllen
    catalog[catalog_count, 1] = product[1]; #offer_id
    catalog[catalog_count, 2] = product[2]; #name
    catalog[catalog_count, 3] = product[3]; #description
    catalog[catalog_count, 4] = product[4]; #price
    catalog[catalog_count, 5] = product[5]; #category
    #Wenn kein Bildchen gefunden wurde, dann wird_
eben das Standardbildchen der Kategorie verwendet.
    catalog[catalog_count, 6] = “http://copyleftstore.de/images/”_
tolower(product[5]) “.png”;

    #Nächste Zeile aus Quelle laden
    eof = (getline line < source );
   }

  #Quelle schließen
  close(source)  ;

  #Für nächste Klausel Field-Seperator festlegen.
  FS = "\|\|";
}

We have now packed the contents of source into an array. In the next clause we load the image information from images.txt line by line and check in a for loop whether we can find the matching product in the array. Of course you could also load the images first and then the products.

{
  #Images in Array nach und nach einpflegen

  #offer_id||image_url

  for (i = 1; i <= catalog_count; i++) {
    if (toupper($1) == toupper(catalog[i, 1])) {
      catalog[i, 6] = $2;
      break;
    }
  }
}

Finally we print the prepared array, complete with header, to STDOUT.

END {
  print "offer_id\tname\tdescription\tprice\tcategory\t_
image_url\tproduct_url";

  for (i = 1; i <= catalog_count; i++) {
    printf "%s\t%s\t%s\t%s\t%s\t%s\thttp://copyleftstore.de/_
product_info.php?language=de&products_id=%s\n",
    catalog[i, 1],
    catalog[i, 2],
    catalog[i, 3],
    catalog[i, 4],
    catalog[i, 5],
    catalog[i, 6],
    catalog[i, 1];
  }
}

We start the whole thing with the following command line. It passes the variable source via the -v option switch.

awk -v source=produktkatalog.txt -f froogle.awk_
images.txt > copyleftstore.txt

Done, the final result can now be uploaded to Froogle.
The icing on the cake would be to optimise the code so that the little for loop only runs through the lines that have not yet been assigned an image. That would be possible by printing directly to STDOUT. Products that have already been matched would be deleted from the array, which would make the for loop faster and faster towards the end. In END only those articles would then be printed for which no image was found. The advantage is obvious: with a product range of just 41 products the performance is fine. With 410,000 it is a different story.