Best method to export fixed width database --> text

[Florian Kammermann]

This is the method I ended up to export a table to fixed width text file:

<etl>

<connection id="database" url="jdbc:hsqldb:mem:mymemdb" user="SA" password=""/>

    <connection id="out" driver="text" url="file:C:\tmp\test_export.txt" />

    <connection id="janino" driver="janino" />

    <query connection-id="database">

    SELECT * FROM T_IMPORT

    <query connection-id="janino">

    import org.apache.commons.lang.StringUtils;

   

    set("fixedId", StringUtils.rightPad(String.valueOf(get("I_ID")), 10));

    set("fixedTyp", StringUtils.rightPad(String.valueOf(get("I_TYP")), 5));

    set("fixedDatum", StringUtils.rightPad(String.valueOf(get("I_BLA")), 10)); 

   

    next();

       <script connection-id="out">

$fixedId$fixedTyp$fixedDatum

</script>

</query>

    </query>

</etl>

Has somebody a better idea, to write it in shorter, more concise form?

[Fyodor Kupolov]

Hi Florian,

This example can be simplified if you try declarable formatting feature available in 1.1. Formatting/parsing options can be defined as properties inside connection element declaration of the text driver. Example:

    <connection id="in" url="jdbc:hsqldb:mem:in"/>

    <connection id="out" driver="text">

        # Use format.<COLUMN_NAME>.<FORMAT_PROPERTY> to define formatting rules

        format.id.pad_right=5

        format.create_date.pad_right=25

        format.create_date.type=date

        format.create_date.pattern=dd.MM.yyyy HH:mm:ss

    </connection>

    <script connection-id="in">

        create table table1 (

            ID INTEGER,

            CREATE_DATE TIMESTAMP

        );

        insert into table1 values('1', {ts '2012-01-09 20:11:11.123455'});

        insert into table1 values('2', {ts '2013-02-09 20:11:11.123455'});

    </script>

    <query connection-id="in">

        select * from table1

            <script connection-id="out">

                $id, $create_date

            </script>

    </query>

Additional documentation can be found in the JavaDoc for Text driver.

Best Regards,

Fyodor

[Bhargav ram]

Hi Fyodor,

I have a problem while using scriptella to transfer data from Mysql to CSV/text. Always first cell data is missing!!!??!!! PLEASE HELP

The table from which I m transferring data has 3 columns. All records either CSV/text are storing with 3 column values separated with column separator. But the first record is always getting truncated. Please refer the following. The highlighted record should be 2;0000343-346F-B81D-16CFE28FEE59; false

getID;testkey;flag
6F-B81D-16CFE28FEE59; false
3; 0001AB69-ED52-4516-AACA-06B85FEF9CFA; false
4; 00023D53-9426-4F60-AA99-802902DE5CF1; false
5; 000258D6-8D8A-46CB-B274-05967EE9FF04; false
6; 0003237D-8B31-4EB3-A482-DD24B916E663; false
7; 00039690-1809-46C1-9BC0-DB90E8B93CEF; false
8; 00045E80-26E9-4892-8EC3-AC713E14DE28; false
9; 000579E1-C9EC-4901-85D3-C46A8E5220E0; false
10; 00058557-AABB-445-85EC-C1B25B7DF98E; false
11; 00060537-BD98-48F-838E-F3DE41AAEFFB; false

[Fyodor Kupolov]

Hi Bhargav,

Can you please post this question on stackoverflow.com along with (possibly anonymized) source file. It's not immediately obvious where the problem is.

Thanks,

Fyodor