This discussion is archived
4 Replies Latest reply: Dec 11, 2012 2:24 AM by odie_63 RSS

How to load multiple CSV files into oracle in one go.

977256 Newbie
Currently Being Moderated
Hi,

I have project requirement like: I'll be getting one csv file as one record for the source table.

So i need to know is there any way by which I can load multiple csv in one go. I have searched a lot on net about external table.(but problem is I can only use one consolidate csv at a time)

and UTL_FILE same thing consolidate file is required here to load.

but in my scenario I'll have (1000+) csv files(as records) for the table and it'd be hectic thing to open each csv file,copy the record in it and paste in other file to make consolidate one.
Please help me ..it's very new thing for me...I have used external table for , one csv file in past but here I have to user many file.

Table description given below.
desc td_region_position

POSITION_KEY             NUMBER     Primary key
POSITION_NAME       VARCHAR2(20)     
CHANNEL                     VARCHAR2(20)     
LEVEL                     VARCHAR2(20)     
IS_PARTNER             CHAR(1)     
MARKET_CODE             VARCHAR2(20)

CSV file example:
POSITION_KEY|POSITION_NAME|CHANNEL|LEVEL|IS_PARTNER|MARKET_CODE
123002$$FLSM$$Sales$$Middle$$Y$$MDM2203


delimeter is --  $$
my database version as follows:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
"CORE     10.2.0.5.0     Production"
TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production
Edited by: 974253 on Dec 10, 2012 9:58 AM
  • 1. Re: How to load multiple CSV files into oracle in one go.
    AlexAnd Guru
    Currently Being Moderated
    if your csv files have some mask, say "mask*.csv" or by file "mask1.csv" and "mask2.csv" and ...

    you can create this.bat file
    FOR %%c in (C:\tmp\loader\mask*.csv) DO (
       c:\oracle\db\dbhome_1\BIN\sqlldr <user>@<sid>l/<password> control=C:\tmp\loader\loader.ctl data=%%c
       )
    and C:\tmp\loader\loader.ctl is
    OPTIONS (ERRORS=0,SKIP=1)
    LOAD DATA
      APPEND  
      INTO TABLE scott.td_region_position
      FIELDS TERMINATED BY '$$' TRAILING NULLCOLS
      ( POSITION_KEY,
         POSITION_NAME ,
         CHANNEL,
         LVL,
         IS_PARTNER,
         MARKET_CODE
      )
    test
    C:\Documents and Settings\and>sqlplus
    
    SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 10 11:03:47 2012
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    Enter user-name: scott
    Enter password:
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select * from td_region_position;
    
    no rows selected
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr
    oduction
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    C:\Documents and Settings\and>cd C:\tmp\loader
    
    C:\tmp\loader>dir
     Volume in drive C has no label.
     Volume Serial Number is F87F-9154
    
     Directory of C:\tmp\loader
    
    12/10/2012  10:51 AM    <DIR>          .
    12/10/2012  10:51 AM    <DIR>          ..
    12/10/2012  10:55 AM               226 loader.ctl
    12/10/2012  10:38 AM               104 mask1.csv
    12/10/2012  10:39 AM               108 mask2.csv
    12/10/2012  10:57 AM               151 this.bat
                   4 File(s)            589 bytes
                   2 Dir(s)   4,523,450,368 bytes free
    
    C:\tmp\loader>this.bat
    
    C:\tmp\loader>FOR %c in (C:\tmp\loader\mask*.csv) DO (c:\oracle\db\dbhome_1\BIN\
    sqlldr user@orcl/password control=C:\tmp\loader\loader.ctl data=%c )
    
    C:\tmp\loader>(c:\oracle\db\dbhome_1\BIN\sqlldr user@orcl/password control=C
    :\tmp\loader\loader.ctl data=C:\tmp\loader\mask1.csv )
    
    SQL*Loader: Release 11.2.0.1.0 - Production on Mon Dec 10 11:04:27 2012
    
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
    Commit point reached - logical record count 1
    
    C:\tmp\loader>(c:\oracle\db\dbhome_1\BIN\sqlldr user@orcl/password control=C
    :\tmp\loader\loader.ctl data=C:\tmp\loader\mask2.csv )
    
    SQL*Loader: Release 11.2.0.1.0 - Production on Mon Dec 10 11:04:28 2012
    
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
    Commit point reached - logical record count 1
    
    C:\tmp\loader>sqlplus
    
    SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 10 11:04:46 2012
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    Enter user-name: scott
    Enter password:
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select * from td_region_position;
    
    POSITION_KEY POSITION_NAME        CHANNEL              LVL                  I
    ------------ -------------------- -------------------- -------------------- -
    MARKET_CODE
    --------------------
          123002 FLSM                 Sales                Middle               Y
    MDM2203
    
          123003 FLSM1                Sa2les               M2iddle              Y
    MDM22203
    
    
    SQL>
  • 2. Re: How to load multiple CSV files into oracle in one go.
    odie_63 Guru
    Currently Being Moderated
    Hi,

    You're aware you're still in the XML forum, right? ;)
    but in my scenario I'll have (1000+) csv files(as records) for the table and it'd be hectic thing to open each csv file,copy the record in it and paste in other file to make consolidate one.
    Why would you want to do it manually?
    Use a simple shell script, for example :
    cd csv_dir
    cat *.csv > all.csv
    then read "all.csv" file with an external table.
  • 3. Re: How to load multiple CSV files into oracle in one go.
    AlexAnd Guru
    Currently Being Moderated
    >
    Use a simple shell script
    >
    for linux
    and bat for win
    :)


    bottlenecks of your approach:
    - csv contain one row so creating one common file will be contain row for skip
    [oracle@oebs andreev_wrk]$
    [oracle@oebs andreev_wrk]$ ls
    mask1.csv  mask2.csv
    [oracle@oebs andreev_wrk]$ cat mask1.csv
    POSITION_KEY|POSITION_NAME|CHANNEL|LEVEL|IS_PARTNER|MARKET_CODE
    123002$$FLSM$$Sales$$Middle$$Y$$MDM2203[oracle@oebs andreev_wrk]$ cat mask2.csv
    POSITION_KEY|POSITION_NAME|CHANNEL|LEVEL|IS_PARTNER|MARKET_CODE
    123003$$FLSM1$$Sa2les$$M2iddle$$Y$$MDM22203[oracle@oebs andreev_wrk]$ cat *.csv > all.csv
    [oracle@oebs andreev_wrk]$ cat all.csv
    POSITION_KEY|POSITION_NAME|CHANNEL|LEVEL|IS_PARTNER|MARKET_CODE
    123002$$FLSM$$Sales$$Middle$$Y$$MDM2203POSITION_KEY|POSITION_NAME|CHANNEL|LEVEL|IS_PARTNER|MARKET_CODE
    123003$$FLSM1$$Sa2les$$M2iddle$$Y$$MDM22203[oracle@oebs andreev_wrk]$
    - if a new file then you must recreate all.csv all time ; of course it nice for unchangeable set of files


    i think the architecture is very important for choice of technology
  • 4. Re: How to load multiple CSV files into oracle in one go.
    odie_63 Guru
    Currently Being Moderated
    AlexAnd wrote:
    and bat for win
    OP is on AIX... so definitely *NIX shell script.

    bottlenecks of your approach:
    - csv contain one row so creating one common file will be contain row for skip
    Of course, it was just an example.
    It's not a real problem, the script can handle that quite nicely too :
    find . -name "*.csv" | xargs -n 1 tail -n +2
    - if a new file then you must recreate all.csv all time ; of course it nice for unchangeable set of files
    The external table PREPROCESSOR feature (available in 10.2.0.5) is perfect to handle this situation.
    Files will be merged and piped to the external table at runtime.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points