How to load multiple csv into multiple tables using sql loader

Question

I use SQL loader to load multiple CSV files into multiple tables. Example: I have the a.csv, b.csv, c.csv files loaded in the Employee table, and the d.csv, e.csv, and f.csv files load into the Student table. I have written a Java script to implement SQL loader

@RestController
public class FileUploadController {
    // Using ServletContext
    @Autowired
    ServletContext context;

String folderUpload = context.getRealPath("/WEB-INF/uploaded");
            Process p = Runtime.getRuntime().exec(new String[] { "cmd", "/C", "all.bat" }, null,
                    new File(folderUpload));

            String sqlldrCmd = "Sqlldr baotrung/baotrung1192 control = " + folderUpload + "/full.ctl"
                    + "log=d:/bt.log skip=1";
            System.out.println(sqlldrCmd.replace("\\", "/"));
            System.out.println("SQLLDR Started ....... ");
            Runtime rt = Runtime.getRuntime();
            Process proc = rt.exec(sqlldrCmd.replace("\\", "/"));
            System.out.println(proc.waitFor());

            System.out.println("SQLLDR Ended ........  ");

I know in SQL loadder there is an option to load:

load data
INFILE 'loader2.csv'
INTO TABLE articles_formatted
APPEND
FIELDS TERMINATED BY ','
(article_id SEQUENCE (MAX, 1),
 author CHAR (30)
 format,
 pub_date SYSDATE,
 title,
 ext_fname FILLER CHAR (80)
 text LOBFILE (ext_fname) TERMINATED BY EOF)

However, if I use the load data command INFILE only loads a file into a table. If I want to continue loading I have to continue writing that command. This is impossible to do because my system has too many CSV files. I have an idea of joining the CSV file into a single CSV file but my CSV files are for multiple tables rather than one table so the work This connection is not implemented. I have two questions:

How to determine which CSV file is loaded into the table when the number of CSV is so large. I made the CSV name match the table name but it did not fit my system.
How to load multiple CSV into multiple tables. Is there a way to insert all the files in my directory into the LOAD DATA INFILE statement and load it one by one?

I have written a Java to do this problem but it can not be read sequentially

package com.baotrung;

import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

public class ListFile {
    public static void main(String[] args) {
        File dir = new File("E:\\xls");
        File[] files = dir.listFiles((d, name) -> name.endsWith(".csv"));
        for (File file : files) {
            file.getName();
        }
        Path content = Paths.get("E://xls//final.ctl");
        Stream<String> stream;

        try {
            stream = Files.lines(content);
            String line1 = Files.readAllLines(Paths.get("E://xls//final.ctl")).get(1);
            System.out.println(line1);
            List<String> replaced = stream.map(line -> line.replace(line1, files[0].getName().toString()))
                    .collect(Collectors.toList());
            Files.write(content, replaced);
            stream.close();
            System.out.println("Find and Replace done!!!");
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}

The code executes the list of CSV files in the directory, then opens the final.ctl file and executes the contents of the CSV file. But I can not duplicate it to make the week of the files in the folder. How can I read the files at [0] files in turn. Please help.

hi, You can read about Oracle External tables . its basically treat CSV file as external tables. you can create one external table per CSV file for Employee , student . External table gives of capability. You can do Insert... select from the external table, we can filter over external tables. Check some e.g.oracleappsnotes.wordpress.com/2012/02/10/… — Ramki

Littlefoot Littlefoot · Accepted Answer · 2018-02-02T13:04:27

If you merge all your CSV files, I'd suggest you to set - in its first column (or an identifier) a value that distinguishes records. For example:

first;1;little;20;111     --> should go to table called "first"
first;2;foot;30;111
second;100;donald;50;43   --> should go to table called "second"

Then you'd utilize control file's WHEN clause (search for "Loading records based on condition", here) and specify which identifier's records go to which table. The following example is adjusted from Case Study 5: Loading data into multiple tables:

   LOAD DATA
   INFILE ...
   INTO TABLE first
     WHEN identifier= 'first'
     (empno    POSITION(1:4)     INTEGER EXTERNAL,
      ename    POSITION(6:15)    CHAR,
      deptno   POSITION(17:18)   CHAR,
      mgr      POSITION(20:23)   INTEGER EXTERNAL)
   INTO TABLE second
     WHEN identifier= 'second'
     (empno    POSITION(1:4)     INTEGER EXTERNAL,
      projno   POSITION(25:27)   INTEGER EXTERNAL)
   INTO TABLE third
     WHEN identifier= 'third'
     (empno    POSITION(1:4)     INTEGER EXTERNAL,
      projno   POSITION(29:31    INTEGER EXTERNAL)

I guess that it should work just fine. Moreover, now you know the keywords to search for (WHEN, MULTIPLE TABLES) so that you could Google yourself for some more info. Good luck!

How to load multiple csv into multiple tables using sql loader

1 Answers