Trying to convert a JSON file or string to a CSV file gives an empty CSV file

user_18212150 0
Viewed 251

So I am trying to convert a JSON file (Which I have exported as JSON using Google Sheets API) to a CSV file, even though there is a JSON file placed correctly at that location, or trying with instead a String, it either shows null pointer exception or returns an empty CSV file.

Sample JSON file:

{
  "range": "products!A1:E4",
  "majorDimension": "ROWS",
  "values": [
    [
      "product_name",
      "price"
    ],
    [
      "Rugrats - Bedtime Bash [VHS]",
      "36.95"
    ],
    [
      "Teenage Mutant Ninja Turtles II - The Secret of the Ooze [VHS]",
      "29.89"
    ],
    [
      "Teenage Mutant Ninja Turtles II - The Secret of the Ooze [VHS]",
      "29.89"
    ]
  ]
}

The sample code I am trying to use here for converting them to CSV,

JSONObject output = new JSONObject(<String or file location that holds JSON file>);
File finalFile=new File(<Destination file path>);
JSONArray docs = output.getJSONArray("values");
String csv = CDL.toString(docs);
FileUtils.writeStringToFile(finalFile, csv);

Did I make any mistake in code or should I handle this JSON with different Java code?

google-sheets csv json java
1 Answers

Why not write directly from the Sheets API response (this example was extracted from the Java Sheet API quick-start)

public static void main(String... args) throws IOException, GeneralSecurityException {
    // Build a new authorized API client service.
    final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
    final String spreadsheetId = "<SHEET_ID>";
    final String range = "<SHEET_RANGE>";
    Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
            .setApplicationName(APPLICATION_NAME)
            .build();
    ValueRange response = service.spreadsheets().values()
            .get(spreadsheetId, range)
            .execute();
    List<List<Object>> values = response.getValues();
    if (values == null || values.isEmpty()) {
        System.out.println("No data found.");
    } else {
        /* Create a new writer */
        PrintWriter writer = new PrintWriter("data.csv");
        for (List<Object> list : values) {
            /* Building the string */
            StringBuilder str = new StringBuilder();
            str.append(list.get(0).toString() + ",");
            str.append(list.get(1).toString() + "\n");
            writer.write(str.toString());
        }
        /* Closing the writer */
        writer.close();
    }
}

In any case, if you are reading the JSON object correctly, the mechanism would be exactly the same.

Updated

If you want to read directly from JSON you can use this example (using GSON):

public class SheetResponse {
    private String range;
    private String majorDimensions; 
    private List<List<Object>> values;
}

public static void main(String... args) throws IOException, GeneralSecurityException {
    // Build a new authorized API client service.
    var gson = new Gson();
    var reader = new JsonReader(new FileReader("test.json"));
    SheetResponse sR = gson.fromJson(reader, SheetResponse.class);
    PrintWriter writer = new PrintWriter("data.csv");
    for (List<Object> list : sR.values) {
        /* Building the string */
        StringBuilder str = new StringBuilder();
        str.append(list.get(0).toString() + ",");
        str.append(list.get(1).toString() + "\n");
        writer.write(str.toString());
    }
    /* Closing the writer */
    writer.close();
}
Updated for the general case
public static void main(String... args) throws IOException, GeneralSecurityException {
    // Build a new authorized API client service.
    var gson = new Gson();
    var reader = new JsonReader(new FileReader("test.json"));
    SheetResponse sR = gson.fromJson(reader, SheetResponse.class);
    PrintWriter writer = new PrintWriter("data.csv");

    for (int i = 0; i < sR.values.size(); i++) {
        var valueRow = sR.values.get(i);
        StringBuilder str = new StringBuilder();
        for (int j = 0; j < valueRow.size(); j++) {
            if (j == valueRow.size() - 1) {
                str.append(valueRow.get(j).toString() + "\n");
            } else {
                str.append(valueRow.get(j).toString() + ",");
            }
        }
        writer.write(str.toString());
    }
    /* Closing the writer */
    writer.close();
}
Documentation
user_142716330
Related