Generate Insert SQL Statements from a CSV File

Generate insert SQL statements from a CSV file

It's a bit crude - but for one off jobs, I sometimes use Excel.

If you import the CSV file into Excel, you can create a formula which creates an INSERT statement by using string concatenation in the formula. So - if your CSV file has 3 columns that appear in columns A, B, and C in Excel, you could write a formula like...

="INSERT INTO MyTable (Col1, Col2, Col3) VALUES (" & A1 & ", " & B1 & ", " & C1 & ")"

Then you can replicate the formula down all of your rows, and copy, and paste the answer into a text file to run against your database.

Like I say - it's crude - but it can be quite a 'quick and dirty' way of getting a job done!

Generate Insert SQL Statement from a .csv file

You can use the java non-blocking class Files to read all the lines of your file using the readAllLines method and then use a template String and format it with the correct values using the String format method.

You then write your statements to a .sql using the write method in java.nio.Files.

    final String template = "INSERT INTO TABLE(%s,%s,%s) VALUES ('%s','%s',%s);";
    final String delimiter = ";";

    List<String> statements = new ArrayList<>();

    List<String> lines = Files.readAllLines(Paths.get("your-file.csv"));
    String[] columnNames = lines.get(0).split(delimiter);

    for (int i = 1; i < lines.size(); i++) {
        String[] values = lines.get(i).split(delimiter);
        statements.add(String.format(template, columnNames[0], columnNames[1], columnNames[2], values[0], values[1], values[2]));
    }

    Files.write(Paths.get("your-output.sql"), statements);

That is a short and simple way to achieve what you are asking. You will need to swap the word TABLE for your table name of course :)

UPDATE:

If you wanted to make the statement variable on the values in the .csv then you could use a nested for loop and amend the template

    for (int i = 1; i < lines.size(); i++) {

        String[] values = lines.get(i).split(delimiter);

        StringBuilder cols = new StringBuilder();
        StringBuilder vals = new StringBuilder();

        for (int j = 0; j < columnNames.length; j++) {
            cols.append(columnNames[j]);
            vals.append("'").append(values[j]).append("'");

            if (j != columnNames.length - 1) {
                cols.append(",");
                vals.append(",");
            }
        }

        statements.add(String.format(template, cols.toString(), vals.toString()));
    }

You would then have the template String as follows:

final String template = "INSERT INTO TABLE (%s) VALUES (%s);";

Create SQL insert statements to CSV file

Here is some pseudo code which generates an insert statement based on a JDBC result set:

String TABLE_NAME = "yourTable";
StringBuilder sb = new StringBuilder("INSERT INTO ");
sb.append(TABLE_NAME).append(" (");

rs = stmt.executeQuery(query);
rsmd = rs.getMetaData();
int columnsNumber = rsmd.getColumnCount();
for (int i=0; i < columnsNumber; ++i) {
    String name = rsmd.getColumnName(i+1);
    if (i > 0) {
        sb.append(", ");
    }
    sb.append(name);
}
sb.append(")\nVALUES\n");

// now generate rows
boolean start = true;
while (rs.next()) {
    if (start) {
        start = false;
    }
    else {
        sb.append(",\n");
    }
    sb.append("(");
    for (int i=0; i < columnsNumber; i++) {
        if (i > 0) {
            sb.append(", ");
        }
        String columnValue = rs.getString(i+1);
        sb.append(columnValue);
    }
    sb.append(")");
}

sb.append(";");

How to generate insert statements of each row from csv in python?

Take this as an example you can work from. You need to store each row where you'v reformatted the values then combine all of this.

csvFile = [
        ['columnA', 'columnB'],
        ['a1', 'b1'],
        ['a2', 'b2'],
]
header = csvFile[0]
headers = map((lambda x: '`' + x + '`'), header)
insert = 'INSERT INTO my_table (' + ", ".join(headers) + ") VALUES "
values = []
for row in csvFile[1:]:
    values.append('(' + ','.join(map(lambda x: '"%s"' % x,  row)) + ')')

data = insert + ', '.join(values)
print(data)

CSV File to SQL Insert Statement

Try this (I ignored the ID part since you can use the mySQL auto_increment)

import csv

openFile = open('test.csv', 'r')
csvFile = csv.reader(openFile)
header = next(csvFile)
headers = map((lambda x: '`'+x+'`'), header)
insert = 'INSERT INTO Table (' + ", ".join(headers) + ") VALUES "
for row in csvFile:
    values = map((lambda x: '"'+x+'"'), row)
    print (insert +"("+ ", ".join(values) +");" )
openFile.close()

Generate insert script for selected records?

If you are using the SQL Management Studio, you can right click your DB name and select
Tasks > Import/Export data and follow the wizard.

one of the steps is called "Specify Table Copy or Query" where there is an option to write a query to specify the data to transfer, so you can simply specify the following query:

select * from [Table] where Fk_CompanyId = 1

---

Related Topics