11

Is there a simple way to convert a simple excel sheet to a JSON file?

For example the source sheet could look like:

   A           B
1 firstName   age
2 Alice       22
3 Bob         33

and the saved JSON:

[{firstName: 'Alice', age: 22}, {firstName: 'Bob', age: 33}]
1
  • 4
    Googling to "table to json" gives you several solutions, e.g. this Sep 13, 2017 at 7:54

6 Answers 6

11

I've combined jcbermu’s answer and JanHudecek’s answer with a UTF-8 version (snippets found here) which keeps accents and other Unicode goodness.

It saves the file beside the active workbook file but with the .json file extension. It's fast. It can be easily formatted in VS Code (Shift+Alt+F).

To use it, hit Alt+F11 to get to the VBA code editor, open the code for your active worksheet, then paste it into the code window. Hit F5 to run.

Public Sub tojson()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    jsonFilename = fso.GetBaseName(ActiveWorkbook.Name) & ".json"
    fullFilePath = Application.ActiveWorkbook.Path & "\" & jsonFilename

    Dim fileStream As Object
    Set fileStream = CreateObject("ADODB.Stream")
    fileStream.Type = 2 'Specify stream type - we want To save text/string data.
    fileStream.Charset = "utf-8" 'Specify charset For the source text data.
    fileStream.Open 'Open the stream And write binary data To the object

    Dim wkb As Workbook
    Set wkb = ThisWorkbook

    Dim wks As Worksheet
    Set wks = wkb.Sheets(1)

    lcolumn = wks.Cells(1, Columns.Count).End(xlToLeft).Column
    lrow = wks.Cells(Rows.Count, "A").End(xlUp).Row
    Dim titles() As String
    ReDim titles(lcolumn)
    For i = 1 To lcolumn
        titles(i) = wks.Cells(1, i)
    Next i
    fileStream.WriteText "["
    dq = """"
    escapedDq = "\"""
    For j = 2 To lrow
        For i = 1 To lcolumn
            If i = 1 Then
                fileStream.WriteText "{"
            End If
            cellvalue = Replace(wks.Cells(j, i), dq, escapedDq)
            fileStream.WriteText dq & titles(i) & dq & ":" & dq & cellvalue & dq
            If i <> lcolumn Then
                fileStream.WriteText ","
            End If
        Next i
        fileStream.WriteText "}"
        If j <> lrow Then
            fileStream.WriteText ","
        End If
    Next j
    fileStream.WriteText "]"
    fileStream.SaveToFile fullFilePath, 2 'Save binary data To disk
    a = MsgBox("Saved to " & fullFilePath, vbOKOnly)
End Sub
1
4

If you want the script to actually finish before you're a pensioner, I suggest writing to the output file immediately instead of concatenating the string var:

Public Sub tojson()
    savename = "exportedxls.json"
    myFile = Application.DefaultFilePath & "\" & savename
    Open myFile For Output As #1
    Dim wkb As Workbook
    Dim wks As Worksheet
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets(1)
    lcolumn = wks.Cells(1, Columns.Count).End(xlToLeft).Column
    lrow = wks.Cells(Rows.Count, "A").End(xlUp).Row
    Dim titles() As String
    ReDim titles(lcolumn)
    For i = 1 To lcolumn
        titles(i) = wks.Cells(1, i)
    Next i
    Print #1, "["
    dq = """"
    For j = 2 To lrow
        For i = 1 To lcolumn
            If i = 1 Then
                Print #1, "{"
            End If
            cellvalue = wks.Cells(j, i)
            Print #1, dq & titles(i) & dq & ":" & dq & cellvalue & dq
            If i <> lcolumn Then
                Print #1, ","
            End If
        Next i
        Print #1, "}"
        If j <> lrow Then
            Print #1, ","
        End If
    Next j
    Print #1, "]"
    Close #1
    a = MsgBox("Saved as " & savename, vbOKOnly)
End Sub
2

This VBA code will work :

Public Sub tojson()
    savename = "exportedxls.json"
    Dim wkb As Workbook
    Dim wks As Worksheet
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets(1)
    lcolumn = wks.Cells(1, Columns.Count).End(xlToLeft).Column
    lrow = wks.Cells(Rows.Count, "A").End(xlUp).Row
    Dim titles() As String
    ReDim titles(lcolumn)
    For i = 1 To lcolumn
        titles(i) = wks.Cells(1, i)
    Next i
    json = "["
    dq = """"
    For j = 2 To lrow
        For i = 1 To lcolumn
            If i = 1 Then
                json = json & "{"
            End If
            cellvalue = wks.Cells(j, i)
            json = json & dq & titles(i) & dq & ":" & dq & cellvalue & dq
            If i <> lcolumn Then
                json = json & ","
            End If
        Next i
        json = json & "}"
        If j <> lrow Then
            json = json & ","
        End If
    Next j
    json = json & "]"
    myFile = Application.DefaultFilePath & "\" & savename
    Open myFile For Output As #1
    Print #1, json
    Close #1
    a = MsgBox("Saved as " & savename, vbOKOnly)
End Sub

Open VBA /Macros with ALT+F11.

On the left side double click on The worksheet, on the right side paste the code.

Set the variable savename to the name that you want for the json file and that's all.

2
  1. save as csv
  2. convert to json with mlr

    mlr --icsv --ifs ';' --ojson cat exported.csv > out.json

If you want to transform the jsonfile. You could use jq

jq -s '[.[]|{newfieldname: .oldone, "phone": "123"}]' out.json > transformed.json
1

If you just need a simple one off conversion to JSON there's websites for this.

I've used this with good results: https://www.convertcsv.com/csv-to-json.htm

0

I used this converter for similar task: names:

http://www.whiterocksoftware.com/2021/08/convert-xlsx-excel-file.html?q=excel+to+json#ExcelToJson 

converter produced json file with array of entities and uses first row as field

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .