Skip to Main Content
  • Questions

  • How to import data from more than one (.csv ) [Excel] files into the related tables !

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, OP.

Asked: December 13, 2000 - 10:41 am UTC

Last updated: November 01, 2024 - 5:37 am UTC

Version: 8.1.5

Viewed 1000+ times

You Asked

Dear Tom,

Hi !

Thanks for the previous answers.They were as perfect as
always.


Qust I have got around 50 .csv files (Excel files) containing
some data which needs to be imported into the related tables
in the database.

I have a script which I am running from the command propmt
(Windows NT) ,which makes use of sqlldr command.I am creating
a .ctl file.For every table I am creating a single .ctl file.
The sample of script is as follows

[---c1.ctl---]

LOAD DATA
INFILE MT_srvc_ctgry_rel.csv
INTO TABLE MT_srvc_ctgry_rel
append
fields terminated by ","
( SRVC_CTGRY_CD,
SRVC_CD,
DEL_F,
DEL_DT DATE 'YYYY-MM-DD',
UPDT_USER_ID,
CRT_DT DATE 'YYYY-MM-DD',
UPDT_DT DATE 'YYYY-MM-DD')


I am making use of following command to execute the script

c:\> SQLLDR user1/user1@dbmatch c1.ctl c1.log

In this script MT_srvc_ctgry_rel is the Table name which is
empty and getting data from MT_srvc_ctgry_rel.csv file.
Now I am creating such script for every Table because
the csv file and ofcourse COLUMNS in every table are different.


Consider I have T1,T2,T3 and T4 tables and C1.csv,c2.csv.c3.csv.c4.csv files containing data.
Can you suggest me a method through which the data in all
these 4 tables can be inserted at one go through there
respective .csv files.

Is there any Keyword which can be used to mark all the
COLUMNS in the table instead of writing every column in
the script in the parenthesis and also is it possible to
mention more than one file (.csv) name in the script.


Kindly answer !


Thanks & Regards
OP Lalwani


and Tom said...

There is no keyword to tell it "all columns" as the order of the columns is extremely important (it would be bad to have column A get loaded with B's data and vice-versa).

We can use SQLPlus to generate a CTL file. I do it for UNLOAD for example (to unload all data). See
</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>
it has scripts for NT you can modify. In this fashion, you can write a script that will generate a CTL file for a given table. Since the datafile name can be specified on the SQLLDR command line -- it should not put this in the control file so the control file is more generic. In this fashion, if you have T1, T2 to load and you want to load d1.dat, d2.dat into T1 and d3.dat and d4.dat into T2, you would issue commands:

genctl t1 user/password > t1.ctl
sqlldr user/password t1.ctl t1.log data=d1.dat
sqlldr user/password t1.ctl t1.log data=d2.dat

genctl t2 user/pasword > t2.ctl
sqlldr user/password t2.ctl t2.log data=d3.dat
sqlldr user/password t2.ctl t2.log data=d4.dat

that will be easier for you to script in any case and shows how to use the same ctl file for many input files.


Rating

  (4 ratings)

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

Comments

moiz, September 18, 2002 - 4:27 am UTC

I am using Oracle SQLLoader to upload my data from CSV files into a single table. My current CTL file is as follows:

LOAD DATA
INFILE 'trace_metals100.csv'
INFILE 'trace_metals101.csv'
APPEND
INTO TABLE TRACE_METALS
FIELDS TERMINATED BY ','
(STATION_NO, METAL_NAME, METAL_WT)

Just for my record I want to upload the data file name also along with the data into a fourth column. How should I do it using SQLLoader??
Regards,
Moiz

Tom Kyte
September 18, 2002 - 7:30 am UTC

you cannot. the current filename just isn't "available"

Moiz, September 18, 2002 - 9:29 am UTC

Thanks Tom for ur answer.

Actually, I have a situation where in I have to load over 100 identical data files thro' ONE control file into ONE table. But I want to keep an identifier column which will tell me that a particular record was loaded from that particular data file. It is not necessary to store the data file name. Any identifier (number or character) will be OK.

Any suggestions??

Tom Kyte
September 18, 2002 - 2:59 pm UTC

You will have to write the control file on the "fly" (eg; via a script) and use the CONSTANT or EXPRESSION features of SQLLDR to do this.

using constant, your script would write a control file that loads the constant filename into a field. Using expression, you would use a function like userenv('sessionid') to load a unique number into the field.

if you did not want to have to write the control file on the fly, you could always:

create table t ( UNIQUE_batch_ID number default userenv( 'sessionid' ),
..... other fields .....


and just load the other fields, unique batch id will have the session id of the session that loaded the file. then load each file with a separate sqlldr session

Excle to Oracle

Dawar, April 30, 2008 - 4:35 pm UTC

I have a data in MS excel format.
I would like to load data into Oracle.
Tom Kyte
April 30, 2008 - 6:06 pm UTC

did you read any of these?

http://asktom.oracle.com/pls/ask/search?p_string=loading+excel+data

Using SQLLOADER for uploading multiple csv files with different columns in single table

Sukhbinder Kaur Khalsa, October 11, 2024 - 11:18 am UTC

Hi !

Can we upload 3 csv files all of them containing different no of columns in single table using one single ctl file ?

Kindly Answer!

Thanks
Connor McDonald
November 01, 2024 - 5:37 am UTC

eg

LOAD DATA
INFILE file1.dat
INFILE file2.dat
INFILE file3.dat
INTO TABLE emp2
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(EMPNO,ENAME,JOB,MGR,HIREDATE date 'dd-mon-yy',SAL,COMM,DEPTNO)

and the three files are

7369,"SMITH","CLERK",7902,"17-DEC-80",800,,20
7499,"ALLEN","SALESMAN",7698,"20-FEB-81",1600,300,30
7521,"WARD","SALESMAN",7698,"22-FEB-81",1250,500,30
7566,"JONES","MANAGER",7839,"02-APR-81",2975,,20
7654,"MARTIN","SALESMAN",7698,"28-SEP-81",1250,1400,30
7698,"BLAKE","MANAGER",7839,"01-MAY-81",2850,,30
7782,"CLARK","MANAGER",7839,"09-JUN-81",2450,,10
7788,"SCOTT","ANALYST",7566,"09-DEC-82",3000,,20
7839,"KING","PRESIDENT",,"17-NOV-81",5000,,10
7844,"TURNER","SALESMAN",7698,"08-SEP-81",1500,,30
7876,"ADAMS","CLERK",7788,"12-JAN-83",1100,,20
7900,"JAMES","CLERK",7698,"03-DEC-81",950,,30
7902,"FORD","ANALYST",7566,"03-DEC-81",3000,,20
7934,"MILLER","CLERK",7782,"23-JAN-82",1300,,10

7369,"SMITH"
7499,"ALLEN"
7521,"WARD"
7566,"JONES"
7654,"MARTIN"
7698,"BLAKE"
7782,"CLARK"
7788,"SCOTT"
7839,"KING"
7844,"TURNER"
7876,"ADAMS"
7900,"JAMES"
7902,"FORD"
7934,"MILLER"

7369,"SMITH","CLERK"
7499,"ALLEN","SALESMAN"
7521,"WARD","SALESMAN"
7566,"JONES","MANAGER"
7654,"MARTIN","SALESMAN"
7698,"BLAKE","MANAGER"
7782,"CLARK","MANAGER"
7788,"SCOTT","ANALYST"
7839,"KING","PRESIDENT"
7844,"TURNER","SALESMAN"
7876,"ADAMS","CLERK"
7900,"JAMES","CLERK"
7902,"FORD","ANALYST"
7934,"MILLER","CLERK"


which gave this

SQL*Loader: Release 23.0.0.0.0 - Production on Fri Nov 1 13:35:48 2024
Version 23.4.0.24.05

Copyright (c) 1982, 2024, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 13
Commit point reached - logical record count 14
Commit point reached - logical record count 27
Commit point reached - logical record count 28
Commit point reached - logical record count 41
Commit point reached - logical record count 42

Table EMP2:
  42 Rows successfully loaded.

Check the log file:
  file.log
for more information about the load.

SQL> select * from emp2;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER
      7369 SMITH      CLERK
      7499 ALLEN      SALESMAN
      7521 WARD       SALESMAN
      7566 JONES      MANAGER
      7654 MARTIN     SALESMAN
      7698 BLAKE      MANAGER
      7782 CLARK      MANAGER
      7788 SCOTT      ANALYST
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN
      7876 ADAMS      CLERK
      7900 JAMES      CLERK
      7902 FORD       ANALYST
      7934 MILLER     CLERK

42 rows selected.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.