How to import data file from Excel or Lotus1-2-3 or word etc to Oracle


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: How to import data file from Excel or Lotus1-2-3 or word etc to Oracle

  1. #1
    Arun Goyal Guest

    How to import data file from Excel or Lotus1-2-3 or word etc to Oracle


    Hi,

    I'll be oblised if u can tell me that how do I transfer my data in the Excel
    or Lotus format to Oracle 8.05

    urs
    friendly etc.
    arongoyal@yahoo.com

  2. #2
    mister pants Guest

    Re: How to import data file from Excel or Lotus1-2-3 or word etc to Oracle



    Export your data to text and then use the Oracle SQL*Loader

    cha-ching

    "Arun Goyal" <arongoyal@yahoo.com> wrote:
    >
    >Hi,
    >
    >I'll be oblised if u can tell me that how do I transfer my data in the Excel
    >or Lotus format to Oracle 8.05
    >
    >urs
    >friendly etc.
    >arongoyal@yahoo.com



  3. #3
    Jerry Guest

    Re: How to import data file from Excel or Lotus1-2-3 or word etc to Oracle


    "mister pants" <mister_pants@x.x> wrote:
    >
    >
    >Export your data to text and then use the Oracle SQL*Loader
    >
    >cha-ching
    >
    >"Arun Goyal" <arongoyal@yahoo.com> wrote:
    >>
    >>Hi,
    >>
    >>I'll be oblised if u can tell me that how do I transfer my data in the

    Excel
    >>or Lotus format to Oracle 8.05
    >>
    >>urs
    >>friendly etc.
    >>arongoyal@yahoo.com

    >



    A better method is to use the plsql toolkit and send it directly to excel.

    Here is an example

    PROCEDURE foo(p_jobno IN VARCHAR2, p_task IN VARCHAR2 DEFAULT NULL)
    IS

    CURSOR gm IS
    SELECT /*+ RULE */ pap.segment1,
    DECODE(p_task,NULL,NULL,SUBSTR(t.task_number,1,3)) Area
    ,ei.expenditure_type EXPTYPE
    ,SUM(ei.accrued_revenue) Revenue
    ,SUM(ei.burden_cost) Cost
    , SUM(ei.accrued_revenue)-SUM(ei.burden_cost) Margin
    FROM
    pa.pa_expenditure_items_all ei,
    pa.pa_tasks t,
    pa.pa_projects_all pap
    WHERE
    pap.segment1=UPPER(p_jobno)
    AND t.project_id=pap.project_id
    AND ei.task_id = t.task_id
    AND t.task_number LIKE p_task||'%'
    GROUP BY pap.segment1,
    DECODE(p_task,NULL,NULL,SUBSTR(t.task_number,1,3)),
    ei.expenditure_type;


    BEGIN
    owa_util.MIME_HEADER('application/excel');
    htp.htmlopen;
    htp.bodyopen(cattributes=>'class="PortletBodyColor" ');
    htp.tableopen('border=1 align=left',
    cattributes=> 'cellspacing=0 cellpadding=0 class="BannerColor" ');
    htp.TABLECAPTION('down and dirty gross margin report<br>'||p_jobno);
    htp.TABLEHEADER('Project ',cattributes=>'CLASS="PortletText1"');
    htp.TABLEHEADER('Area ',cattributes=>'CLASS="PortletText1"');
    htp.TABLEHEADER('Expendituretype ',cattributes=>'CLASS="PortletText1"');
    htp.TABLEHEADER('Revenue ',cattributes=>'CLASS="PortletText1"');
    htp.TABLEHEADER('Cost ',cattributes=>'CLASS="PortletText1"');
    htp.TABLEHEADER('Margin ',cattributes=>'align "right"
    CLASS="PortletText1"');
    FOR rec IN gm LOOP
    htp.tablerowopen;
    htp.tabledata(rec.segment1, cattributes=>'align left nowrap CLASS="PortletText1"');
    htp.tabledata(rec.area, cattributes=>'align left nowrap CLASS="PortletText1"');

    htp.tabledata(rec.exptype, cattributes=>'align left nowrap CLASS="PortletText1"');
    htp.tabledata(rec.revenue, cattributes=>'align left nowrap CLASS="PortletText1"');
    htp.tabledata(rec.cost, cattributes=>'align left nowrap CLASS="PortletText1"');
    htp.tabledata(rec.margin, cattributes=>'align=right nowrap CLASS="PortletText1"');
    htp.tablerowclose;

    END LOOP;

    htp.bodyclose;
    htp.HTMLCLOSE;
    EXCEPTION
    WHEN OTHERS THEN
    v_message := SUBSTR(SQLERRM, 1, 150);
    Foo_Lib.error_page(v_message);

    END foo;




  4. #4
    dman Guest

    Re: How to import data file from Excel or Lotus1-2-3 or word etc to Oracle


    Hi,

    How can I get this PLSQL toolkit and more information for this toolkit?

    Best regard,
    dman

    "Jerry" <jdbivens@sbec.com> wrote:
    >
    >"mister pants" <mister_pants@x.x> wrote:
    >>
    >>
    >>Export your data to text and then use the Oracle SQL*Loader
    >>
    >>cha-ching
    >>
    >>"Arun Goyal" <arongoyal@yahoo.com> wrote:
    >>>
    >>>Hi,
    >>>
    >>>I'll be oblised if u can tell me that how do I transfer my data in the

    >Excel
    >>>or Lotus format to Oracle 8.05
    >>>
    >>>urs
    >>>friendly etc.
    >>>arongoyal@yahoo.com

    >>

    >
    >
    >A better method is to use the plsql toolkit and send it directly to excel.
    >
    >Here is an example
    >
    >PROCEDURE foo(p_jobno IN VARCHAR2, p_task IN VARCHAR2 DEFAULT NULL)
    >IS
    >
    >CURSOR gm IS
    >SELECT /*+ RULE */ pap.segment1,
    > DECODE(p_task,NULL,NULL,SUBSTR(t.task_number,1,3)) Area
    > ,ei.expenditure_type EXPTYPE
    > ,SUM(ei.accrued_revenue) Revenue
    > ,SUM(ei.burden_cost) Cost
    > , SUM(ei.accrued_revenue)-SUM(ei.burden_cost) Margin
    >FROM
    > pa.pa_expenditure_items_all ei,
    > pa.pa_tasks t,
    > pa.pa_projects_all pap
    >WHERE
    > pap.segment1=UPPER(p_jobno)
    > AND t.project_id=pap.project_id
    > AND ei.task_id = t.task_id
    > AND t.task_number LIKE p_task||'%'
    >GROUP BY pap.segment1,
    > DECODE(p_task,NULL,NULL,SUBSTR(t.task_number,1,3)),
    > ei.expenditure_type;
    >
    >
    >BEGIN
    > owa_util.MIME_HEADER('application/excel');
    > htp.htmlopen;
    > htp.bodyopen(cattributes=>'class="PortletBodyColor" ');
    > htp.tableopen('border=1 align=left',
    > cattributes=> 'cellspacing=0 cellpadding=0 class="BannerColor"

    ');
    > htp.TABLECAPTION('down and dirty gross margin report<br>'||p_jobno);
    > htp.TABLEHEADER('Project ',cattributes=>'CLASS="PortletText1"');
    > htp.TABLEHEADER('Area ',cattributes=>'CLASS="PortletText1"');
    > htp.TABLEHEADER('Expendituretype ',cattributes=>'CLASS="PortletText1"');
    > htp.TABLEHEADER('Revenue ',cattributes=>'CLASS="PortletText1"');
    > htp.TABLEHEADER('Cost ',cattributes=>'CLASS="PortletText1"');
    > htp.TABLEHEADER('Margin ',cattributes=>'align "right"
    >CLASS="PortletText1"');
    > FOR rec IN gm LOOP
    > htp.tablerowopen;
    > htp.tabledata(rec.segment1, cattributes=>'align left nowrap CLASS="PortletText1"');
    > htp.tabledata(rec.area, cattributes=>'align left nowrap CLASS="PortletText1"');
    >
    > htp.tabledata(rec.exptype, cattributes=>'align left nowrap CLASS="PortletText1"');
    > htp.tabledata(rec.revenue, cattributes=>'align left nowrap CLASS="PortletText1"');
    > htp.tabledata(rec.cost, cattributes=>'align left nowrap CLASS="PortletText1"');
    > htp.tabledata(rec.margin, cattributes=>'align=right nowrap CLASS="PortletText1"');
    > htp.tablerowclose;
    >
    > END LOOP;
    >
    >htp.bodyclose;
    >htp.HTMLCLOSE;
    >EXCEPTION
    > WHEN OTHERS THEN
    > v_message := SUBSTR(SQLERRM, 1, 150);
    > Foo_Lib.error_page(v_message);
    >
    >END foo;
    >
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center