JsonCSV file format

Documentazione •
In questo articolo

JsonCSV (Json Comma Separated Value) is plain text file format for tabular data. It unites the advantages of JSon and the CSV format and make importing and exporting data  more reliable.

JsonCSV for tabular data

JsonCSV (Json Comma Separated Value) is plain text file format for tabular data. It is realiable to read and with support for multiple tables.
JsonCSV is CSV for the internet age, it brings together the advantages of JSon and the CSV format (Comma Separated Value)

  • Reliable for reading and writing (JSon based data format and can use the JSon parser and writer).
  • Easy for human reading (maintains the characteristics of CSV)
  • It allows mixing data and metadata (header, column info, descriptions, formats etc.).
  • Multiple tables can fit in the same file.

This is the JsonCSV file in the simplest form. An header row, followed by the data rows.
Each line is written in a JSon data format.

{"comment":"Example of JsonCSV format"}
{"column-names":["Section","Group","Account","Description","Boolean","BClass","Gr","Opening","Balance"]}
["","","1000","Cash on hand",true,"1","10",100,1290.3]
["","","1020","Bank account",false,"1","10",0,10]
["","","2000","Suppliers or Creditors",false,"2","20",-50,-50]
["","28","","Equity",false,"","2",-250,-1450.3]

There are 3 type of rows:

  • Metadata rows, within {} brackets, in the form of a valid JSon Object in compact form, followed by end of line.
    In the above example the "column-names".
  • Data rows, within [] brackets (JSon Array), followed by end of line.
    This is the CSV data, but formatted as a valid JSon Array.
    The data contains the value in the same sequence as the columns headers. 
  • Other rows, that are ignored.

JsonCSV with data and metadata

JsonSCV allows to embed in the same file the data and metadata, like table and column name, description, format and attributes, in a very simple form, without requiring a schema file.

  • JsonCSV is a better format for archiving tabular data.
  • JsonCSV simplify the exchange of data .

The advantages of JsonSCV comes from the possibility to embed any metadata in JSon.

  • Encoding specification.
  • File information.
  • Table information. It is therefore possible to include more tables in the same file. 
  • Columns sequence.
  • Field information.
  • Unlimited expansion. Any metadata information, specific to the application or the data row, can be embedded.
  • It is also possible to insert any other text. All information not within {} or [] is ignored. 

JsonCSV references

The JsonCSV is based on the accepted standards.
See the reference documentation:

JsonCSV format specification

See example below.

  • Use the UTF8 format
  • JsonCSV file is composed of text lines terminated by the "\n", LF:Line Feed, U+000A.
    • The "\r\n" should be supported in reading, but the "\r" alone is not considered as line breaking.
    • When writing the \r should be omitted.
    • The LF, Line Feed character should be used only at the end of the line and not within the data or metadata line.
    • In the data or metadata line, replace the LF char with the LS:Line Separator, U+2028 or any appropriate character you prefer.
  • Metadata lines
    • They need to be valid Json Objects (JSon document), in compact format (No space and no end of line).
      Meta data line start "{" and end with the  "}" brackets.
    • Metadata lines comes before the data.
      • {"table":"Accounts"}  data that follows will be considered belonging to the "Accounts" table.
      • Other metadata like row attributes {"row":{"styleNumber":1144}} come before the data row.
    • Reserved metadata keyword:
      • "jcsv" with version and encoding.
      • "kind" is a property that uniquely identify the data, so that it make easier to understand what kind of data is contained in the jcsv file and apply appropriate transformation.
      • "table" contain the name of a table.
        All rows following the "table" are considered to belong to this table.
      • "schema" a url to a document that contains the infeormation necessary to verify the document or the table.
      • "column-names" is a required element that contains a Json Array with the columns names.
        The data rows following the columns are considered to be in the sequence of the columns name.
        A "column-names" that does not follow a "table" is considered to start a new table.
      • "columns" contains information relative to the columns.
        The columns information is not necessarily in the sequence of the data, so the  "columnsNames" should always be present.
      • "comment" for entering comments
      • "row-attributes" contain supplementary information regarding the following data row.
  • Data lines.
    Thex need to be Json arrays in compact form (no space or line feed).
    • Data lines start with the "[" and terminate with the "]" an contains the row data.
    • The array size need to be the same size as the preceding column-names.
    • Data is stored in Json format.
      • String are between ".
        • Usual string "Bank account".
      • Date, Time and Timestamp are Json string in the ISO date format 8601
        • Date  "2018-01-03".
        • Time "10:18:21.000".
        • Timestamp "2016-11-19T09:52:39".
      • Number in valid Json format, decimal separator "."
      • true or false.
      • null.
      • Other lines not being a valid Json Object or array (like empty lines, text or else) are not considered.

Examples

In this example it used the column-datatypes to specify the datatype of each column.


/* text that is not within {} or [] is ignored */
{"jcsv" : {"version" : "1.0", "encoding":"UTF-8"}}
{"comment": "Example JsonCSV file"}
{"kind" : "banana.ch/testfile/test"}
{"fileinfo":{"Application":"Banana","Application version":"8.0.4.160915"}}
{"table":"Accounts"}
{"column-names":["Section","Group","Account","Description","Boolean","BClass","Gr","Opening","Balance"]}{"column-datatypes" :["string","string","string","string","boolean","number","string","number","number"]}["","","1000","Cash on hand",true,"1","10",100,1290.3]
["","","1020","Bank account",false,"1","10",0,10]
["","","2000","Suppliers or Creditors",false,"2","20",-50,-50]
{"row-attributes":{"styleNumber":1024}}
["","28","","Equity",false,"","2",-250,-1450.3]

{"table":"Transactions"}
{"column-names":["Date","Time","Doc","Description","AccountDebit","AccountCredit","Amount"]}
{"column-datatypes":["date","time","string","string","string","string","number"]}
["2018-01-03","10:18:21.000","1","Cash to Bank","1020","1000",10]
["2018-02-02","23:55:00.000","2","Sales","1000","3400",1200.3]

In the following example it is used the "columns" specification with metadata information regarding each column.


{"table":"Accounts"}
{"column-names":["Account","Balance"]}
{"columns":[{"name":"Account", "datatype":"string","titles":"Account"},{"name":"Balance","titles":"Balance", "datatype":{"base":"number","scale":1}}]}
["1020",10]
["2000",-50]

JsonCSV files

  • Mime type "text/jcsv".
  • File extension ".jcsv".
  • Encoding "UTF-8".

Advantages and limitation of JsonCSV format

Advantages of JsonCSV file format over CSV

  • JsonCSV follows the JSon format.
    • There is just one way to write and read the data. No more doubts about encoding, fields separators, number format, decimal separator.
    • For generating and reading a JsonCSV it is possible to use the Json libraries, avoiding therefore escaping error that you find typically on CSV. .
  • JsonCSV can contains data of different tables.
  • Supplementary information relative to the structure or the attributes of the rows does not interfere with the data.

Advantages over Json

  • In JsonCSV each line is a unique json document, independent from the other lines.
    • It is possible to add lines to an existing document (Append mode).
    • It is possible to parse the lines individually.

Limitation of JsonCSV

  • Like CSV the JsonCSV format can be used only to exchange tabular data and not nested data structure (like Json).

Try the JsonCSV format

You can try a new format by installing the Banana Accounting software.

  • See JsonCSV example files on Github\BananaAccounting
  • Open or drag a JsonCSV file in Banana
    Banana will create a new file tables and columns. You can the copy and paste the data in Excel.
  • Export in JsonCSV
    • Export of a single table
      Menu Data -> Export Rows->JCsv 
    • Export of all the tables
      Menu File -> Export ->JCsv

Using Json library to generate or read the JsonCSV format

Generating JsonCSV files

  1. Create a Json Object that contains and Json Array with the columns name and convert to Json text, plus the line feed.
  2. For each data row create a JSon array that contains the data and convert to Json text, plus the line feed.

Parse JsonCSV files

  1. Read each line.
  2. If lines start with "{" and end with "}" parse as JSon and extract the values.
  3. If lines start with "[" and end with "]" parse as JSon and get the data.

Javascript example for generating and parsing JsonCSV



// Create and parse JCSV data in Javascript
// header
var text = JSON.stringify({"column-names" : ["Date", "Name", "Amount"]}) + "\n";
// Data row
text += JSON.stringify(["2018-01-24", "John Smith", 1200.10]) + "\n";
text += JSON.stringify(["2018-12-31", "Maria Callas", -200]) + "\n";

// parse JCSV data
var lines = text.split("\n");
for (i = 0; i < lines.length; i++)
{
  // header lines
  if (lines[i].startsWith("{") && lines[i].endsWith("}"))
  {
    JSON.parse(lines[i]);
  }
  // data lines
  if (lines[i].startsWith("[") && lines[i].endsWith("]"))
  {
    JSON.parse(lines[i]);
  }
}

Creating and parsing the JsonCSV file without the JSon library

Generating the file

  • Write the header line as a simple text with the header.

"{"column-names" : ["Date", "Name", "Amount"]}\n"

  • Create a CSV with the data that follow the rules of JSon data.
    Add at the begin the "[" and at the end the "]".

Parse the JsonCSV file

  • Read the file line by line.
  • Process the lines that start with "{" and end with "}".
  • Process as data the lines that start with "[" and end with "]" as a normal CSV data structure.

Author

The JsonCSV specification has been conceived and developed by Domenico Zucchetti, founder and CEO of Banana.ch and creator of Banana Accounting.

Domenico Zucchetti has more then 30 year of experience in international accounting and as legal expert and software developers. He has been a blockchain pioneer. In 2002, it was the first in the world to implement a blockchain certification functionality in an accounting software.

D. Zucchetti welcomes feedback.

Aiutaci a migliorare la documentazione

Sono benvenuti suggerimenti per come migliorare questa pagina.

Informaci quale tema necessita di una spiegazione migliore o come chiarire meglio un argomento.

Condividi questo articolo: Twitter | Facebook | LinkedIn | Email