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

How To Produce Daily Database Table Dumps In CSV Format

11.16.2007
| 7824 views |
  • submit to reddit
        The following code demonstrates how to produce CSV files with dynamic
file name pattern based on a current day. The produced files have the following naming format:
TABLE_NAME_MM_DD_YYYY.csv

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
    <properties> <!-- Configure table name -->
        table_name=test
    </properties>
    <connection id="in" driver="auto" url="jdbc:oracle:thin:@localhost:1521:ORCL" 
      classpath="ojdbc14.jar" user="scott" password="tiger"/>
    <connection id="out" driver="csv" url="${table_name}_${etl.date.now('MM_dd_yyyy')}.csv" />
    <query connection-id="in"> <!-- Query table rows -->
        SELECT * FROM ${table_name}
        <script connection-id="out"> <!-- Export each row into a CSV -->
            $ID, $Name, $Surname <!-- Use column names from selected table -->
        </script>
    </query>
</etl>
Use <a href="http://scriptella.javaforge.com">Scriptella ETL</a> to run the example. 

See <a href="http://snippets.dzone.com/posts/show/4862">How to execute an ETL file</a> from command line, Ant or directly from Java .