Best method to export fixed width database --> text

126 views
Skip to first unread message

Florian Kammermann

unread,
Mar 12, 2013, 12:16:59 PM3/12/13
to scrip...@googlegroups.com
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

unread,
Mar 13, 2013, 4:03:56 PM3/13/13
to scrip...@googlegroups.com
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

unread,
Sep 12, 2014, 10:06:00 AM9/12/14
to scrip...@googlegroups.com
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

unread,
Sep 15, 2014, 3:27:47 PM9/15/14
to scrip...@googlegroups.com
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
Reply all
Reply to author
Forward
0 new messages