Generate Oracle insert statements for excel data using java

Many times, it will be easier to generate insert statements using a program rather than creating it manually which will be time consuming especially if you have to create hundreds/thousands of them.

There are many ways to achieve the goal, the easiest being using formulas in excel and generating the insert statements.

It will become more complex to use excel to generate insert statements if the data is in columns rather than rows.

It will be a lot easier to use excel to do this job if the data is in a single row, i.e table columns can be mapped to excel columns.

The below program can be used if the data in excel as given below:



Example : Insert statements should have Row1-Cell1 i.e. 123456 common in 3 insert statements and each should have the values in the columns.
123456,ABC
123456,DEF
123456,GHI
456789,ABC
456789,DEF
456789,GHI
456789,JKL
456789,MNO
.......

Program:

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;

public class ReadExcelAndGenerateInserts {

    public static void main(String[] args) {
        try {
            Map> map = readExcelFile();
            List insertStatements = createInsertStatements(map);
            createTxtFile(insertStatements);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static void createTxtFile(List insertStatements) throws IOException {
        FileWriter fileWriter = new FileWriter("inserts.txt");
        for (int i = 0; i < insertStatements.size(); i++) {
            fileWriter.append(insertStatements.get(i));
            fileWriter.append("\n");
        }
        fileWriter.flush();
        fileWriter.close();
        System.out.println("File created!!");
    }

    private static List createInsertStatements(Map> map) {
        List inserts =  new ArrayList<>();
        Set

Share: