trdsql File format conversion

Posted on:

trdsql describes files such as CSV as a tool for processing SQL, but can also be used as a tool for simply converting file formats.

In that case, SQL is enough to remember the following fixed phrases. Output all rows and columns in the file.

SELECT * FROM fileName

After that, if you specify the input format (-i …) and the output format (-o …) as the option, you can convert the file format. CSV, LTSV, JSON, etc. can be converted.

The conversion from CSV (-icsv) to LTSV (-oltsv) is as follows.

trdsql -icsv -oltsv "SELECT * FROM fileName"

CSV header

If the CSV file has a column name on the header, the header can be interpreted with -ih and used as a column name.

header.csv

id,name
1,Orange
2,Melon
3,Apple
trdsql -icsv -ih -oltsv "SELECT * FROM header.csv" > test.ltsv

test.ltsv

id:1	name:Orange
id:2	name:Melon
id:3	name:Apple

If there is no header, the column name will be c1, c2, c3 …

LTSV input

If you use the LTSV output above for input, you will return to CSV.

trdsql -iltsv -ocsv -oh "SELECT * FROM test.ltsv"

id,name
1,Orange
2,Melon
3,Apple

Change of separation characters（TSV）

Also, as CSV is sometimes called Character-separated values instead of Comma-Separated Values, you can use anything other than “,” as a delimiter.

You can change it by specifying the text after the -ID option. For tab delimited (also known as TSV) use “\t”.

The following will be changed from TSV to CSV.

trdsql -icsv -id "\t" -ih "SELECT * FROM test.tsv"

JSON出力

JSON output prints the entire JSON as an array.

trdsql -icsv -ih -ojson "SELECT * FROM header.csv"

[
  {
    "id": "1",
    "name": "Orange"
  },
  {
    "id": "2",
    "name": "Melon"
  },
  {
    "id": "3",
    "name": "Apple"
  }
]

JSON input

JSON in trdsql expects a format consisting of rows and columns. One is a format with an array at the top and containing a name and a value, as in the output above.

The other is a format called NDJSON,LDJSON or JSONL, where one line is one line as shown below.

{"id":"1","name":"Orange"}
{"id":"2","name":"Melon"}
{"id":"3","name":"Apple"}

If such a column is the same, it can be entered like CSV or LTSV.

trdsql -ijson -ocsv "SELECT * FROM test.json"

(Because JSON’s objects are unprecedented, the order of columns may be different as Name, ID.)

Other output

If it is only output, it can be output as a mark -down table (equivalent to a tool called CSV2MD, JSON2MD, or LTSV2MD) because it supports more formats.

trdsql -icsv -ih -ovf "SELECT * FROM header.csv"

| id |  name  |
|----|--------|
|  1 | Orange |
|  2 | Melon  |
|  3 | Apple  |

You can display files that are long and hard to see with CSV files with many columns and display them vertically in Vertical format.

trdsql -icsv -ih -ovf "SELECT * FROM header.csv"

---[ 1]-----------------------------------------------------
    id | 1
  name | Orange
---[ 2]-----------------------------------------------------
    id | 2
  name | Melon
---[ 3]-----------------------------------------------------
    id | 3
  name | Apple

Used format

FormatinputOutputNote
CSVOKOKTSV etc. correspond to options
LTSVOKOK[Ltsv.org] (http://ltsv.org)
JSONOKOK[www.json.org] (www.json.org)
JSONLOKOKInput is possible with JSON
YAMLOKOK[yaml.org] (http://yaml.org)
TBLNOKOK[tbln.dev] (https://tbln.dev)
RAWNGOKOutput as it is (do not process escape)
MDNGOKMarkdown table
AtNGOKASCII table
VFNGOKVertical format

CSV

id,name
1,Orange
2,Melon
3,Apple

LTSV

id:1	name:Orange
id:2	name:Melon
id:3	name:Apple

JSON

[
  {
    "id": "1",
    "name": "Orange"
  },
  {
    "id": "2",
    "name": "Melon"
  },
  {
    "id": "3",
    "name": "Apple"
  }
]

JSONL

{"id":"1","name":"Orange"}
{"id":"2","name":"Melon"}
{"id":"3","name":"Apple"}

TBLN

; name: | id | name |
; type: | text | text |
| 1 | Orange |
| 2 | Melon |
| 3 | Apple |

RAW

id,name
1,Orange
2,Melon
3,Apple

MD

| id |  name  |
|----|--------|
|  1 | Orange |
|  2 | Melon  |
|  3 | Apple  |

AT

+----+--------+
| id |  name  |
+----+--------+
|  1 | Orange |
|  2 | Melon  |
|  3 | Apple  |
+----+--------+

VF

---[ 1]-------------------------------------------------------------------
    id | 1
  name | Orange
---[ 2]-------------------------------------------------------------------
    id | 2
  name | Melon
---[ 3]-------------------------------------------------------------------
    id | 3
  name | Apple