CODEWITHSUNDEEP
mysqlspringspring-batch
CHARAFI Saad
CHARAFI Saad

Reputation: 1440

Spring batch xlxs file to MySQL database

I'm new with spring batch and I implemented an example of a cvs file in which columns are separated by comma, but I have an xlxs file which contains just columns without separator. I want to insert the data without using separators in my xlxs files just columns.

Here is the contents of job-report

<bean id="report" class="com.mkyong.model.Report" scope="prototype" />
<batch:job id="reportJob">
    <batch:step id="step1">
      <batch:tasklet>
        <batch:chunk reader="cvsFileItemReader" writer="mysqlItemWriter"
        commit-interval="2">
        </batch:chunk>
      </batch:tasklet>
    </batch:step>
</batch:job>

  <bean id="cvsFileItemReader" class="org.springframework.batch.item.file.FlatFileItemReader">

<!-- Read a csv file -->
<property name="resource" value="classpath:cvs/report.csv" />

<property name="lineMapper">
    <bean class="org.springframework.batch.item.file.mapping.DefaultLineMapper">
      <!-- split it -->
      <property name="lineTokenizer">
            <bean
          class="org.springframework.batch.item.file.transform.DelimitedLineTokenizer">
            <property name="names" value="date,impressions,clicks,earning" />
        </bean>
      </property>
      <property name="fieldSetMapper">
             <!-- return back to reader, rather than a mapped object. -->
             <!--
         <bean class="org.springframework.batch.item.file.mapping.PassThroughFieldSetMapper" />
              -->
          <!-- map to an object -->
          <bean
            class="org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper">
            <property name="prototypeBeanName" value="report" />
          </bean>
      </property>

      </bean>
  </property>

  </bean>
 <bean id="mysqlItemWriter"
class="org.springframework.batch.item.database.JdbcBatchItemWriter">
<property name="dataSource" ref="dataSource" />
<property name="sql">
  <value>
        <![CDATA[
            insert into RAW_REPORT(DATE,IMPRESSIONS,CLICKS,EARNING)
        values (:date, :impressions, :clicks, :earning)
        ]]>
  </value>
</property>
<!-- It will take care matching between object property and sql name parameter -->
<property name="itemSqlParameterSourceProvider">
    <bean
    class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" />
</property>

when I execute my code this error appears

org.springframework.batch.item.file.FlatFileParseException: Parsing error at line: 1 in resource=[class path resource [cvs/report.csv]], input=[123456;139,237;37;123456]

P.S.: In my file each column contains values ​​without separator.

Upvotes: 0

Views: 1917

Answers (1)

Monzurul Shimul
Monzurul Shimul

Reputation: 8396

If you want to read the input data of your batch job from a .XLS or .XLSX file that was created with Excel, you need to use Spring Batch extension which contains ItemReader implementations for Excel. You need to build the jar from source. Here is the link: https://github.com/spring-projects/spring-batch-extensions

And use it like below:

<bean id="excelReader" class="org.springframework.batch.item.excel.poi.PoiItemReader">
    <property name="resource" value="/path/to/your/excel/file" />
    <property name="rowMapper">
        <bean class="org.springframework.batch.item.excel.mapping.BeanWrapperowMapper">
            <property name="targetType" value="com.your.package.Model" />
        <bean>
    </property>
</bean>

Reference: https://github.com/spring-projects/spring-batch-extensions/tree/master/spring-batch-excel

Upvotes: 1

Related Questions