| Converting Excel to csv using PL/SQL [message #645992] |
Thu, 17 December 2015 06:07  |
venkat_bollu
Messages: 20
Registered: June 2009
|
Junior Member |
|
|
Dear Experts,
I wonder if Oracle has come up with an approach to convert/load the data from excel file to database.
I have been searching on google but found only vb script(requires excel on server) as best approach to convert the file format to csv, Unfortunately the MS excel can't be installed on the server due to its cost.
Please help me with any other approach in PL/SQL or any other work around that doesn't require additional tools/softwares on server.
I get the files from 10 countries, upto 100 files per month per country and in different formats. These users are so lazy like me to convert them to csv.
Oracle Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
OS: Windows 7
Thanks in advance
Venkat
|
|
|
|
|
|
| Re: Converting Excel to csv using PL/SQL [message #646000 is a reply to message #645998] |
Thu, 17 December 2015 09:03  |
venkat_bollu
Messages: 20
Registered: June 2009
|
Junior Member |
|
|
BlackSwan wrote on Thu, 17 December 2015 07:59PERL can read actual Excel files & can write CSV text files; plus it ships with Oracle database distributions.
I never worked on PERL, but I think installation is required. My further research gives me some idea about powershell. Let me see how it goes. Thanks for the response.
|
|
|
|
|
|
| Re: Converting Excel to csv using PL/SQL [message #646047 is a reply to message #646014] |
Fri, 18 December 2015 02:03 |
venkat_bollu
Messages: 20
Registered: June 2009
|
Junior Member |
|
|
ThomasG wrote on Thu, 17 December 2015 13:38It also somewhat depends on what you exactly mean with "Excel File".
I have to deal with any format either xls or xlsx, its all depends on the source from where the user gets these files.
Quote:
An "Old" binary .xls File? Then I would also suggest a PERL Module, for example:
http://search.cpan.org/~hmbrand/Spreadsheet-Read/Read.pm
Unfortunately can't use the PERL scripting.
Quote:
If you only have to deal with the newer.xlsx, which is basically an XML format, that could also be handled by using XML procedures in PL/SQL. See an example package here:
https://technology.amis.nl/2013/01/19/read-a-excel-xlsx-with-plsql/
I read this article yesterday itself after posting here, thanks for your reference as well, The drawback is you get the data grouped based on datatype, so I though modifying the source code today but fortunately my colleague helped with powershell scripting. I am testing the same at the moment, will post here once testing is done.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
