java -How to insert excel data to db using batch process?

Question

I am trying to insert excel data to db using batch process using spring. It appears the file can not be parsed correctly as I get the following error :

org.springframework.batch.item.file.FlatFileParseException: Parsing error at line: 2 in resource=[class path resource [BulkData.xlsx]], input=[��ӵL��b.j"�"%5�].

Here is my batch configuration file for spring

    @Configuration
    @EnableBatchProcessing
    public class BatchConfig {
        @Autowired
        private DataSource dataSource;
        @Autowired
        private JobBuilderFactory jobBuilderFactory;
        @Autowired
        private StepBuilderFactory stepBuilderFactory;
        @Bean
        public FlatFileItemReader<User> reader()  {         
         FlatFileItemReader<User> reader = new FlatFileItemReader<>();
            reader.setResource(new ClassPathResource("BulkData.xlsx"));
            reader.setLineMapper(getLineMaper());
            reader.setLinesToSkip(1);
            return reader;
        }
          private LineMapper<User> getLineMaper() {
              DefaultLineMapper<User> lineMapper = new DefaultLineMapper<>();
    DelimitedLineTokenizer lineTokenizer = new DelimitedLineTokenizer()        lineTokenizer.setNames(new String[]{"firstName", "lastName","email"});
            lineTokenizer.setIncludedFields(new int[]{0, 1, 2});
            BeanWrapperFieldSetMapper<User> fieldSetMapper = new 
            BeanWrapperFieldSetMapper<>();
            fieldSetMapper.setTargetType(User.class);
    
            lineMapper.setLineTokenizer(lineTokenizer);
            lineMapper.setFieldSetMapper(fieldSetMapper);
           return lineMapper;
        }
       @Bean
        public UserItemProcesser processer() {
            return new UserItemProcesser();
        }
        public JdbcBatchItemWriter<User> writer() {
            JdbcBatchItemWriter<User> writer = new 
            JdbcBatchItemWriter<>();
            writer.setItemSqlParameterSourceProvider(new  
            BeanPropertyItemSqlParameterSourceProvider<User>());
            writer.setSql("insert into user(firstName,lastName,email) 
            values(:firstName,:lastName,:email)");
            writer.setDataSource(this.dataSource);
            return writer;
    
        }
        @Bean
        public Job importUserJob() {
            return this.jobBuilderFactory.get("USER-IMPORT-JOB")
                    .incrementer(new RunIdIncrementer())
                    .flow(step1())
                    .end()
                    .build();
        }
        @Bean
        public Step step1() {
             return this.stepBuilderFactory.get("step1")
                    .<User, User>chunk(10)
                    .reader(reader())
                    .processor(processer())
                    .writer(writer())
                    .build();
        }
    }

Model

the model class for the data that has to be parsed from the excel document.

package com.example.exceltodb.model;

public class User {


    private String firstName;
    private String lastName;
    private String email;
    // regular java beans constructors, getters and setters

}

is there a specific format xslx files have to have to be parsed correctly by the spring parser, or have I done something wrong with the parsing. Considering the error, it might be some kind of unwanted characters in the file.

Answer 1

The FlatFileItemReader is designed to read flat files, not Microsoft Excel files. You can use the reader from spring-batch-excel extension to read Excel files.