Generate PostgreSQL DDL From Excel

In this example, you are going to learn how to generate a PostgreSQL DDL from the data in an Excel file. First ExcelReader will be used to read the Excel document then GenerateEntityFromDataset will convert the document 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 CSV.

Java Code Listing

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

import java.io.File;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.excel.ExcelDocument;
import com.northconcepts.datapipeline.excel.ExcelReader;
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;

public class GeneratePostgreSqlDdlFromExcel {

    public static void main(String[] args) {
        ExcelDocument document = new ExcelDocument()
                .open(new File("example/data/input/call-center-inbound-call.xlsx"));

        DataReader reader = new ExcelReader(document).setFieldNamesInFirstRow(true);

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

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

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

        System.out.println(postgresDdl);
    }
    
}

Code walkthrough

1. An ExcelDocument is created corresponding to the input file call-center-inbound-call.xlsx.
2. An instance of ExcelReader is created and field names set to the first row.
3. GenerateEntityFromDataset is used to generate an entity with the name callingInfo.
4. A SchemaDef with the name callCenter is created and the entity is added to it.
5. CreatePostgreSqlDdlFromSchemaDef is used to generate PostgreSQL DDL from the schema.
6. .setPretty(true) is used to enhance the look of the output, it creates new lines where necessary which improves the readability of the output.
7. There are a couple of methods that can help you customize the output according to your liking e.g. .setDropTable(false) removes the DROP TABLE IF EXISTS "callingInfo"; statement and .setCheckIfTableNotExists(false) removes the IF NOT EXISTS statement in the creation of the table.

Console output

DROP TABLE IF EXISTS "callingInfo";

CREATE TABLE IF NOT EXISTS "callingInfo" (
  "event_type" varchar (100) NOT NULL,
  "id" int4 NOT NULL,
  "agent_id" int4 NOT NULL,
  "phone_number" varchar (100) NOT NULL,
  "start_time" timestamp NOT NULL,
  "end_time" timestamp,
  "disposition" varchar (100)
);