Convert TSV data to LibreOffice Flat XML ODF spreadsheet (FODS)

tsv2fods

#!/bin/bash

# Converts tab-separated data (TSV) to simple LibreOffice Flat XML ODF Spreadsheet (FODS)

# One major advantage of FODS over many text-based formats is that spreadsheets will
# preserve text data accurately without trying to convert it into a number.
# e.g. 0123.0, 1/2, 2:1

# It uses awk to do the processing.  The `-F` parameter can be used to support different delimiters, e.g. `-F ','`
# But there is no support for things like commas in quoted strings.

# Usage: tsv2fods mytable.tsv > mytable.fods

awkscript='
  function esc(t) {
    gsub(/&/, "\\&", t);
    gsub(/</, "\\&lt;", t);
    gsub(/>/, "\\&gt;", t);
    return t;
  }

  BEGIN {
    print "<?xml version=\"1.0\" encoding=\"UTF-8\"?>"

    print "<office:document"
    print " xmlns:office=\"urn:oasis:names:tc:opendocument:xmlns:office:1.0\""
    print " xmlns:text=\"urn:oasis:names:tc:opendocument:xmlns:text:1.0\""
    print " xmlns:table=\"urn:oasis:names:tc:opendocument:xmlns:table:1.0\""
    print " xmlns:of=\"urn:oasis:names:tc:opendocument:xmlns:of:1.2\""
    print " office:version=\"1.4\""
    print " office:mimetype=\"application/vnd.oasis.opendocument.spreadsheet\">"

    print " <office:body><office:spreadsheet><table:table>"
  }

  {
    print "  <table:table-row>"
    for(i=1;i<=NF;i++) {
      printf("   <table:table-cell> <text:p>%s</text:p> </table:table-cell>\n", esc($i) )
    }
    print "  </table:table-row>"
  }

  END {
    print "</table:table></office:spreadsheet></office:body></office:document>"
  }
'

awk -F'\t' "$awkscript" "$@"