4 Replies Latest reply: Dec 11, 2012 4:24 AM by odie_63 RSS

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

    977256
      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
          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
            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
              >
              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
                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.