Convert Excel data to JSON CSV and XML format

By default, Excel doesn’t provide any formula to convert a range or a table into a JSON (JavaScript Object Notation), CSV (Comma Separated Value) or to an XML (Extensible Markup Language) format. This article shows how to convert a range or a table into JSON, XML, or a CSV format.

Convert Excel data to JSON format

Once the code below is pasted to the VBA, one can use a formula ToJSON and convert a range to a JSON format. The second argument in the formula is optional. When this argument is set to false, the name part of the JSON will NOT be surrounded by a quote as shown in the image above.

Convert Excel data to XML format

Excel to XML
Converting to XML format is very similar to JSON format. Over the internet (client/server communication) XML format is losing ground however it is still used in web or other places.

Convert Excel data to CSV format

Excel to CSV
CSV format is very common format used at so many places to transport data. Eventhough CSV stands for Comman Separated Value, there are lot of other character used to separate values. So in the formula below, one can specify the delimitter character. Also if the values need to be surrounded by a single quote, the second argument can be set to true.

%d bloggers like this: