JCSV file format

JSCV (Json Comma Separated Value) for tabular data

JSCV (Json Comma Separated Value) is plain text file format for tabular data. It is realiable to read and with support for multiple tables.
JSCV 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).
  • 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 JCSV file in the simplest form. An header row, followed by the data rows.
Each line is written in a JSon data 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.

JCSV with data and metadata

JCSV it 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.

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

The advantages of JCSV 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. 

JCSV references

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

JCSV format specification

See example below.

  • Use the UTF8 format
  • JCSV 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.
      • "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

n 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"}}
{"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]

JCSV files

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

Advantages and limitation of JCSV format

Advantages of JCSV file format over CSV

  • JCSV 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 JCSV it is possible to use the Json libraries.
  • JCSV 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 JCSV 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 JCSV

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

Try the JCSV format

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

  • See JCSV example files on Github\BananaAccounting
  • Open or drag a JCSV file in Banana
    Banana will create a new file tables and columns. You can the copy and paste the data in Excel.
  • Export in JCSV
    • 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 JCSV format

Generating JCSV 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 JCSV 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 JCSV

 

// 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 JCSV 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 JCSV 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 JCSV 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.

 

Help us improve the documentation

We welcome feedback on how to improve this page.

Tell us what theme needs a better explanation or how to clarify a topic.

Share this article: Twitter | Facebook | Email