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

107 views
Skip to first unread message

Migue Ram

unread,
Oct 6, 2014, 5:45:23 PM10/6/14
to scrip...@googlegroups.com
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

unread,
Oct 7, 2014, 1:32:48 AM10/7/14
to scrip...@googlegroups.com

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

unread,
Oct 8, 2014, 7:01:02 AM10/8/14
to scrip...@googlegroups.com
I've looked into issue and this problem has something to do how excel stores data internally.
<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

unread,
Oct 20, 2014, 1:30:00 PM10/20/14
to scrip...@googlegroups.com
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)
Reply all
Reply to author
Forward
0 new messages