Generate PostgreSql DDL From CSV

In this example, you are going to learn how to generate a PostgreSQL DDL from the data in a CSV file. First CSVReader will be used to read the file then GenerateEntityFromDataset will convert the file to an EntityDef which will be added to a Schema and finally CreatePostgreSqlDdlFromSchemaDef will generate the PostgreSQL DDL from the Schema.

This example can be easily modified to show how to Generate PostgreSql DDL From Excel.

Java Code Listing

package com.northconcepts.datapipeline.foundations.examples.datamapping;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.csv.CSVReader;
import com.northconcepts.datapipeline.foundations.schema.EntityDef;
import com.northconcepts.datapipeline.foundations.schema.SchemaDef;
import com.northconcepts.datapipeline.foundations.tools.GenerateEntityFromDataset;
import com.northconcepts.datapipeline.sql.postgresql.CreatePostgreSqlDdlFromSchemaDef;

import java.io.File;

public class GeneratePostgreSqlDdlFromCsv {

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

        GenerateEntityFromDataset generator = new GenerateEntityFromDataset();
        EntityDef entity = generator.generateEntity(reader).setName("creditBalance");

        SchemaDef schema = new SchemaDef("accountInfo")
                .addEntity(entity);

        CreatePostgreSqlDdlFromSchemaDef postgresDdl = new CreatePostgreSqlDdlFromSchemaDef(schema)
                .setPretty(true)
                .setCheckIfDropTableExists(false);

        System.out.println(postgresDdl);
    }
    
}

Code Walkthrough

1. A CSVReader is created corresponding to the input file credit-balance-01.csv and field names set to the first row.
2. GenerateEntityFromDataset is used to generate an entity with the name creditBalance.
3. A SchemaDef with the name accountInfo is created and the entity is added to it.
4. CreatePostgreSqlDdlFromSchemaDef is used to generate PostgreSQL DDL from the schema.
5. .setPretty(true) is used to enhance the look of the output, it creates new lines where necessary which improves the readability of the output.
6. .setCheckIfDropTableExists(false) when false does not check if the table exists before dropping.
7. There are a couple of methods that can help you customize the output according to your liking. For eample:
   .setDropTable(false) removes the DROP TABLE "creditBalance"; statement
   .setCheckIfTableNotExists(false) removes the IF NOT EXISTS statement in the creation of the table.

Console Output

DROP TABLE "creditBalance";

CREATE TABLE IF NOT EXISTS "creditBalance" (
  "Account" int4 NOT NULL,
  "LastName" varchar (100) NOT NULL,
  "FirstName" varchar (100) NOT NULL,
  "Balance" double precision NOT NULL,
  "CreditLimit" double precision NOT NULL,
  "AccountCreated" timestamp NOT NULL,
  "Rating" varchar (100) NOT NULL
);