This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Jan 20, 2009 2:20 AM by 642956 RSS

Transfer Data from Excel to Oracle

569999 Newbie
Currently Being Moderated
I want to transfer data from Excel to Oracle 10g is there any third party tool available to perform this task. or if oracle also support it please advise.

With prior thanks

Afzaal
  • 1. Re: Transfer Data from Excel to Oracle
    559180 Newbie
    Currently Being Moderated
    Hi
    1/ Describe excel file (Physical & logical) in Topology...see ODI help for exemple
    2/ Create a model in designer
    3/ Create an interface with excel datastore in source, oracle datastore in target and use a LKM like LKM SQL to Oracle to load data...and the IKM you need

    MA
  • 2. Re: Transfer Data from Excel to Oracle
    569735 Newbie
    Currently Being Moderated
    Hi,

    You can use ODI or SqlLoader for transfering data to excel on Oracle Database.
    Which is format an your excel file ?

    Gilles.
  • 3. Re: Transfer Data from Excel to Oracle
    569735 Newbie
    Currently Being Moderated
    Hi,

    You can also use externally table if you don't transforming your data.

    Gilles.
  • 4. Re: Transfer Data from Excel to Oracle
    559180 Newbie
    Currently Being Moderated
    your xls file is it heavy?
    1/ sqlloader is in fact an opportunity for heavy file. If it's your case, use a LKM SQL to ORACLE (SnpsSqlUnload, loader) witch unload data in flat file and load it. or use csv format and load directly your data with LKM File to Oracle (SqlLoader)

    2/but if your file is light, you must use LKM presented before...

    MA
  • 5. Re: Transfer Data from Excel to Oracle
    558856 Newbie
    Currently Being Moderated
    To simply transfer from Excel to Oracle, If you are looking at a one time load scenario or a very infrequent load, you can try Application Express (A GUI tool that can not only load but also create applications).

    else you can also try sqlloader...

    If you are looking at an periodic load then ODI/OWB would be the choices you have...
  • 6. Re: Transfer Data from Excel to Oracle
    3923 Oracle ACE
    Currently Being Moderated
    Apex could be used if your excel-files aren't using any macro's, pivot tables, ... otherwise it would be time consuming to get the excel files sorted properly so can import them using Apex.

    I would say the options for importing complex excell files one-time-only (migration from excel to oracle), would be:
    - sqlloader
    - external tables
    - ODI
    - open source ETL tool

    Correct me if I'm wrong but this would be the options I have wright?

    I'm wondering if you have to do this step just ones, what would be the preferred solution? I have complex excel-files and a oracle db to transform to migrate to oracle 10g, new db model, new schema and new front-end.
    The ODI seems to be a powerful tool to transform from source to target in a visual way, without having to write pl/sql procedures ;o), but is it more time-consuming to set everything up than to actually perform the migration?
  • 7. Re: Transfer Data from Excel to Oracle
    Bouch Explorer
    Currently Being Moderated
    Hi Romanna,

    If your need is to upgrad 9i db to a 10g. you should use Oracle migration tool. But the migration tool will not make any transformation on your data. If you have many transformations, ODI will help you in your development even if it is for one shot.
  • 8. Re: Transfer Data from Excel to Oracle
    3923 Oracle ACE
    Currently Being Moderated
    Hi Bouch,

    Which guide did you follow to get started With Oracle Data Integrator? I've followed the Oracle Data Integrator Users' Guide & Knowledge Module Developer's Guide - Fundamentals but I didn't find any starting points on defining my own Migration path in the ODI?
  • 9. Re: Transfer Data from Excel to Oracle
    3923 Oracle ACE
    Currently Being Moderated
    Bouch,

    I'm keeping a blog about my experiences with ODI to let the community know how everything works, how I got started for my project, etc.
    I'll keep you posted via this blog on my thoughts, experience, way of work, etc.

    Feel free to comment and advice on my blog's so I know I'm keeping the right track !

    http://iadvise.blogspot.com/
  • 10. Re: Transfer Data from Excel to Oracle
    3923 Oracle ACE
    Currently Being Moderated
    Hi Bouch,

    I have a question conerning the definition of an Oracle Physical Schema in my Toplogy and there's no example of an Oracle DataStore in the 'getting started'-project (which is weard also ;o), no Oracle DB used).

    When I define a Physcial Schema for my Oracle Db Schema I have 2 dropdowns in the definition-tab to define the Schema and Work Schema.

    Why do I need to define these 2 schema's and what's the difference between the work schema and the schema?

    Do I need to define Work-repository for the work schema or do I need to define a different schema so the different system-views used by ODI won't be created in my own user-defined schema?
  • 11. Re: Transfer Data from Excel to Oracle
    559180 Newbie
    Currently Being Moderated
    Hi romana

    Don't forget ODI is more an ELT than an ETL...and then need a workspace in genarally on target.
    For Oracle, If you have a schema called TARGET, create another one for ODI staging area (workspace), with his own tablespace if you want...
    In topology (physical schema), declare TARGET as your schema, ODI_SA (for example) as the workspace associated to TARGET.

    Then the logical schema can be TARGET or ODI_SA....all standard KM are developed to create temporary tables in the workspace then in ODI_SA...

    Regards,

    MA
  • 12. Re: Transfer Data from Excel to Oracle
    3923 Oracle ACE
    Currently Being Moderated
    Hi Cenisis,

    I'm sorry I didn't post the question to you to, because I've already noticed you are answering a lot of posts on this forum.

    Just to make sure I understand you correctly, the work schema will hold the different work tables used by ODI and that's it.

    This work-schema doesn't need to have my user-defined tables, triggers, db objects etc. this is just an empty schema that will hold the work objects, correct?

    I've already created a master and a work repository (= snpw, as suggested in the install guide), can I use the work repository as the work schema, is that the way to go or is this work schema another schema?
  • 13. Re: Transfer Data from Excel to Oracle
    559180 Newbie
    Currently Being Moderated
    :o)
    I want to improve my english...

    You're correct...work-schema is an empty schema dedicated to ODI work objects (C$/I$/E$/J$/JV$...tables,views, triggers...coming from ODI and used for load, forCDC,...)

    Your personnal DB objects could be in your schema
    and called by the same ODI function ...getObjectName( "","","" )...
    The last parameter is the physical schemain the logical schema:
    D for Data...your schema
    W for Work....ODI work schema

    MA
  • 14. Re: Transfer Data from Excel to Oracle
    3923 Oracle ACE
    Currently Being Moderated
    Thanx a lot for your replies Cenisis, and your english is just fine ! ;-)

    If I have to look up this kind of information I'm wasting valuable time because there are experts out there who can answer my questions straight away, which is the case on this forum.

    I'm glad someone is actually answering my posts on this forum because on the other forums there isn't much movement going on, I think I'm asking to many questions ;o))

    Feel free to give your comments, suggestions, advise, ... on my blogspot where I'm keeping track of my ODI experience, http://iadvise.blogspot.com/.

    Thanx a million, I know who to talk to now!
1 2 Previous Next