converting CSV/XLS to JSON?

Question

Does anyone know if there is application that will let me convert preferably XLS to JSON?

I'll also settle for a converter from CSV since that's what I'll probably end up having to write myself if there is nothing around.

Answer 1

You can try this tool I made:

Mr. Data Converter

It converts to JSON, XML and others.

It's all client side, too, so your data never leaves your computer.

Answer 2

This worked perfectly for me and does NOT require a file upload:

https://github.com/cparker15/csv-to-json?files=1

Answer 3

Since Powershell 3.0 (shipped with Windows 8, available for Windows 7 and windows Server 2008 but not Windows Vista ) you can use the built-in convertto-json commandlet:

PS E:> $topicsjson = import-csv .\itinerary-all.csv | ConvertTo-Json 

PS E:\> $topicsjson.Length
11909

PS E:\> $topicsjson.getType()

IsPublic IsSerial Name                                     BaseType                  
-------- -------- ----                                     --------                  
True     True     Object[]                                 System.Array              

Online Help Page on Technet

Answer 4

If you can't find an existing solution it's pretty easy to build a basic one in Java. I just wrote one for a client and it took only a couple hours including researching tools.

Apache POI will read the Excel binary. http://poi.apache.org/

JSONObject will build the JSON

After that it's just a matter of iterating through the rows in the Excel data and building a JSON structure. Here's some pseudo code for the basic usage.

FileInputStream inp = new FileInputStream( file );
Workbook workbook = WorkbookFactory.create( inp );

// Get the first Sheet.
Sheet sheet = workbook.getSheetAt( 0 );

    // Start constructing JSON.
    JSONObject json = new JSONObject();

    // Iterate through the rows.
    JSONArray rows = new JSONArray();
    for ( Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext(); )
    {
        Row row = rowsIT.next();
        JSONObject jRow = new JSONObject();

        // Iterate through the cells.
        JSONArray cells = new JSONArray();
        for ( Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext(); )
        {
            Cell cell = cellsIT.next();
            cells.put( cell.getStringCellValue() );
        }
        jRow.put( "cell", cells );
        rows.put( jRow );
    }

    // Create the JSON.
    json.put( "rows", rows );

// Get the JSON text.
return json.toString();

Answer 5

This works for me and runs client-side: http://www.convertcsv.com/csv-to-json.htm

Answer 6

I just found this:

http://tamlyn.org/tools/csv2json/

( Note: you have to have your csv file available via a web address )

Answer 7

Take a try on the tiny free tool:

http://keyangxiang.com/csvtojson/

It utilises node.js csvtojson module

Answer 8

None of the existing solutions worked, so I quickly hacked together a script that would do the job. Also converts empty strings into nulls and and separates the header row for JSON. May need to be tuned depending on the CSV dialect and charset you have.

#!/usr/bin/python
import csv, json
csvreader = csv.reader(open('data.csv', 'rb'), delimiter='\t', quotechar='"')
data = []
for row in csvreader:
    r = []
    for field in row:
        if field == '': field = None
        else: field = unicode(field, 'ISO-8859-1')
        r.append(field)
    data.append(r)
jsonStruct = {
    'header': data[0],
    'data': data[1:]
}
open('data.json', 'wb').write(json.dumps(jsonStruct))

Answer 9

Instead of hard-coded converters, how about CSV support for Jackson (JSON processor): https://github.com/FasterXML/jackson-dataformat-csv. So core Jackson can read JSON in as POJOs, Maps, JsonNode, almost anything. And CSV support can do the same with CSV. Combine the two and it's very powerful but simple converter between multiple formats (there are backends for XML, YAML already, and more being added).

An article that shows how to do this can be found here.

Answer 10

See if this helps: Back to CSV - Convert CSV text to Objects; via JSON

This is a blog post published in November 2008 that includes C# code to provide a solution.

From the intro on the blog post:

As Json is easier to read and write then Xml. It follows that CSV (comma seperated values) is easier to read and write then Json. CSV also has tools such as Excel and others that make it easy to work with and create. So if you ever want to create a config or data file for your next app, here is some code to convert CSV to JSON to POCO objects