JCSV文件格式

在此文中

Banana.ch has developed a new file format for data interchange named JCSV (Json Comma Separated Value).
JCSV brings together the advantages of JSon and the CSV format (Comma Separated Value):

  • It maintains the characteristics of CSV, but makes the parsing more reliable and effective.
  • It allows mixing data and metadata (header, column info, etc.).
  • Multiple tables can fit in the same file.

This is the JCSV file in the simplest form.

{"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 are within [] brackets, 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 fully ignored.

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

JCSV with data and metadata

JCSV is an innovative file format for the fact that it allows to embed in the same file the data and the information that allows to understand and process the data (metadata). In most file format, like CSV, JSon and XML the metadata must be specified on other files (Schema or DTD).

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

The adavantages 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 "\r\n" or "\n". Both end of line terminators should be supported.
  • 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.
    • 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).

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.

 

This documentation is outdated

The most complete and up-to-date documentation is the one of Banana Accounting Plus: Try it now

分享这篇文章: Twitter | Facebook | LinkedIn | Email