OpenOffice and external data in CSV

Bleh. OpenOffice has a neat feature whereby it’s possible to link a spreadsheet to external data simply by clicking Insert -> Link to External Data.

That would be great if you could link to a CSV file, which I often generate based on script output.

After the dialog pops up and a CSV file is selected is shows up in the preview, but when clicking OK, the next dialog doesn’t show anything under Available tables/ranges.

The way I worked around that was to convert the CSV file to an HTML with a table. It’s a bit baffling that an HTML file would be linkable but not a CSV, but the conversion was easy enough. Here is the script. Just use the CSV file as the only parameter and then redirect STDOUT. You’ll need double quotes around the data and have it separated by commas … or edit the code.

echo "<body><table>"
head -n1 $FILE | sed 's/^"/<tr><th>/; s/","/<\\/th><th>/g; s/"$/<\\/th><\\/tr>/;'
tail -n+2 $FILE | sed 's/,/ /g; s/"//g' | sed 's/^/<tr><td>/; s/ /<\\/td><td>/g; s/$/<\\/td><\\/tr>/;'
echo "</table></body>"


Leave a Reply

Your email address will not be published. Required fields are marked *