Convert CSV with nested JSON object to JSON

Question

I have a sample CSV message:

header1,header2,header3
value1,value2,{"name":"John","age":30,"car":null}

How to convert it in form of embedded JSON as in:

{
  "header1": "value1",
  "header2": "value2",
  "header3": "{\"name\":\"John\",\"age\":30,\"car\":null}"
}

I am using Jackson schema builder with default column separator:

CsvSchema.builder().disableQuoteChar().setUseHeader(true).build();
CsvMapper.builder().enable(CsvParser.Feature.IGNORE_TRAILING_UNMAPPABLE, CsvParser.Feature.WRAP_AS_ARRAY).build();

Answer 1

Presented CSV content is broken. Values which contains column separator should be wrapped with a quote character. If we can not change the app which generates it we need to amend it before deserialising process. This example is simple so we can just replace { with |{ and } with }| and set | as a quote character. But for JSON payloads with internal objects we need to replace only the first { and the last } brackets. Code could look like below:

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.SerializationFeature;
import com.fasterxml.jackson.databind.json.JsonMapper;
import com.fasterxml.jackson.dataformat.csv.CsvMapper;
import com.fasterxml.jackson.dataformat.csv.CsvSchema;

import java.io.File;
import java.nio.file.Files;
import java.util.stream.Collectors;

public class CsvApp {

    public static void main(String[] args) throws Exception {
        File csvFile = new File("./resource/test.csv").getAbsoluteFile();
        String csv = Files.readAllLines(csvFile.toPath()).stream().collect(Collectors.joining(System.lineSeparator()));
        csv = csv.replace("{", "|{").replace("}", "}|");

        CsvMapper csvMapper = CsvMapper.builder().build();

        CsvSchema csvSchema = CsvSchema.builder().setQuoteChar('|').setUseHeader(true).build();
        Object csvContent = csvMapper.readerFor(JsonNode.class).with(csvSchema).readValue(csv);

        JsonMapper mapper = JsonMapper.builder().enable(SerializationFeature.INDENT_OUTPUT).build();
        mapper.writeValue(System.out, csvContent);
    }
}

Above code prints:

{
  "header1" : "value1",
  "header2" : "value2",
  "header3" : "{\"name\":\"John\",\"age\":30,\"car\":null}"
}

Answer 2

You can use a csv JS library such as json-2-csv

# Global so it can be called from anywhere
npm install -g json2csv

# or as a dependency of a project
npm install json2csv --save

Answer 3

You can org.json.CDL as follows:

        BufferedReader br = new BufferedReader(new FileReader("file.csv"));
        String csvAsString = br.lines().collect(Collectors.joining("\n"));
        String json = CDL.toJSONArray(csvAsString).toString();
        try {
            Files.write(Path.of("src/main/resources/output.json"), json.getBytes(StandardCharsets.UTF_8));
        } catch (IOException e) {
            e.printStackTrace();
        }