How to convert an excel/CSV table to JSON object in JAVA?

Question

I want to convert an excel table (or CSV table) to a JSON object in the form of Key-Value pair. I cannot use any online service as files are really large.

For example, my table looks something like this:

name	place	animal
Himanshu	India	Cat
Kamal	London	Dog
John	Turkey	Lion

And I want my response similar to this:

{
  {"name":"Himanshu",
    "place":"India",
    "animal":"Cat"
  },
  {
    "name":"Kamal",
    "place":"London",
    "animal":"Dog"
  },
  {
    "name":"John",
    "place":"Turkey",
    "animal":"Lion"
  }
}

As I have to post this as a response, I tried to convert it to a list of arrays using the following code :

public static List<String[]> get(String file) {

    String delimiter = ",";
    String line;
    List lines = new ArrayList<>();

    try (Scanner s = new Scanner(new File(file))) {
        while (s.hasNext()) {
            line = s.next();
            List values = Arrays.asList(line.split(delimiter));
            lines.add(values);
        }
        
    } catch (Exception e) {
        System.out.println(e);
    }
    return lines;

}

Now the issue with such a code is that not only it doesn't return output as a list of arrays but also it isn't very generalized and cannot handle any real-life exceptions. Looping through an excel file with hundreds of thousands of entries is using such code would require a lot of time and resources. And we have to work with many such files.

The language I am using is JAVA (Springboot as a framework). It would be great if you could suggest an efficient library to handle such cases. Suggestions for libraries in other languages are welcome but the priority is to do this using JAVA.

Thank you.

Please add what you have tried so far (and the missing comma as well)! — Klaus D.
– Klaus D., Commented Feb 1, 2021 at 7:30

Amit Kumar Lal · Accepted Answer · 2021-02-01 08:36:14Z

Use Apache POI to convert excel data to Java Objects (can use a class to hold data) & then use JSONObject to convert it into JSON

public static void main(String[] args) throws IOException, JSONException {
    //Out put JSONArray
    JSONArray data = new JSONArray();

    //Read the File 
    FileInputStream fileInputStream = new FileInputStream("D:\\test.xls");

    //Load the file into workbook using Apache POI 
    Workbook workbook = new XSSFWorkbook(fileInputStream);
    Sheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rows = sheet.iterator();
    int index = 0;
    while(rows.hasNext()) {
        Row row = rows.next();
        if(index!=0) {
            //Add to JSON Array
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("Name", row.getCell(0).getStringCellValue());
            jsonObject.put("Place", row.getCell(1).getStringCellValue());
            jsonObject.put("Animal", row.getCell(2).getStringCellValue());
            data.put(jsonObject);
        }
        index++;
    }

    //print JsonArray
    System.out.println(data.toString());
}

output

[
   {
      "Animal":"Cat",
      "Place":"India",
      "Name":"Himanshu"
   },
   {
      "Animal":"Dog",
      "Place":"London",
      "Name":"Kamal"
   },
   {
      "Animal":"Lion",
      "Place":"Turkey",
      "Name":"John"
   }
]

Collectives™ on Stack Overflow

How to convert an excel/CSV table to JSON object in JAVA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related