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
LTSVOKOKLtsv.org
JSONOKOKwww.json.org
JSONLOKOKInput is possible with JSON
YAMLOKOKyaml.org
TBLNOKOKtbln.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