Spring Boot解析CSV入库报错：Header name 'first_name'未找到

错误详情 #

Failed to complete request: java.lang.IllegalArgumentException: Header name 'first_name' not found. Available columns are: [first_name;last_name;account_type;email;address;zipcode;Location]
2022-10-14 19:29:31.510 ERROR 5256 --- [nio-8080-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is java.lang.IllegalArgumentException: Header name 'first_name' not found. Available columns are: [first_name;last_name;account_type;email;address;zipcode;Location]] with root cause.

Postman响应：

java.lang.IllegalArgumentException: Header name 'first_name' not found. Available columns are: [first_name;last_name;account_type;email;address;zipcode;Location]
	at com.univocity.parsers.common.record.RecordMetaDataImpl.getMetaData(RecordMetaDataImpl.java:50)
	at com.univocity.parsers.common.record.RecordMetaDataImpl.metadataOf(RecordMetaDataImpl

相关代码与数据 #

Entity类 #

@Entity
@Table(name = "account_details")
public class AccountDetails {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.TABLE)
    private int id;
    @Column(name = "first_name")
    private String firstName;
    @Column(name = "last_name")
    private String lastName;
    @Column(name = "account_type")
    private String accountType;
    @Column(name = "email")
    private String email;
    @Column(name = "address")
    private String adddress; // 注意：此处字段名拼写错误，应为address
    @Column(name = "zipcode")
    private String zipcode;
    @Column(name = "Location")
    private String location;

    public AccountDetails() {
        super();
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getAccountType() {
        return accountType;
    }

    public void setAccountType(String accountType) {
        this.accountType = accountType;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getAdddress() {
        return adddress;
    }

    public void setAdddress(String adddress) {
        this.adddress = adddress;
    }

    public String getZipcode() {
        return zipcode;
    }

    public void setZipcode(String zipcode) {
        this.zipcode = zipcode;
    }

    public String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }
}

Repository类 #

package com.alavu.fileupload_in_springboot_csv_to_db.entity;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface AccountRepository extends JpaRepository<AccountDetails,Integer> {
}

Controller类 #

@RestController
public class BankController {
    @Autowired
    AccountRepository service; 
    
    @PostMapping("/upload")
    public String uploadData(@RequestParam("file") MultipartFile file) throws IOException {
        List<AccountDetails> accountList = new ArrayList<>();
        InputStream inputStream = file.getInputStream();
        CsvParserSettings settings = new CsvParserSettings();
        settings.setHeaderExtractionEnabled(true);
        CsvParser parser = new CsvParser(settings);
        List<Record> parseAllRecords =  parser.parseAllRecords(inputStream);
        parseAllRecords.forEach(record->{
            AccountDetails account = new AccountDetails();
            account.setFirstName(record.getString("first_name"));
            account.setLastName(record.getString("last_name"));
            account.setAccountType(record.getString("account_type"));
            account.setAdddress(record.getString("address"));
            account.setEmail(record.getString("email"));
            account.setZipcode(record.getString("zipcode"));
            account.setLocation(record.getString("Location"));
            accountList.add(account);
        });
        service.saveAll(accountList);
        return "Upload Successfull !!!";
    }
}

CSV文件数据 #

first_name;last_name;account_type;email;address;zipcode;Location
smith;12se74;rb9012;Rachel;Booker;Sales;Manchester
rahul;04ap67;lg2070;Laura;Grey;Depot;London

问题分析 #

错误信息显示解析器将整个表头识别为单个列名first_name;last_name;account_type;email;address;zipcode;Location，而非拆分后的独立列。原因是你使用的univocity-parsers默认以逗号作为CSV分隔符，但你的CSV文件实际用分号分隔字段，导致解析器无法正确拆分表头和数据。

解决方案 #

在Controller的CsvParserSettings中显式设置分隔符为分号，修改后的代码如下：

@RestController
public class BankController {
    @Autowired
    AccountRepository service; 
    
    @PostMapping("/upload")
    public String uploadData(@RequestParam("file") MultipartFile file) throws IOException {
        List<AccountDetails> accountList = new ArrayList<>();
        InputStream inputStream = file.getInputStream();
        CsvParserSettings settings = new CsvParserSettings();
        settings.setHeaderExtractionEnabled(true);
        // 添加此行设置CSV分隔符为分号
        settings.setDelimiter(';');
        CsvParser parser = new CsvParser(settings);
        List<Record> parseAllRecords =  parser.parseAllRecords(inputStream);
        parseAllRecords.forEach(record->{
            AccountDetails account = new AccountDetails();
            account.setFirstName(record.getString("first_name"));
            account.setLastName(record.getString("last_name"));
            account.setAccountType(record.getString("account_type"));
            account.setAdddress(record.getString("address"));
            account.setEmail(record.getString("email"));
            account.setZipcode(record.getString("zipcode"));
            account.setLocation(record.getString("Location"));
            accountList.add(account);
        });
        service.saveAll(accountList);
        return "Upload Successfull !!!";
    }
}

另外，建议修正Entity类中address字段的拼写错误（当前为adddress多了一个d），避免后续数据库存储或字段映射出现异常。

内容的提问来源于stack exchange，提问作者ALAVUDHEEN E