Generate MySql INSERT Statements From CSV

In this example, you will see how to generate MySQL DML statements from a CSV file using MysqlInsertWriter. This tool automatically converts records from any source file into MySQL INSERT statements, streamlining the data ingestion process by eliminating the need for manual data conversion. This approach significantly reduces the time required to build efficient data pipelines.

Input File

Account,LastName,FirstName,Balance,CreditLimit,AccountCreated,Rating
101,Reeves,Keanu,9315.45,10000.00,1/17/1998,A
312,Butler,Gerard,90.00,1000.00,8/6/2003,B
868,Hewitt,Jennifer Love,0,17000.00,5/25/1985,B
761,Pinkett-Smith,Jada,49654.87,100000.00,12/5/2006,A
317,Murray,Bill,789.65,5000.00,2/5/2007,C

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.DataWriter;
import com.northconcepts.datapipeline.csv.CSVReader;
import com.northconcepts.datapipeline.job.Job;
import com.northconcepts.datapipeline.sql.mysql.MySqlInsertWriter;

import java.io.File;

public class GenerateMySqlInsertStatementsFromCsvFile {

    public static void main(String[] args) {
        DataReader reader = new CSVReader(new File("example/data/input/credit-balance-01.csv"))
                .setFieldNamesInFirstRow(true);

        DataWriter writer = new MySqlInsertWriter("credit-balance", new File("example/data/output/credit-balance-01.sql"))
                .setPretty(true) // Pretty print the SQL statements
                .setBatchSize(3); // Insert 3 records in a insert statement

        Job.run(reader, writer);
    }
}

Code Walkthrough

1. A CSVReader is created corresponding to the input file credit-balance-01.csv and field names are set to the first row.
2. A MySqlInsertWriter is created to write the generated INSERT statements to credit-balance-01.sql. The target database table is specified in the second argument as credit-balance.
3. .setPretty(true) is used to enhance the look of the output, it creates new lines where necessary which improves the readability of the output.
4. .setBatchSize(3) sets the number of records to chunk together in batch inserts to 3.
5. Data are transferred from CSVReader to MySqlInsertWriter via Job.run() method. See how to compile and run data pipeline jobs.

Console Output

INSERT INTO `credit-balance` (`Account`, `LastName`, `FirstName`, `Balance`, `CreditLimit`, `AccountCreated`, `Rating`)
VALUES ('101', 'Reeves', 'Keanu', '9315.45', '10000.00', '1/17/1998', 'A'),
       ('312', 'Butler', 'Gerard', '90.00', '1000.00', '8/6/2003', 'B'),
       ('868', 'Hewitt', 'Jennifer Love', '0', '17000.00', '5/25/1985', 'B');
INSERT INTO `credit-balance` (`Account`, `LastName`, `FirstName`, `Balance`, `CreditLimit`, `AccountCreated`, `Rating`)
VALUES ('761', 'Pinkett-Smith', 'Jada', '49654.87', '100000.00', '12/5/2006', 'A'),
       ('317', 'Murray', 'Bill', '789.65', '5000.00', '2/5/2007', 'C');