Univocity - how to parse CSV and insert into DB multiple fields into one column?

Question

I'm using the fantastic Univocity CSV Parser (thumbs up to the author!) but I'm struggling to find a solution for my problem.

Let's use a CSV with 10 fields. User uploads the CSV and chooses 2 columns, discarding the other 8. Upload is done to a Java Spring Rest Service with the file containing all 10 columns + a JSON object with the selected columns. The goal is to have the REST service get the file, parse it and generate POJO objects where:

• property A = field #1
• property B = field #2
• property C = json object with all other remaining fields

i couldn't find a way to do it using the library. But maybe I missed something. Anyone knows if this is possible with Univocity CSV Parser libs ? How can this be implemented?

Thanks in advance

Answer 1

You can achieve this functionality using the Univocity CSV Parser in combination with Jackson, a JSON library for Java.

1. Define a POJO to represent your CSV data. Let's call it CsvData.

public class CsvData {
    private String propertyA;
    private String propertyB;
    private Map<String, String> propertyC;

    // Getters and setters
}

2. Implement the CsvParserService to parse the CSV file and create CsvData objects.

import com.univocity.parsers.csv.CsvParser;
import com.univocity.parsers.csv.CsvParserSettings;

import org.springframework.stereotype.Service;

import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public class CsvParserService {

    public List<CsvData> parseCsv(InputStream inputStream, Map<String, Object> selectedColumnsMap) {
        List<CsvData> csvDataList = new ArrayList<>();

        CsvParserSettings settings = new CsvParserSettings();
        settings.detectFormatAutomatically();
        CsvParser parser = new CsvParser(settings);

        parser.beginParsing(new InputStreamReader(inputStream));
        String[] headers = parser.parseNext();

        String propertyAHeader = (String) selectedColumnsMap.get("propertyA");
        String propertyBHeader = (String) selectedColumnsMap.get("propertyB");

        int[] propertyCIndexes = new int[headers.length - 2];
        int propertyCIndex = 0;
        for (int i = 0; i < headers.length; i++) {
            if (!headers[i].equals(propertyAHeader) && !headers[i].equals(propertyBHeader)) {
                propertyCIndexes[propertyCIndex++] = i;
            }
        }

        String[] row;
        while ((row = parser.parseNext()) != null) {
            CsvData csvData = new CsvData();
            csvData.setPropertyA(row[propertyAIndex]);
            csvData.setPropertyB(row[propertyBIndex]);

            Map<String, String> propertyC = new HashMap<>();
            for (int index : propertyCIndexes) {
                propertyC.put(headers[index], row[index]);
            }
            csvData.setPropertyC(propertyC);

            csvDataList.add(csvData);
        }

        return csvDataList;
    }
}

3. In your Spring controller, handle the file upload and parsing. Use Jackson to deserialize the JSON object representing the selected columns.

import com.fasterxml.jackson.databind.ObjectMapper;

@RestController
public class CsvUploadController {

    @Autowired
    private CsvParserService csvParserService;

    @PostMapping("/upload")
    public ResponseEntity<List<CsvData>> uploadCsv(@RequestParam("file") MultipartFile file,
                                                   @RequestParam("selectedColumns") String selectedColumns) {
        try {
            ObjectMapper objectMapper = new ObjectMapper();
            Map<String, Object> selectedColumnsMap = objectMapper.readValue(selectedColumns, new TypeReference<Map<String, Object>>() {});

            List<CsvData> csvDataList = csvParserService.parseCsv(file.getInputStream(), selectedColumnsMap);
            return ResponseEntity.ok(csvDataList);
        } catch (IOException e) {
            e.printStackTrace();
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build();
        }
    }
}