BBEdit is still one of the best text editors that you can find around.

Here I’m sharing two filters I wrote to convert TSV (tab-sparated values) into a JSON array or an XML file.

Please note that copy&paste from Excel or another spreadsheet will produce a TSV text when pasted into BBEdit.

Here we suppose that the first line, as usual, contains the name of the columns.

How to install filters

1. Download the two files attached to this page
2. Go to your user Library (Go => Go to folder => ~/Library)
3. Go to folder “~/Library/Application Support/BBEdit/Text Filters”
4. Copy there the two files
5. Use them in BBEdit (Text => Apply text filters => …)

Tips

• Headers can be marked with option flags, affecting the whole column.
  It’s done by appending or prepending the column name with a string like “{b}” (if any, its trailing and leading whitespaces are stripped out).
  E.g.: for “field_is_valid {!B}” column name is “field_is_valid”, flags are ! and B.
  Flags are case sensitive. Available flags are:
  
  • “!”: mandatory (XML, JSON)
    A column with this flag is reportes even if empty.
    The default behavior skips empty values.
  • “b”: boolean (XML, JSON)
    Values starting with “f”, “F” or “0” become false ("false()" for XML).
    When column is mandatory, empty values are mapped to false/"false()".
    Every other value becomes true ("true()" tor XML)
  • “B”: 0-1 boolean (XML, JSON)
    The same as boolean, but 0 is used for false and 1 for true.
  • “s”: string (JSON only)
    JSON converter writes number if the value only contains [0-9].
    Using this switch conversion isn’t performed: value remain always a string.
• The above flags are mutually exclusive, with the exception of “!” (mandatory).
  If using b or B don’t forget that empty values are skipped, unless mandatory.
• Regardless of any option, data lines that don’t contain a tab character are skipped.
• Lines without values are skipped (the mandatory option can affect this behavior).

JSON-conversion specific notes:

• In values «\» is replaced with «\\», «"» with «\"»

XML-conversion specific notes:

• When converting to XML a generic “item” node name is used.
  To replace it with a custom node name insert it in the first line, before headers.
• In attribute values «"» is replaced with «"», «<» with «<», «>» with «>» and «&» with «&»

Using filters without BBEdit

BBEdit’s text filters simply bind STDIN, STDOUT end STDERR to an external executable.

You don’t need actually BBEdit to run the filter as soon as you use whatever decent OS ^{(see note below)}

It’s quit easy to download the script and setup it like this:

After setup, you can always use it with the commandline:

Please note

Here above I wrote «whatever decent OS»…

• «decent» excludes that few *nix systems not including perl (i.e.: minimal install of FreeBSD and OpenWRT).
• «OS» excludes every Windows.