Converting Nested Json files to CSV in java

Question

    {
    "Employee": [
        {
            "empMID": "mock:1",
            "comments": [],
            "col1": "something",
            "contact": [{"address":"2400 waterview", "freetext":true}
                         ],
            "gender": "male"
        },
        {
            "empMID": "mock:2",
            "comments": [],
            "col1": "something",
            "contact": [{"address":"2200 waterview", "freetext":true}
                         ],
            "gender": "female"
        }
    ],
    "cola": false,
    "colb": false
}

This is how my Json file looks .I m required to convert this json to a csv .(I m trying to convert a multi-dimesional data to 2d).I m using gson for my purpose.I cannot use gson.fromgson() function to object map with a template because it should be generic .

I know we can use CDL to convert jsonarray to csv format but It wont work in my case .

my csv format looks like

Employee*
empMID,comment.$,contact.address,contact.freetext,gender
mock:1,,2400 waterview,TRUE,male
mock:123,,2200 waterview,TRUE,female
colA#
TRUE
colB#
FALSE

I tried using google-GSON api to convert to this format .But I m not able to convert to this format .I have used * to represent its a json array and # to represent its a primitive type and contact.address to represent nested array inside another json array .I having problem relating this nested structure .I m able to traverse everything recursively like a column. Thanks in advance

public static void main(String[] args) throws IOException{

        BufferedReader reader=null;
        StringBuilder content=null;
        String result=null;

            reader = new BufferedReader(new FileReader("temp.json"));

            String line = null;
            content= new StringBuilder();

            while ((line = reader.readLine()) != null) {
            content.append(line);
            }
            reader.close();
            result= content.toString();

            JsonElement jelement = new JsonParser().parse(result);

            printJsonRecursive(jelement);


        }


    public static void printJsonRecursive(JsonElement jelement){


        if(jelement.isJsonPrimitive()){

            System.out.println(jelement.getAsString());
            return;
        }
        if(jelement.isJsonArray()){

            JsonArray jarray= jelement.getAsJsonArray();
            for(int i=0;i<jarray.size();i++){
                JsonElement element= jarray.get(i);
                printJsonRecursive(element);
            }
            return;

        }
        JsonObject  jobject= jelement.getAsJsonObject();

        Set<Entry<String, JsonElement>> set= jobject.entrySet();

        for (Entry<String, JsonElement> s : set) {

            printJsonRecursive(s.getValue());


        }

    }



}

skap · Accepted Answer · 2016-12-11 06:35:27Z

You can use the library json2flat for converting your JSON to CSV.

This library doesn't require any POJO's. It simply takes your JSON as string and returns a 2D representation of it in the format of List<Object[]>.

For example for the JSON:

{
    "Employee": [
        {
            "empMID": "mock:1",
            "comments": [],
            "col1": "something",
            "contact": [{"address":"2400 waterview", "freetext":true}
                         ],
            "gender": "male"
        },
        {
            "empMID": "mock:2",
            "comments": [],
            "col1": "something",
            "contact": [{"address":"2200 waterview", "freetext":true}
                         ],
            "gender": "female"
        }
    ],
    "cola": false,
    "colb": false
}

It gives an output:

/cola,/colb,/Employee/empMID,/Employee/col1,/Employee/gender,/Employee/contact/address,/Employee/contact/freetext
,,"mock:1","something",,"2400 waterview",true
,,"mock:2","something",,"2200 waterview",true
false,false,,,,,

LeOn - Han Li · Accepted Answer · 2017-04-13 17:18:51Z

1

You can achieve this thru reflection if you have a object mapped to the json.

use gson/jackson to convert json to java object
append fields using reflection by iterating the class and get any field you interested in.
append value with reflection by getting value from the target object.

Comments

rocketboy · Accepted Answer · 2013-08-02 04:13:44Z

0

You are not printing the key. This should fix it.

    for (Entry<String, JsonElement> s : set) {

        System.out.println(s.getKey());            //Added
        printJsonRecursive(s.getValue());

    }

You can take care of \ns from here.

EDIT

If you want to print the keys just once for repeating json objects, create a Java bean to hold the data and populate it during your recursion. Once the bean is complete, add a method there to print all the data in the format you want (printing keys only once and so on).

edited Aug 2, 2013 at 4:13

answered Aug 2, 2013 at 3:53

rocketboy

9,7912 gold badges36 silver badges37 bronze badges

2 Comments

arun Over a year ago

I did try this .But the problem is I could get key for every row in the array .I want it only once to print it .

arun Over a year ago

I cannot create a java bean because I wont be knowing the schema of json .for eg I will not know what structures the json will contain .It should be generic

shubhi agarwal · Accepted Answer · 2021-03-02 07:56:24Z

0

/**
 * Get separated comlumns used a separator (comma, semi column, tab).
 *
 * @param headers The CSV headers
 * @param map     Map of key-value pairs contains the header and the value
 *
 * @return a string composed of columns separated by a specific separator.
 */
private static String getSeperatedColumns(Set<String> headers, Map<String, String> map, String separator) {
    List<String> items = new ArrayList<String>();
    for (String header : headers) {
        String value = map.get(header) == null ? "" : map.get(header).replaceAll("[\\,\\;\\r\\n\\t\\s]+", " "); 
        items.add(value);
    }

    return StringUtils.join(items.toArray(), separator);
}

edited Mar 2, 2021 at 7:56

answered Mar 2, 2021 at 7:02

shubhi agarwal

11 bronze badge

Collectives™ on Stack Overflow

Converting Nested Json files to CSV in java

4 Answers 4

Comments

Comments

EDIT

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

EDIT

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related