Big Data/Analytics Zone is brought to you in partnership with:
DZone 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

Fyodor has posted 14 posts at DZone. View Full User Profile

Load CSV Data Into A Database (Scriptella ETL Tool)

  • submit to reddit
        This example demonstrates usage of <a href="">Scriptella ETL Tool</a> to load CSV data into a database table.

Input CSV file data.csv:
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:
  <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)

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


Fyodor Kupolov replied on Wed, 2006/12/20 - 10:28am

For additional details and configuration options see CSV Driver JavaDoc

Fyodor Kupolov replied on Wed, 2006/12/20 - 10:28am

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);