Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

converting CSV/XLS to JSON? [closed]

Tags:

json

csv

xls

People also ask

Can I convert Excel to JSON?

There is no predefined method in Excel to convert the Excel data to JSON. You can either use online Excel to JSON conversion software or download an add-in from the Microsoft store for this task to get done.


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.


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

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


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


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();