xslx file - Read numeric values as string - Read numeric values without round.

[Migue Ram]

Hello, 

I have the next problem...

I have a xlsx file with information to import. 

I imported the information of the xlsx to a table of my database, I thought that the job was finished. 

But I found that some numbers were rounded when the process readed the informacion of the xlsx.

I can't format the numbers because some values of columns has more decimal and someones less(the client want to see the value as in the xlsx file). 

Also some cells have two or more numbers delimiter by "/", and other cells nothing.

The xlsx file is used in others process so I can't modify it :(

The columns of the table are varchar2(Oracle).

Some examples of the errors:

Excel  ===    Database(imported information)

87.60           -> 87.60000...01

0.3600752   -> .36007520000...04

0.01200668 -> .012006679999...

0.3300         -> .32999...96

0.450           -> .44999...96

Information of my configuration file... 

<connection id="informacionXlsx" 

       driver="net.pcal.sqlsheet.XlsDriver" 

       url="jdbc:xls:file:$data.xlsx.path?readStreaming=true" >

</connection>

<query connection-id="informacionXlsx">

SELECT * FROM "Catalogo";

<script connection-id="oracleDB" if="rownum>1">

INSERT INTO

MY_TABLE (ID, COL_2, COL_3, COL_4, COL_5)

VALUES (?1, ?2, ?3, ?4, ?5);

</script>

</query>

I thought these possible solutions but I don't know if are possible...

Disable the round of the number with some configuration to do the inserts with the numbers without round.

Add some configuration to read the values of the cells as String to insert in the table the same information of the xslx file without problems.

I searched information about these topics but I can't found nothing that help me... :S

Did you have sometime this problem?

Thank you for your time! :)

Regards... 

Migue.

[Ivan Vasiliev]

If you provide sample of xlsx file I can take a look whats wrong.

07 окт. 2014 г. 0:45 пользователь "Migue Ram" <mra...@ats-connection.com> написал:

--
You received this message because you are subscribed to the Google Groups "Scriptella ETL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scriptella+...@googlegroups.com.
To post to this group, send email to scrip...@googlegroups.com.
Visit this group at http://groups.google.com/group/scriptella.
For more options, visit https://groups.google.com/d/optout.

[Ivan Vasiliev]

I've looked into issue and this problem has something to do how excel stores data internally.

http://java.dzone.com/articles/when-reading-excel-poi-beware

You will have to add intermediate script into your logic to format numbers properly. E.g. using janino driver:

http://scriptella.javaforge.com/docs/api/scriptella/driver/janino/package-summary.html#package_description

<query connection-id="informacionXlsx">

SELECT * FROM "Catalogo";

                <query connection-id="java">

                            set("Rounded5", new BigDecimal(get("5")).setScale(2, RoundingMode.HALF_UP).floatValue())

                            next()

<script connection-id="oracleDB" if="rownum>1">

INSERT INTO

MY_TABLE (ID, COL_2, COL_3, COL_4, COL_5)

VALUES (?1, ?2, ?3, ?4, ?Rounded5);

</script>

                </query>

</query>

С уважением,
Васильев Иван

[Migue Ram]

Thanks Ivan, 

I modified the configuration file with the idea of your last response and it is running correctly! 

Thanks again!

Regards, 

Migue! (from Argentina)