trdsql File format conversion
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
Format | input | Output | Note |
---|---|---|---|
CSV | OK | OK | TSV etc. correspond to options |
LTSV | OK | OK | Ltsv.org |
JSON | OK | OK | www.json.org |
JSONL | OK | OK | Input is possible with JSON |
YAML | OK | OK | yaml.org |
TBLN | OK | OK | tbln.dev |
RAW | NG | OK | Output as it is (do not process escape) |
MD | NG | OK | Markdown table |
At | NG | OK | ASCII table |
VF | NG | OK | Vertical 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