Big Data/BI Zone
Big Data/BI 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)

02.13.2007
| 22978 views |
  • submit to reddit 
        This example demonstrates usage of <a href="http://scriptella.javaforge.com">Scriptella ETL Tool</a> 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

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);
"Starting from scratch" is seductive but disease ridden
-Pithy Advice for Programmers