8 Replies Latest reply on Mar 17, 2010 10:07 AM by BluShadow

    reading xls files from a ftp server and putting into database

    ravikumar.sv
      Hi All,

      Here's my db details
      SQL> select * from v$version;
      
      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
      PL/SQL Release 10.2.0.4.0 - Production
      CORE    10.2.0.4.0      Production
      TNS for HPUX: Version 10.2.0.4.0 - Production
      NLSRTL Version 10.2.0.4.0 - Production
      I have a requirement where we will be receiving xls reports(not csv) in a ftp server and we need to read their data and put them into a oracle table as and when we receive. And this needs to be done automatically.
      Can it be done using pl/sql?
      I even checked jXLS api used for reading excel data...But I don't want to use java unless until it is required and pl/sql cannot do the same.
      Can anybody point to some links or provide some info on how to achieve the same?

      Best Regards,
      Ravi Kumar
        • 2. Re: reading xls files from a ftp server and putting into database
          ravikumar.sv
          can external tables handle xls files??
          I know they can work on csv files, but I am not sure about xls files.

          Thanks,
          Ravi Kumar
          • 3. Re: reading xls files from a ftp server and putting into database
            009
            A little more search got me to Excel in external table ;)

            *009*
            1 person found this helpful
            • 4. Re: reading xls files from a ftp server and putting into database
              Marwim
              Hello,

              another way is Oracle heterogenious connectivity

              excel file to import database

              Regards
              Marcus
              1 person found this helpful
              • 5. Re: reading xls files from a ftp server and putting into database
                ravikumar.sv
                Thank you for the link...

                Seems like it is using java :-(

                But can this api (or available jXLS api) be placed on database server(If so where I can place this api on server) and can I write java stored procedure directly??

                My concern here is I dont want to create an intermediary csv file...I want to automate the reading from xls and putting into database.

                regards,
                Ravi Kumar
                • 6. Re: reading xls files from a ftp server and putting into database
                  BluShadow
                  Well, something that may help is Chris Poole's XUTL_FTP package:

                  http://www.chrispoole.co.uk/apps/xutlftp.htm

                  This would certainly allow you to FTP the .xls file to a location where you can then read it.

                  As for reading it, your best option, as it's a .xls file and not a flat file, is to use heterogeneous services (i.e. connect to the Excel workbook as if it's a database using an Excel ODBC connection), thus treating it as a database over a database link). Note though that this will require the .xls file to reside on a Windows server.

                  example:

                  1- Go to Control Panel>Administrative Tools>Data Sources (ODBC)>System DSN and create a data source with appropriate driver. Name it EXCL.

                  2- In %ORACLE_HOME%\Network\Admin\Tnsnames.ora fie add entry:
                  EXCL =
                  (DESCRIPTION =
                  (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.0.24)(PORT = 1521))
                  )
                  (CONNECT_DATA =
                  (SID = EXCL)
                  )
                  (HS = OK)
                  )
                  Here SID is the name of data source that you have just created.

                  3- In %ORACLE_HOME%\Network\Admin\Listener.ora file add:
                  (SID_DESC = 
                  (PROGRAM = hsodbc) 
                  (SID_NAME = <hs_sid>) 
                  (ORACLE_HOME = <oracle home>) 
                  )
                  under SID_LIST_LISTENER like:
                  SID_LIST_LISTENER =
                  (SID_LIST =
                  (SID_DESC =
                  (SID_NAME = PLSExtProc)
                  (ORACLE_HOME = d:\ORA9DB)
                  (PROGRAM = extproc)
                  )
                  (SID_DESC =
                  (GLOBAL_DBNAME = ORA9DB)
                  (ORACLE_HOME = d:\ORA9DB)
                  (SID_NAME = ORA9DB)
                  )
                  (SID_DESC = 
                  (PROGRAM = hsodbc) 
                  (SID_NAME = EXCL) 
                  (ORACLE_HOME = D:\ora9db) 
                  ) ) 
                  Dont forget to reload the listener
                  c:\> lsnrctl reload
                  4- In %ORACLE_HOME%\hs\admin create init<HS_SID>.ora. For our sid EXCL we create file initexcl.ora.

                  In this file set following two parameters:
                  HS_FDS_CONNECT_INFO = excl
                  HS_FDS_TRACE_LEVEL = 0
                  5- Now connect to Oracle database and create database link with following command:
                  SQL> CREATE DATABASE LINK excl
                  2 USING 'excl'
                  3 /
                  
                  Database link created.
                  Now you can perform query against this database like you would for any remote database.
                  SQL> SELECT table_name FROM all_tables@excl;
                  
                  TABLE_NAME
                  ------------------------------
                  DEPT
                  EMP
                  Or refer to this Article...
                  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206
                  1 person found this helpful
                  • 7. Re: reading xls files from a ftp server and putting into database
                    ravikumar.sv
                    Thanks for that info...

                    One small question...
                    does this HS connectivity works for oracle db on HP unix server??
                    And also each time do I need to create a data source connection for each and every excel work book?
                    Because we will be receiving a number of generated reports and throughout the year. We dont want to restart the listener often because there are few other applications running on the server which might get affected.

                    Regards,
                    Ravi Kumar
                    • 8. Re: reading xls files from a ftp server and putting into database
                      BluShadow
                      Unix server makes it a little more difficult, although I think (I've not done it myself) you can get ODBC drivers for unix too, but you'd have to look into that.

                      Unfortunately, it is one ODBC connection for one Excel workbook, as they are being treated as databases. You wouldn't expect to set up several normal databases all with the same connection string, so why would seperate workbooks be any different. ;)

                      Perhaps you could use the FTP package to copy the file across and rename it to a standard fixed name so that you only require one connection to that and just replace the underlying workbook each time.