Skip to Main Content
  • Questions
  • How to create tables automatically from multiple csv files in oracle database?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sid.

Asked: May 08, 2017 - 11:22 am UTC

Last updated: May 15, 2017 - 4:18 pm UTC

Version: 12.1.2.0

Viewed 10K+ times! This question is

You Asked

How to create tables automatically from multiple csv files in oracle database?
For Example:
I have a client requirement that load data from several csv files into an oracle database.I have 50 files with different structures.so ,i want create table structure based on file columns and import data form several files with single instance.

Please let me know if anyone faced this requirement.

Thanks advance..


and Connor said...

Both Application Express and SQL Developer have data import facilities to scrutinize a file and suggest likely datatypes for the result tables.

Here's a blog post by Jeff Smith on the SQL Developer. It's for Excel but the process and screens are similar

http://www.thatjeffsmith.com/archive/2014/12/sql-developer-4-1-easier-excel-imports/

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sid Goel, May 09, 2017 - 6:26 am UTC

Thanks for sharing your view..

Above mentioned link doesn't meet my requirement.We have thousands of csv files based on files we need to create table for each file.

Actually we want to do this as single instance.Please let me know if have script for Create tables automatically according to input csv files in oracle sql.

Thanks advance..

Connor McDonald
May 15, 2017 - 4:18 pm UTC

Well....that esclated quickly. Yesterday it was:

"I have 50 files"

and now it is

"We have thousands of csv files"

Sam Jacob, May 09, 2017 - 7:14 pm UTC

You can use SQL loader

KPI, May 10, 2017 - 6:00 am UTC

Thanks for your response.

By using SQL*Loder,automatically Table creation is not possible.we need to specify the column names manually in .ctl file and need to create a table in DB level.

For Example:
1. we have few files each file consists of different structure like:
File1-5 columns; File2 - 12 columns ..etc
2.Need to create tables automatically based on file structure and table name should be File name and data populations.

To KPI

J. Laurindo Chiappa, May 15, 2017 - 4:47 pm UTC

Hi :

in normal/routine cases you would use a FILE WATCHER, looking for new files arriving and executing a routine that do a LOAD FROM FILE, execute an INSERT INTO realtable FROM externaltablereadingthetextfile - see https://aychin.wordpress.com/2014/08/21/oracle-file_watcher-and-its-troubleshooting/ and http://ittichaicham.com/2009/10/11gr2-new-feature-file-watchers/ for examples....

In your case, your data model (in a non-routinely/not so common way) is dynamic, so each file can have a different structure - so, imho you will need to write a custom procedure that reads each new file and dynamically creates the table and the columns via DBMS_SQL ou EXECUTE IMMEDIATE : totally possible, but highly a manual task....

Regards,

J. Laurindo Chiappa

External Table of CSV -> table

Duke Ganote, May 16, 2017 - 6:31 pm UTC

As the foregoing comment (or elsewhere on AskTom) states, we can get the entire directory and access files via an external table.

Suppose the external table has CSV contents like so (I’ve mocked it up using ordinary SQL using the SALGRADE table):

  select rownum-1 as r#, 'GRADE'||','||'LOSAL'||','||'HISAL' csv
    from dual
   union all
  select rownum, Grade||','||losal||','||hisal
   from salgrade
SQL> /

R# CSV
--- ---------------------------------------------------------------
  0 GRADE,LOSAL,HISAL
  1 1,700,1200
  2 2,1201,1400
  3 3,1401,2000
  4 4,2001,3000
  5 5,3001,9999

6 rows selected.


Let’s assume the file name is X1. If you can assume string datatypes, and a standard length (say VARCHAR2(20) in my example below), then a basic PL/SQL block can create the table from the external table:

DECLARE ctas varchar2(2000);
        inserter CLOB := '';
BEGIN
  BEGIN
  EXECUTE IMMEDIATE 'drop table x1';
  EXCEPTION WHEN OTHERS THEN NULL; END;
  FOR rec IN (
select r#
     , case when r# = 0
            then 'create table x1 ('
                  ||replace(csv,',',' VARCHAR2(20),')||' VARCHAR2(20))'
            when r# = 1
            then 'insert into x1 select '||csv||' from dual '
            else 'select '||csv||' from dual '
        end
     ||case when NOT ( r# = 0 or rr# = 1 ) then ' UNION ALL' end as DDL
from (
select row_number()over(order by r# DESC) rr#
     , t.*
from (
select rownum-1 as r#, 'GRADE'||','||'LOSAL'||','||'HISAL' csv
  from dual
union all
select rownum, Grade||','||losal||','||hisal
  from salgrade
) t order by r#
)
)LOOP
IF rec.r# = 0
     THEN execute immediate rec.ddl;
     ELSE inserter := inserter||' '||rec.ddl;
END IF;
END LOOP;
execute immediate inserter;
END;
/

PL/SQL procedure successfully completed.

SQL> select * from x1;

GRADE                LOSAL                HISAL
-------------------- -------------------- -------
1                    700                  1200
2                    1201                 1400
3                    1401                 2000
4                    2001                 3000
5                    3001                 9999