How to Convert a Grouped Excel File That Contains Merged Cells into JSON Format

 

We have an Excel file json.xlsx

EMPId

Name

Date

Periodname

TimUnitDuration

charge entry

Type

Price

21

abc

2015/12/31

15 data

15

fixed

100

45 data

45

fixed

100

42

def

2015/12/31

15 data

15

fixed

100

30 data

30

fixed

100

45 data

45

fixed

100

We need to convert the grouped Excel table containing merged cells into JSON format. Below is the expected result:

[

    {

        "EMPId":21,

        "Name":"abc",

        "Date":"2015-12-31",

        "Period":[

            {

                "Periodname":"15 data",

                "TimUnitDuration":15,

                "ChargeEntry":[

                    {

                        "Type":"fixed",

                        "Price":100

                    }

                ]

            },

            {

                "Periodname":"45 data",

                "TimUnitDuration":45,

                "ChargeEntry":[

                    {

                        "Type":"fixed",

                        "Price":100

                    }

                ]

            }

        ]

    },

    {

        "EMPId":42,

        "Name":"def",

        "Date":"2015-12-31",

        "Period":[

            {

                "Periodname":"15 data",

                "TimUnitDuration":15,

                "ChargeEntry":[

                    {

                        "Type":"fixed",

                        "Price":100

                    }

                ]

            },

            {

                "Periodname":"30 data",

                "TimUnitDuration":30,

                "ChargeEntry":[

                    {

                        "Type":"fixed",

                        "Price":100

                    }

                ]

            },

            {

                "Periodname":"45 data",

                "TimUnitDuration":45,

                "ChargeEntry":[

                    {

                        "Type":"fixed",

                        "Price":100

                    }

                ]

            }

        ]

    }

]

It’s simple to accomplish this with esProc.
Download esProc DSK edition and free license HERE.

1.        Write script p1.dfxin esProc:

A

1

=file("json.xlsx").xlsimport@w(;,3)

2

=A1.group@i(~(1))

3

=A2.(~.(~.(if(~==null,A2.~(1)(#),~))))

4

=create(EMPId,Name,Date,Periodname,TimUnitDuration,CT,CP)

5

=A3.(~.(A4.record(~)))

6

=A4.group(#1,#2,#3;~.group(Periodname,TimUnitDuration;~.new(CT:Type,CP:Price):ChargeEntry):Period)

7

=json(A6)

A1   Import data of json.xlsxfrom the third row; @t option enables reading data as a sequence of sequences.

A2   Create a new group if the first value of each sequence is not null.

A3   Assign null to the empty cell in each merged cell.

A4   Create an empty table sequence.

A5   Insert data to the table sequence.

A6   Group rows according to Excel rules.

A7   Convert the table sequence to JSON format.

2.     Execute script to get desired result in A7.