Never been to DZone Snippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Load CSV data into a database (Scriptella ETL tool) (See related posts)

This example demonstrates usage of Scriptella ETL Tool to load CSV data into a database table.

Input CSV file data.csv:
id,priority,summary,status
1,Critical,NullPointerException in Main class,Open
5,Low,"Checkstyle, PMD, Findbugs issues",Reopened
7,Low,Maven integration,Open
10,High,SPI API,Closed

The CSV loading script has the following content:
<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
  <connection id="in" driver="csv" url="data.csv"/>
  <connection id="out" driver="oracle" url="jdbc:oracle:thin:@localhost:1521:ORCL" 
      classpath="ojdbc14.jar" user="scott" password="tiger"/>
  <!-- Copy all CSV rows to a database table -->
  <query connection-id="in">
      <!-- Empty query means select all columns -->
      <script connection-id="out">
          INSERT INTO Table_Name VALUES (?id,?priority, ?summary, ?status)
      </script>
  </query>
</etl>


Use RegEx to filter CSV data:
<query connection-id="in">
    <!--Select bugs with status open or reopened.-->
    ,,,open|reopened
    <!--Inserts imported rows into a database-->
    <script connection-id="out">
       INSERT INTO Table_Name VALUES (?id, ?priority, ?summary, ?status);
    </script>
</query>


Comments on this post

ejboy posts on May 08, 2007 at 05:19
When CSV file has no header line or a column name contains spaces, use column index.
Example:
INSERT INTO Table_Name VALUES (?1, ?2, ?3, ?4);

ejboy posts on May 09, 2007 at 15:39
For additional details and configuration options see CSV Driver JavaDoc

You need to create an account or log in to post comments to this site.


Click here to browse all 5140 code snippets

Related Posts