1

I had gone through Saving Excel sheet as JSON file, which helped me to convert EXCEL table to JSON but I wish to add some additional fields to this JSON file.

enter image description here

I am asking the following information In first worksheet

  1. Your ID : A001
  2. Name : My NAME
  3. List item : Oct-2018

[My 2nd Sheet]

This information will be common for data entered in 2nd sheet

While creating JSON file I want to Add "Automatically" the following 3 fields (accepted in Sheet 1) in each record for JSON I am creating from Sheet2

  1. Your ID : A001
  2. Name : My NAME
  3. List item : Oct-2018

I am using following VBA code to create JSON

Public Sub xls2json()
    savename = "xls2json.json"
    Dim wkb As Workbook
    Dim wks As Worksheet
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets(2)
    lcolumn = wks.Cells(4, 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(4, i) 'titles are taken from this row
    Next i
    json = "["
    dq = """"
    For J = 5 To lrow ' data picked from this row onwards
        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
Prajakta Software
  • 11

1 Answers1

-1

As I understand, you have JSON files for sheel1 and sheet2 and want to add columns from sheet2 to sheet1. Something like below.

0 > cat file1.json
[
  {
    "firstName": "Alice",
    "age": 22
  },
  {
    "firstName": "Bob",
    "age": 33
  }
]
0 > cat file2.json
[
  {
    "fullName": "Alice Alice"
  },
  {
    "fullName": "Bob Bob"
  }
]

to get something like ...

0 > python3 ./bla.py file1.json file2.json
[
    {
        "firstName": "Alice",
        "age": 22,
        "fullName": "Alice Alice"
    },
    {
        "firstName": "Bob",
        "age": 33,
        "fullName": "Bob Bob"
    }
]

Following Python code should work

import json, sys


def main():
    '''The Main'''

    data1 = json.load(open(sys.argv[1]))
    data2 = json.load(open(sys.argv[2]))

    assert (len(data1) == len(data2)), "Number of rows not equal"

    for i in range(len(data1)):
        for k in data2[i].keys():
            data1[i][k] = data2[i][k]

    print(json.dumps(data1, indent=4))


if __name__ == '__main__':
    main()

HTH

Krishna

Krishna
  • 124