Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: How to upload the data from this text file into my db tables.

    Hi,

    I have a text file which has the following data.

    1§00§EMPLOYEE§
    1§50§2009-09-08 10:53:14.0§23232426§§
    1§50§2009-09-08 10:53:14.0§23232427§§
    1§99§2§

    1§00§EMPLOYEEINFO§
    1§50§2009-09-08 10:53:14.0§Rama§§
    1§50§2009-09-08 10:53:14.0§George§§
    1§99§2§

    Here

    EMPLOYEE and EMPLOYEEINFO are 2 data from different tables which I want to bulk upload into my database tables.

    EMPLOYEE has 2 rows of data. The final line states the count of data. Then starts the data for the EMPLOYEEINFO. The final line states the count of data for EMPLOYEEINFO table.

    Now, I want a way to bulk upload the data from this text file into two tables in my data base EMPLOYEE and EMPLOYEEINFO.

    Can I use SQL Loader / External tables for this type of data file? If so, how can I read the data? Please help.

    Any other concepts other than SQL Loader / External tables are also welcome. My aim is to upload the data into tables.

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    If it's a one-time thing, MSAccess works really well. Just import the data into an Access table, then create a link to your table in Oracle (using ODBC) and then run an Append query, pushing the local Access table data into your Oracle table.

    If it's going to be a regular thing, my preference is to create an EXTERNAL TABLE (an Oracle construct which allows you to reference a file external to the database instance, and define it as a table). Then, you just have to build a procedure which

    Code:
    insert into <real table>
    select * from <external table>
    SQL Loader works also, we've just never used it here.
    --=Chuck

  3. #3
    Join Date
    Nov 2009
    Posts
    2
    Chuck, Thanks for your reply.

    It is not an one time activity. My data file has data for more than one table data in the stated format. For this scenario I do not know how to proceed with external tables /sql loader option.

    Any idead are welcome.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Here are some sites that talk about EXTERNAL TABLEs in general:

    External Tables | Oracle FAQ
    13 External Tables Concepts

    The idea is ... once you've set up the external table, then what you do with the data is up to you. The same tools you've always had available are there, since you access the data in your flat file using SQL. Doesn't matter how many tables you're inserting into.

    Code:
    insert into tableA
    select * from my_external_table where type = 'A';
    
    insert into tableB
    select distinct code, description from my_external_table where type = 'B';
    --=Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •