Today we look at the technology behind integrations 🤓!
There are three main data formats used to transfer data from one system to another: CSV, XML and JSON.
To develop a solid solution, it is important to understand the differences between each format and when to use them.
The purpose of this post is to define each data format, describe the advantages and disadvantages for each, and discover what situations work best with each format. I hope it helps you!
#1: CSV
CSV stands for “comma-separated values.” As the name implies, this data format is simply a list of elements separated by commas. It’s super simple, but that’s also the downside.
This is it is the oldest and least flexible format. CSV does not really support data hierarchies and if there is a small change in the order of columns, the integration (parser) must be changed.
Useful for importing master data, but not ideal for linking (unfortunately, many old ERP and CAM packages still use this).
My favorite CSV tools: Google Sheets, Excel or online tools
#2: XML
XML stands for “extensible markup language.” It was designed in 1996 and standardized in 1998, making it the most popular integration format for most applications developed in the years 2000-2010.
This data format fully supports hierarchical data structures and is well suited for receiving complex data. It is also very easy for people to read. In addition, it includes capabilities to validate content via XSD files.
Most browsers have built-in XML readers that allow you to inspect XML files. And conversion from XML to other formats is quite possible (Pro tip: XML mappings can also be made in Excel).
My favorite XML tools: XML marker (v1.1 = free), NotePad++ (with xml-addon), Excel
#3: JSON
JSON stands for JavaScript Object Notation. It was invented in 2001 and popularized by Yahoo and Google in 2005 and 2006. It was created as an alternative to XML.
JSON is a simple and compact data format, but supports hierarchical data like XML. Unlike XML, JSON formats are only about twice the size of CSV formats.
You will encounter this format most often in modern cloud applications and links to them via APIs (an email about this will follow).
My favorite JSON tools: MiTec JSON viewer (free), various web apps, or VS Code
Conclusion
XML is currently still the status quo. As a general rule of thumb, JSON is the best format for data exchange so far. It is lightweight, compact and versatile. But not every system can handle this. CSV should only be used when sending large amounts of data.
PS All of the above formats can also be edited with tools such as VS Code (free), but this package has a slightly steeper learning curve.
