title: How can I configure an Excel spreadsheet as a javax.sql.DataSource?
body: <p>I have a Spring Boot 1.3.0 (Java 8) application that needs to use Excel as a data source. (POI and JXL do not work due to type of Excel file.) The application needs to run in an unix environment. I have the application configured to set the datasource url and driver-class-name via a yml file. </p>
  
  <p>Is there a driver (preferably available in the maven repo) that can use Excel as a data source? What value should I specify for the url? </p>
  
  <p><strong>Edit</strong></p>
  
  <p>Here's the code from an attempt to use the Excel spreadsheet as a javax.sql.DataSource:</p>
  
  <p>src/main/resources/application.yml</p>
  
  <pre><code>---
  spring:
      profiles:
          active: development
  ---        
  spring:
      profiles: development
      datasource:
          url: jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/dev/testproj/src/main/resources/test.xls
          driver-class-name: sun.jdbc.odbc.JdbcOdbcDriver
  </code></pre>
  
  <p>src/main/java/com/test/TestApplication.java</p>
  
  <pre><code>package com.test;
  
      import org.springframework.boot.SpringApplication;
      import org.springframework.boot.autoconfigure.SpringBootApplication;
  
      @SpringBootApplication
      public class TestApplication {
  
          public static void main(String[] args) {
              SpringApplication.run(TestApplication.class, args);
          }
      }
  </code></pre>
  
  <p>src/main/java/com/test/batch/BatchConfiguration.java</p>
  
  <pre><code>package com.test.batch;
  
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.util.List;
  
  import javax.sql.DataSource;
  
  import org.springframework.batch.core.Job;
  import org.springframework.batch.core.Step;
  import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
  import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
  import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
  import org.springframework.batch.core.launch.support.RunIdIncrementer;
  import org.springframework.batch.item.ItemProcessor;
  import org.springframework.batch.item.ItemReader;
  import org.springframework.batch.item.ItemWriter;
  import org.springframework.batch.item.database.JdbcCursorItemReader;
  import org.springframework.context.annotation.Bean;
  import org.springframework.context.annotation.Configuration;
  import org.springframework.jdbc.core.RowMapper;
  
  @Configuration
  @EnableBatchProcessing
  public class BatchConfiguration {
  
      @Bean
      public ItemReader&lt;String&gt; reader(DataSource dataSource) {
  
          JdbcCursorItemReader&lt;String&gt; itemReader = new JdbcCursorItemReader&lt;String&gt;();
          itemReader.setDataSource(dataSource);
          System.out.println(dataSource);
          itemReader.setSql("SELECT a from Sheet1");
          itemReader.setRowMapper(new RowMapper&lt;String&gt;() { 
              public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                  String here= rs.getString(0);
                  System.out.println(here);
                  return here;
              }
          });
  
         return itemReader; 
      }
  
      @Bean
      public ItemProcessor&lt;String, String&gt; processor() {
          return new ItemProcessor&lt;String, String&gt;() { 
              public String process(final String data) throws Exception {
                  return data;
              }
          };
      }
  
      @Bean
      public ItemWriter&lt;String&gt; writer(DataSource dataSource) {
          return new ItemWriter&lt;String&gt;() {
              public void write(List&lt;? extends String&gt; items) throws Exception {
              }
          };
      }
  
      @Bean
      public Step step(StepBuilderFactory stepBuilderFactory, ItemReader&lt;String&gt; reader,  ItemProcessor&lt;String, String&gt; processor, ItemWriter&lt;String&gt; writer) {
          return stepBuilderFactory.get("step")
                  .&lt;String, String&gt; chunk(1)
                  .reader(reader)
                  .processor(processor)
                  .writer(writer)
                  .build();
      }
  
  
      @Bean
      public Job importUserJob(JobBuilderFactory jobs, Step step) {
          return jobs.get("importUserJob")
                  .incrementer(new RunIdIncrementer())
                  .flow(step)
                  .end()
                  .build();
      }   
  
  }
  </code></pre>
  
  <p>build.gradle</p>
  
  <pre><code>buildscript {
      ext {
          springBootVersion = '1.3.0.RELEASE'
      }
      repositories {
          mavenCentral()
      }
      dependencies {
          classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}") 
      }
  }
  
  apply plugin: 'java'
  apply plugin: 'eclipse'
  apply plugin: 'spring-boot' 
  
  jar {
      baseName = 'test-load'
      version = '1.0'
  }
  sourceCompatibility = 1.8
  targetCompatibility = 1.8
  
  repositories {
      mavenCentral()
  }
  
  
  dependencies {
      compile('org.springframework.boot:spring-boot-starter-batch')
      compile('org.springframework.boot:spring-boot-starter-integration')
      testCompile('org.springframework.boot:spring-boot-starter-test') 
  }
  
  
  eclipse {
      classpath {
           containers.remove('org.eclipse.jdt.launching.JRE_CONTAINER')
           containers 'org.eclipse.jdt.launching.JRE_CONTAINER/org.eclipse.jdt.internal.debug.ui.launcher.StandardVMType/JavaSE-1.8'
      }
  }
  
  task wrapper(type: Wrapper) {
      gradleVersion = '2.9'
  }
  
  tasks.withType(Test) {
      scanForTestClasses = false
      include "**/*Test.class" 
  }
  </code></pre>
  
  <p>The following error is given when executing the gradle task bootRun:</p>
  
  <pre><code>2015-12-28 16:01:18.530 ERROR 1356 --- [           main] o.s.batch.core.step.AbstractStep         : Encountered an error executing step step in job importUserJob
  
  org.springframework.batch.item.ItemStreamException: Failed to initialize the reader
      at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open(AbstractItemCountingItemStreamItemReader.java:147) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.batch.item.support.CompositeItemStream.open(CompositeItemStream.java:96) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.batch.core.step.tasklet.TaskletStep.open(TaskletStep.java:310) ~[spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:197) ~[spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:67) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:169) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:144) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:134) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50) [spring-core-4.2.3.RELEASE.jar:4.2.3.RELEASE]
      at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:128) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_66]
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_66]
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_66]
      at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_66]
      at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
      at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
      at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) [spring-batch-core-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
      at com.sun.proxy.$Proxy45.run(Unknown Source) [na:na]
      at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.execute(JobLauncherCommandLineRunner.java:215) [spring-boot-autoconfigure-1.3.0.RELEASE.jar:1.3.0.RELEASE]
      at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.executeLocalJobs(JobLauncherCommandLineRunner.java:232) [spring-boot-autoconfigure-1.3.0.RELEASE.jar:1.3.0.RELEASE]
      at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.launchJobFromProperties(JobLauncherCommandLineRunner.java:124) [spring-boot-autoconfigure-1.3.0.RELEASE.jar:1.3.0.RELEASE]
      at org.springframework.boot.autoconfigure.batch.JobLauncherCommandLineRunner.run(JobLauncherCommandLineRunner.java:118) [spring-boot-autoconfigure-1.3.0.RELEASE.jar:1.3.0.RELEASE]
      at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:792) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
      at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:776) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
      at org.springframework.boot.SpringApplication.afterRefresh(SpringApplication.java:763) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
      at org.springframework.boot.SpringApplication.doRun(SpringApplication.java:356) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
      at org.springframework.boot.SpringApplication.run(SpringApplication.java:295) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
      at org.springframework.boot.SpringApplication.run(SpringApplication.java:1112) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
      at org.springframework.boot.SpringApplication.run(SpringApplication.java:1101) [spring-boot-1.3.0.RELEASE.jar:1.3.0.RELEASE]
      at com.test.TestApplication.main(TestApplication.java:10) [bin/:na]
  Caused by: org.springframework.jdbc.BadSqlGrammarException: Executing query; bad SQL grammar [SELECT a from Sheet1]; nested exception is java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: SHEET1
      at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:91) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE]
      at org.springframework.batch.item.database.JdbcCursorItemReader.openCursor(JdbcCursorItemReader.java:131) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.batch.item.database.AbstractCursorItemReader.doOpen(AbstractCursorItemReader.java:406) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open(AbstractItemCountingItemStreamItemReader.java:144) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      ... 36 common frames omitted
  Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: SHEET1
      at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.jdbc.JDBCPreparedStatement.&lt;init&gt;(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.springframework.batch.item.database.JdbcCursorItemReader.openCursor(JdbcCursorItemReader.java:120) ~[spring-batch-infrastructure-3.0.5.RELEASE.jar:3.0.5.RELEASE]
      ... 38 common frames omitted
  Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: SHEET1
      at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.ParserDQL.readTableName(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.ParserCommand.compilePart(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.ParserCommand.compileStatement(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.Session.compileStatement(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.StatementManager.compile(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
      at org.hsqldb.Session.execute(Unknown Source) ~[hsqldb-2.3.3.jar:2.3.3]
  </code></pre>