Import CSV into PostgreSQL with Java and Scriptella

Load a local CSV file into a PostgreSQL table using Scriptella and the PostgreSQL JDBC driver. The example inserts two contacts and updates them by ID on subsequent runs.

Prerequisites

Install Scriptella 1.7.0 and Java 17 or later. Use a PostgreSQL database named demo and a login with INSERT, UPDATE, and SELECT access to the destination table. Download Scriptella and get postgresql-42.7.13.jar from the PostgreSQL driver page.

Create a working directory and place the JDBC JAR in its lib/ subdirectory. The example resolves the JAR and CSV paths relative to the ETL file.

Create the table and CSV file

Connect to demo in your PostgreSQL client and run this SQL once with a role that can create tables in the public schema.

CREATE TABLE public.csv_contacts (
    id integer PRIMARY KEY,
    name text NOT NULL,
    email text NOT NULL
);

Save the following as contacts.csv in UTF-8. The first row supplies the column names. Quotes keep the comma in the second contact’s name within one field.

id,name,email
1,Ada Lovelace,ada@example.com
2,"Rivera, Sam",sam@example.com

Save and run the ETL

Save this as import.etl.xml next to contacts.csv. Change the JDBC URL if your database uses a different host, port, or name. Scriptella binds the CSV fields as JDBC parameters and PostgreSQL casts the ID to an integer.

<!DOCTYPE etl SYSTEM "http://scriptella.org/dtd/etl.dtd">
<etl>
    <connection id="source" driver="csv" url="contacts.csv">
        encoding=UTF-8
    </connection>
    <connection id="target" driver="postgresql"
                url="jdbc:postgresql://localhost:5432/demo"
                user="DEMO_USER"
                password="DEMO_PASSWORD"
                classpath="lib/postgresql-42.7.13.jar"/>
    <query connection-id="source">
        <script connection-id="target">
            INSERT INTO public.csv_contacts (id, name, email)
            VALUES (CAST(?id AS integer), ?name, ?email)
            ON CONFLICT (id) DO UPDATE
            SET name = EXCLUDED.name, email = EXCLUDED.email;
        </script>
    </query>
</etl>

Replace DEMO_USER and DEMO_PASSWORD with your local demo database credentials. Keep real passwords out of version control. For shared or deployed ETL files, set SCRIPTELLA_DB_USER and SCRIPTELLA_DB_PASSWORD in the process environment and use user="${env.SCRIPTELLA_DB_USER}" and password="${env.SCRIPTELLA_DB_PASSWORD}" in the connection.

From the working directory, run the launcher. On Windows, use scriptella.bat.

scriptella.sh import.etl.xml

Verify the import

SELECT id, name, email FROM public.csv_contacts ORDER BY id;
 id |     name     |      email
----+--------------+-----------------
  1 | Ada Lovelace | ada@example.com
  2 | Rivera, Sam  | sam@example.com

Run the same command again and expect the same two rows in a fresh table. An existing ID gets the name and email from the CSV. Rows whose IDs are absent from the CSV remain in the table.

Transactions and troubleshooting

This example uses one PostgreSQL transaction for the import. If a write fails, such as when a CSV ID cannot be cast to an integer, Scriptella rolls back the PostgreSQL changes from that run. Correct the input and rerun the command. The separate table creation step remains in effect.

For missing driver errors, check the JAR filename and its location under lib/. For connection or permission errors, verify the JDBC URL, the credentials, and the login’s table privileges. Check that the CSV header has the exact names used in the ETL.

Related documentation