Convert CSV to JSON array in Java Springboot

Question

Hi I am trying to convert a CSV file into a JSON array using A dependency called csvReader, but when I run the code it prints out the JSON response incorrectly and I ament sure why would anyone be able to point me in the right direction.

    @GetMapping("/convert")
    public List<List<String>> convertCSV() throws FileNotFoundException {
        List<List<String>> records = new ArrayList<List<String>>();
    try (CSVReader csvReader = new CSVReader(new FileReader("C:/Download/cities.csv"));) {
        String[] values = null;

    while ((values = csvReader.readNext()) != null) {
        records.add(Arrays.asList(values));
    }

} catch (IOException e) {
            e.printStackTrace();
        }
        return values;
    }

Answer 1

That is because of your reading data in String and printing the List of String. If you want to map the CSV to Object ( JSON Object), You need to read the CSV as bean object please find below code snippet to print as JSON, override toString method as JSON format.

User.java

public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotNull
    private String name;

    @NotNull
    private String surname;

    //Getter and Setters
}

CsvReaderUtil.java

public static List<User> readCsvFile() throws IOException {
            List<User> list = null;
            CSVReader reader = null;
            InputStream is = null;
            try {
                File initialFile = new File("C:\\Users\\ER\\Desktop\\test.csv");
                is = new FileInputStream(initialFile);
                reader = new CSVReader(new InputStreamReader(is), ',', '"', 1);
                ColumnPositionMappingStrategy strat = new ColumnPositionMappingStrategy();
                strat.setType(User.class);
                String[] columns = new String[]{"id", "name", "surname"};
                strat.setColumnMapping(columns);
                CsvToBean csv = new CsvToBean();
                list = csv.parse(strat, reader);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                is.close();
                reader.close();
            }
            return list;
        }

Now print this List Of Users as a JSON object.

Answer 2

Your case is not a big deal.

You can read that csv and build json.

Read first row and determine columns. The rest of rows are values.

public class Foo{

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

        List<String> csvRows = null;
        try(var reader = Files.lines(Paths.get("dataFile.csv"))){
            csvRows = reader.collect(Collectors.toList());
        }catch(Exception e){
            e.printStackTrace();
        }

        if(csvRows != null){

            String json = csvToJson(csvRows);
            System.out.println(json);

        }

    }

    public static String csvToJson(List<String> csv){

        //remove empty lines
        //this will affect permanently the list. 
        //be careful if you want to use this list after executing this method
        csv.removeIf(e -> e.trim().isEmpty());

        //csv is empty or have declared only columns
        if(csv.size() <= 1){
            return "[]";
        }

        //get first line = columns names
        String[] columns = csv.get(0).split(",");

        //get all rows
        StringBuilder json = new StringBuilder("[\n");
        csv.subList(1, csv.size()) //substring without first row(columns)
            .stream()
            .map(e -> e.split(","))
            .filter(e -> e.length == columns.length) //values size should match with columns size
            .forEach(row -> {

                json.append("\t{\n");

                    for(int i = 0; i < columns.length; i++){
                        json.append("\t\t\"")
                            .append(columns[i])
                            .append("\" : \"")
                            .append(row[i])
                            .append("\",\n"); //comma-1
                    }

                    //replace comma-1 with \n
                    json.replace(json.lastIndexOf(","), json.length(), "\n");

                json.append("\t},"); //comma-2

            });

        //remove comma-2
        json.replace(json.lastIndexOf(","), json.length(), "");

        json.append("\n]");

        return json.toString();

    }

}

Tested on:

fname,lname,note
Shaun,Curtis,a
Kirby,Beil,b

-----------------------

[
    {
        "fname" : "Shaun",
        "lname" : "Curtis",
        "note" : "a"
    },  {
        "fname" : "Kirby",
        "lname" : "Beil",
        "note" : "b"
    }
]

This method work on any structure of csv. Don't need to map columns.

Answer 3

Here is a useful example of how to transform CSV to JSON using Java 11+:

private String fromCsvToJson(String csvFile) {
    String[] lines = file.split("\n");

    if (lines.length <= 1) {
        return List.of();
    }

    var headers = lines[0].split(",");
    var jsonFormat = Arrays.stream(lines)
                           .skip(1)
                           .map(line -> line.split(","))
                           .filter(line -> headers.length == line.length)
                           .map(line -> IntStream.range(0, headers.length).boxed().collect(toMap(i -> headers[i], i -> line[i], (a, b) -> b)))
                           .toList();

    return new ObjectMapper().writeValueAsString(jsonFormat);
}