Create SQL insert statements to CSV file

Question

I was hoping to get some advice on a tricky issue. I am reading data from a Oracle database and wanting to generate insert statement from the data I get. Each table will have a different number of values.

Here is the method currently writing the columnValue tot a csv file. Which prints to like in this format 1708||1||C||0||A||2018-06-19 00:00:00.0||2118-06-30 00:00:00.0||.

private void writeToCsvFileWithDatabase() throws SQLException, IOException {

        File file = new File("data.csv");
        selectQueries = new SelectQueries();

        ArrayList<String> queries = SelectQueries.queries();
        ResultSet rs;
        ResultSetMetaData rsmd;

        if (file.exists()){
            Statement stmt = dbConnection.createStatement();
            BufferedWriter writer = new BufferedWriter(new FileWriter("data.csv"));

            for (String query: queries) {
                rs = stmt.executeQuery(query);
                rsmd = rs.getMetaData();
                int columnsNumber = rsmd.getColumnCount();

                while (rs.next()) {
                    for (int i = 1; i <= columnsNumber; i++) {
                        if (i > 1) System.out.print(",  ");
                        String columnValue = rs.getString(i);
                        String tableName = query.substring(query.lastIndexOf(" ")+1);
                        writer.write(columnValue+"||"); //I wish I could just generate the inserts with a library here
                        System.out.print(tableName);
                    }
                    System.out.println("");

                }
            }

        } else {
            throw new FileNotFoundException("data.csv was not found in ReadDatabase.class");
        }

    }

I either need code the logic to create the insert statements:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

or find a library that will take the csv file and generate the inserts for me.

I know they're are tools that can do this but I for what I am doing I cannot use them.

Advice?

-------------------Issue 1--------------------

I am currently having an issue with previous values being appended to to insert statements. Here is an example:

INSERT INTO CAMPAIGN (CAMPAIGN_ID, SHOP_ID, CAMPAIGN_TYPE, SORT_ORDER, STATUS, VALID_FROM, VALID_TILL, CREATED_AT, MODIFIED_AT, CUSTOM_GRID_LAYOUT_CSS, IMAGE_URL, KEY, SHOW_PRODUCTS_FILTER, MOBILE_APP_IMAGE_URL, LAYOUT_ID, SHOW_OWN_BRAND, VALIDATION_STATUS, CAMPAIGN_USAGE_ID, STORE_END_DATE, PAGING_ALLOWED, CAROUSEL_BUTTON_TEXT_COLOR, CAROUSEL_BUTTON_BG_COLOR, CAROUSEL_BUTTON1_LABEL, CAROUSEL_BUTTON1_URL, CAROUSEL_BUTTON2_LABEL, CAROUSEL_BUTTON2_URL, CAROUSEL_HTML_OVERLAY, MOBILE_APP_TEASER_URL, CAROUSEL_CLAIM_URL, CAROUSEL_HERO_URL, CAROUSEL_BOX_COLOR, AVOID_CAMPAIGN_NAV_TEASER, CAROUSEL_BUTTON1_NEW_TAB, CAROUSEL_BUTTON2_NEW_TAB, SALE_QUALIFICATION_ID, SALE_TARGET_CAMPAIGN) 
VALUES (952, 1, 'C', 0, 'A', to_timestamp('2016-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2116-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2016-11-23 14:58:53.878264 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2018-07-01 08:02:57.791698 AM', 'DD-MON-RR HH.MI.SSXFF AM'), null, "campaign/0/0/0/0/9/5/2/4fd7809f-aee4-4d45-a2c7-4a258e09a709.jpg", null, 1, "campaign/0/0/0/0/9/5/2/8504e780-f69f-42c5-93c7-5e163fe4140f.jpg", 5_COL, 0, null, 4, to_timestamp('2116-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), 0, null, null, "Artikel ab Do.,10.11.", "https://www.lidl.de", "Unsere Online-Prospekte", "https://www.lidl.de", null, "campaign/0/0/0/0/9/5/2/8943afff-0823-4c35-aad2-2332172bc675.jpg", "campaign/0/0/0/0/9/5/2/96f9c073-fc1c-481d-a2d6-903f8770fecb.png", "campaign/0/0/0/0/9/5/2/81c7c88a-c4fb-4e26-8d92-94716dad642b.jpg", null, 0, 0, 0, null, 0);

this is a good Insert statement on the first loop but look at what happens on the second loop:

    INSERT INTO CAMPAIGN (CAMPAIGN_ID, SHOP_ID, CAMPAIGN_TYPE, SORT_ORDER, STATUS, VALID_FROM, VALID_TILL, CREATED_AT, MODIFIED_AT, CUSTOM_GRID_LAYOUT_CSS, IMAGE_URL, KEY, SHOW_PRODUCTS_FILTER, MOBILE_APP_IMAGE_URL, LAYOUT_ID, SHOW_OWN_BRAND, VALIDATION_STATUS, CAMPAIGN_USAGE_ID, STORE_END_DATE, PAGING_ALLOWED, CAROUSEL_BUTTON_TEXT_COLOR, CAROUSEL_BUTTON_BG_COLOR, CAROUSEL_BUTTON1_LABEL, CAROUSEL_BUTTON1_URL, CAROUSEL_BUTTON2_LABEL, CAROUSEL_BUTTON2_URL, CAROUSEL_HTML_OVERLAY, MOBILE_APP_TEASER_URL, CAROUSEL_CLAIM_URL, CAROUSEL_HERO_URL, CAROUSEL_BOX_COLOR, AVOID_CAMPAIGN_NAV_TEASER, CAROUSEL_BUTTON1_NEW_TAB, CAROUSEL_BUTTON2_NEW_TAB, SALE_QUALIFICATION_ID, SALE_TARGET_CAMPAIGN) 
VALUES (952, 1, 'C', 0, 'A', to_timestamp('2016-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2116-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2016-11-23 14:58:53.878264 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2018-07-01 08:02:57.791698 AM', 'DD-MON-RR HH.MI.SSXFF AM'), null, "campaign/0/0/0/0/9/5/2/4fd7809f-aee4-4d45-a2c7-4a258e09a709.jpg", null, 1, "campaign/0/0/0/0/9/5/2/8504e780-f69f-42c5-93c7-5e163fe4140f.jpg", 5_COL, 0, null, 4, to_timestamp('2116-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), 0, null, null, "Artikel ab Do.,10.11.", "https://www.lidl.de", "Unsere Online-Prospekte", "https://www.lidl.de", null, "campaign/0/0/0/0/9/5/2/8943afff-0823-4c35-aad2-2332172bc675.jpg", "campaign/0/0/0/0/9/5/2/96f9c073-fc1c-481d-a2d6-903f8770fecb.png", "campaign/0/0/0/0/9/5/2/81c7c88a-c4fb-4e26-8d92-94716dad642b.jpg", null, 0, 0, 0, null, 0);
 (953, 1, 'C', 0, 'A', to_timestamp('2016-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2116-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2016-11-23 15:09:40.201996 AM', 'DD-MON-RR HH.MI.SSXFF AM'), to_timestamp('2018-07-01 08:02:57.791756 AM', 'DD-MON-RR HH.MI.SSXFF AM'), null, "campaign/0/0/0/0/9/5/3/7c019b7c-5cf7-415c-bbe1-7316083a1339.jpg", null, 1, "campaign/0/0/0/0/9/5/3/c3c97d4c-4018-4e6f-91dd-00417a0e9f08.jpg", 5_COL, 0, null, null, to_timestamp('2116-11-23 00:00:00.0 AM', 'DD-MON-RR HH.MI.SSXFF AM'), 0, null, null, "Ab Do., 24.11.", "https://www.lidl.de", "Unsere Online-Prospekte", "https://www.lidl.de", null, "campaign/0/0/0/0/9/5/3/78e01f6a-435c-4aa4-af17-6231f13abb55.jpg", "campaign/0/0/0/0/9/5/3/a268d712-26fc-4eac-bf58-ebf7d38b2b12.png", "campaign/0/0/0/0/9/5/3/43bacb3e-6b9e-4ada-8b4c-3e7b5d9d9090.jpg", null, 0, 0, 0, null, 0);

The first insert statements is appended to the seconds ones. This will happen every single time till I have a huge amount of duplicates in each insert statement. I am not sure why it keep appending the all the previous statements over and over.

Current code:

if (file.exists()){
            Statement stmt = dbConnection.createStatement();
            BufferedWriter writer = new BufferedWriter(new FileWriter("data.csv"));

            for (String query: queries) {
                rs = stmt.executeQuery(query);
                rsmd = rs.getMetaData();
                int columnsNumber = rsmd.getColumnCount();
                TABLE_NAME = query.substring(query.lastIndexOf(" ") + 1);
                sb.append(TABLE_NAME).append(" (");
                for (int i = 0; i < columnsNumber; ++i) {
                    String name = rsmd.getColumnName(i + 1);
                    if (i > 0) {
                        sb.append(", ");
                    }

                    sb.append(name);
                }
                sb.append(") VALUES ");

                // generate rows
                while (rs.next()) {

                    sb.append("(");
                    //might not need for loop anymore..
                    for (int i = 0; i < columnsNumber; i++) {
                        if (i > 0) {
                            sb.append(", ");
                        }
                        String columnValue = rs.getString(i + 1);

                        if (columnValue == null) {
                            sb.append("null");
                        } else if (columnValue.contains("-") && Character.isDigit(columnValue.charAt(0))) {
                            columnValue = convertToSqlDate(columnValue);
                            sb.append(columnValue);
                        } else if (Character.isLetter(columnValue.charAt(0)) && columnValue.length() > 2) {
                            //is a varchar
                            sb.append("\"");
                            sb.append(columnValue);
                            sb.append("\"");
                        } else if (Character.isLetter(columnValue.charAt(0))) {
                            //is a Character
                            sb.append("\'");
                            sb.append(columnValue);
                            sb.append("\'");
                        } else {
                            sb.append(columnValue);
                        }
                    }
                    sb.append(");");
                    sb.append("\n");
                    writer.write(sb.toString());

                }
            }
            //writer.write(sb.toString());

        } else {
            throw new FileNotFoundException("data.csv was not found in ReadDatabase.class");
        }

Why are you printing double pipe separated data? In any case, why do you need the insert script? Most databases, including Oracle, have some sort of tool which can read CSV back into the database. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 29, 2018 at 10:46
@TimBiegeleisen I mentioned in the post using tools is not an option. I can change the ||. In the end I want insert statements in a csv or a csv file to be able to be imported to another schema. — Mike3355
– Mike3355, Commented Jun 29, 2018 at 10:50
Why don't you use String.format() and generate SQL statement by yourself? Do you indeed need a library? — Yevgen
– Yevgen, Commented Jun 29, 2018 at 10:53

Tim Biegeleisen · Accepted Answer · 2018-06-29 11:02:54Z

2

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(";");

answered Jun 29, 2018 at 11:02

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Mike3355 Over a year ago

sb.append(");"); sb.append("\n"); writer.write(sb.toString()); } That does the trick

Tim Biegeleisen Over a year ago

@Drew1208 Glad to help you. Obviously, I can't test my code with your data.

Tim Biegeleisen Over a year ago

@Drew1208 I'm not sure I follow what you are describing. The VALUES clause in an insert may have any number of tuples/rows following it. What behavior are you expecting here?

Tim Biegeleisen Over a year ago

@Drew1208 I don't think my code generates the output you are referring to in your updated question. But, besides that each line in the VALUES clause ends in a semicolon, when in fact each line should end in comma (except for the last, which really should end in semicolon), there is nothing unexpected or wrong about your current output.

Tim Biegeleisen Over a year ago

Well you're adding a semicolon inside the loop over the result set, when in fact you should only be adding one at the end (which is what my code is doing).

|

Yevgen · Accepted Answer · 2018-06-29 14:59:48Z

You can use DBUtils from Apache Commons to do some work for you. It has RowProcessor class which allows you to get ResultSet as a map, so it will be easier to process it for your purposes. Further steps would be to add custom method to that class and to define template for your statements.

Consider the example.

class CustomRowProcessor extends BasicRowProcessor {
  List<String> getInsertList(ResultSet rs) throws SQLException {
    List<String> res = new ArrayList<>();
    while (rs.next()) {
      String tableName = rs.getMetaData().getTableName(1);
      String template = "INSERT INTO %s (%s) VALUES (%s);";
      String columns = toMap(rs).keySet().stream()
          .collect(Collectors.joining(", "));
      String values = toMap(rs).values().stream()
          .map(v -> "'" + v + "'")
          .collect(Collectors.joining(", "));
      res.add(String.format(template, tableName, columns, values));
    }
    return res;
  }
}

Having the above and DBUtils on classpath, a significant part of your original code can be refactored as follows.

for (String query : queries) {
  rs = stmt.executeQuery(query);
  for (String insert : new CustomRowProcessor().getInsertList(rs)) {
    writer.write(insert + "\n");
  }
}

Collectives™ on Stack Overflow

Create SQL insert statements to CSV file

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related