No need for sql loader! « abughali.com

20Feb/100

No need for sql loader!

Scriptella is an open source ETL (Extract-Transform-Load) and script execution tool written in Java. I used it to load data from csv file to Oracle. Of course it can be used to load from any data source to any data source. It has been used as a migration tool for data for many tasks.

Sample etl.xml file :

<etl>
  <connection id="in" driver="csv" url="data.csv">
  headers=false
  </connection>
  <connection id="out" driver="oracle" url="jdbc:oracle:thin:@127.0.0.1:1521:orcl"
      classpath="ojdbc14.jar" user="test" password="test"/>
  <!-- Empty the destination table -->
  <script connection-id="out">
    truncate table TEMP1 ;
  </script>
 
  <query connection-id="in">
      <!-- Empty query means select all columns -->
   <script connection-id="out">
          INSERT INTO TEMP1 VALUES ('$1', $2)
      </script>
  </query>
</etl>

in data.csv :

text1,1
text2,2
text3,3

Sample Java class (ojdbc14.jar and scriptella.jar should be in your class path) :

import scriptella.execution.EtlExecutor;
import scriptella.execution.EtlExecutorException;
 
public class MainRunner {
    public static void main(String[] args) {
 
    try {
         EtlExecutor.newExecutor(new File("etl.xml")).execute();
         } catch (EtlExecutorException e) {
          e.printStackTrace();
           } 
    }
}