CODEWITHSUNDEEP
javaspringcsvparsingunivocity
scharrua
scharrua

Reputation: 93

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

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:

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

Upvotes: -1

Views: 68

Answers (1)

MalcolmInTheCenter
MalcolmInTheCenter

Reputation: 1605

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
}
  1. 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;
    }
}
  1. 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();
        }
    }
}

Upvotes: 0

Related Questions