How to import CSV file into PostgreSQL

In this PostgreSQL tutorial, we will discuss how to import CSV file into PostgreSQL and we will cover different methods to import the CSV file into PostgreSQL. Below are the topics that we will cover in this section:

• How to import csv file into postgresql database
• How to import csv file into postgresql database using python
• How to import csv file into postgresql database using java
• How to import csv file in postgresql using command line
• How to import csv file in postgresql using pgadmin

Before we begin, let’s first understand the need for importing CSV files into PostgreSQL. Here are some of the reasons.

1. Sometimes we don’t want to write multiple insert statements to insert data into a table because of cumbersome and takes time.
2. Or maybe data is available outside of the database in a file with a large number of records that you can’t insert one by one manually into the database.
3. Maybe you are a data engineer or data analyst and you have collected your data in a file. Using this file, you can’t query about your question or whatever information you need. After importing these files into the database you will be able to query or search for the insights that you need and perform some analysis.

How to import csv file into postgre database

Let’s begin, first create a CSV file that stands for “comma-separated values” and it saves data in tabular format.

To create a CSV, open a notepad and type the given data to create a CSV file. And in the end, save the file where you want in your system’s drive.

Here are some of the main features of a CSV file.

• In the CSV file, each data is separated using commas. It can also be separated using characters like tab, semicolon, etc.
• In the above file, the data is separated using commas like id, name, class. Above is the simple structure of the CSV file but it can contain thousands of lines or long entries separated by semicolons.
• And some CSV file contains a header or some don’t.
• If you remove entries like id, name, class from the above file, then it will become a CSV file without a header, and here the header is id, name, class.
• CSV files are designed in a way that can be imported and exported into any programs or can be opened using simple text-editor, notepad, ms-excel etc.

Now you have enough information about the CSV file and how it is created. Let’s move to import the CSV file into the Postgres database.

Note: Whether we are a Windows or Linux user, the same commands are used everywhere to import CSV files into the Postgres database.

Syntax:

COPY [Table Name]
FROM '[Absolute Path to File]'
DELIMITER '[Delimiter Character]' CSV [HEADER];

Before importing data, you must have a table in the database that can hold information. While importing a CSV file, check for the header if exists and then mention the header in the query otherwise doesn’t mention it. And also keep in mind the delimiter that is used in the file.

Now you know about the header and delimiter in the file, time to import them into the database.

So our file contains three columns ‘id‘, ‘name‘, ‘class‘, and the datatype of id is an integer, the name is varchar, and class is an integer.

The next step is to create a table that can hold all of this information. For this, you can also refer to “Create a table in PostgreSQL“.

Before importing the CSV, we will look at the necessary parts of the query:

• Copy: It is command for copying data from csv file to table

• Table Name: Name of the table ‘student‘
• Absolute Path: It is location of the csv file on your system. In this example it is on the drive, so the path is: ‘D:/student_details.csv’
• Delimiter Character : which delimiter is used in csv file, here in this example is commas ‘ , ’.
• HEADER: does csv file contains a header

Now we will import the created CSV file into the student table in Postgresql using this query.

COPY student FROM 'D:/student_details.csv' DELIMITER ',' CSV HEADER;

After running the above query, verify the imported data using ‘select * from student‘. If data is imported successfully then it will show the imported data in the output otherwise it will show an empty table.

Let’s again import some of the CSV files into the database with different columns and data. The file data is given below that will be imported into the database.

The red colour box shows that the header of the file or we can call it columns of the table.

Let’s import the above file into the database named Postgres, for that first we will create the new empty table.

The above code creates the four columns and the column name is id, Canada, Australia, New Zealand.

The below code will import the file into an empty table named major_city.

COPY major_city FROM 'C:/major_cities.csv' DELIMITER ',' CSV HEADER;
SELECT * FROM major_city;

Read: PostgreSQL Export Table to CSV

How to import CSV file into Posgtresql database using python

To import CSV files from python into the Postgresql database, python has a library called psycopg2 that helps in communicating with the Postgresql database running on a server. What is “Psycopg2“?. It is an open-source library that acts as a Postgres protocol.

If the application needs to communicate with the Postgres server then we need a kind of client that can speak database protocol with the Postgres server.

Before connecting to the Postgres server, please install the necessary library and follow the below command to install Psycopg2 from your terminal.

pip install psycopg2

Now, open any IDE ( Integrated development environment ) like PyCharm, Jupyter, visual studio, etc, and follow the following steps.

• Import psycopg2 into the environment using :

import psycopg2 as psg

• Connect Postgres database using :

conn = psycopg2.connect("host=localhost dbname=postgres user=postgres")

Here is the connecting to Postgres server that returns connection object which means a persistent session is created to communicate with a database.

Next, let’s understand “What are the parameters in connect method of psycopg2?.”

• host: It is the host where the Postgres database server is running, in our case it is localhost.
• dbname: Name of the database that you want to connect
• user: Name of the user for that database

Now you have a connection object (conn) but you can’t issue a command or query with the connection object. Instead, you need another object called the cursor object that will be created by calling Cursor() method on the connection object.

cur = conn.cursor()

To execute the database query for importing CSV files into the database, you need to open your CSV file using with open( ) method of python and read each line from the file. Input to the copy_from( ) method of the cursor object that loads the file into a table.

import psycopg2
conn = psycopg2.connect("host=localhost dbname=student user=postgres password=12345")
cur = conn.cursor()
with open('D:/student_details.csv', 'r') as f:
  
    next(f) #  To Skip the header row.
    cur.copy_from(f, 'student', sep=',')

conn.commit()

• with open(‘D:/student_details.csv’, ‘r’) as f: this code helps in opening file and return file as an object for the manipulation of the opened file. And in the end, it closes the opened file.
• next(f): It is used to skip the header.
• cur.copy_from(f,’ student’,sep=’,’): Here you need to know copy_from() that requires parameters or arguments.
• First argument is the file to load, the second name of the table where the file is loaded, and the delimiter that separate data in the file.

Read: How to backup PostgreSQL database

How to import CSV file into Posgtresql database using java

In java, you can import CSV files into the Postgresql database. For this implementation, we need to create three java files in your src and jar files in the lib folder of the java project:

CSVLoader.java

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;

import org.apache.commons.lang.StringUtils;

import au.com.bytecode.opencsv.CSVReader;

public class CSVLoader {

	private static final 
		String SQL_INSERT = "INSERT INTO ${table}(${keys}) VALUES(${values})";
	private static final String TABLE_REGEX = "\\$\\{table\\}";
	private static final String KEYS_REGEX = "\\$\\{keys\\}";
	private static final String VALUES_REGEX = "\\$\\{values\\}";

	private Connection connection;
	private char seprator;

	/**
	 * Public constructor to build CSVLoader object with
	 * Connection details. The connection is closed on success
	 * or failure.
	 * @param connection
	 */
	public CSVLoader(Connection connection) {
		this.connection = connection;
		//Set default separator
		this.seprator = ',';
	}
	
	/**
	 * Parse CSV file using OpenCSV library and load in 
	 * given database table. 
	 * @param csvFile Input CSV file
	 * @param tableName Database table name to import data
	 * @param truncateBeforeLoad Truncate the table before inserting 
	 * 			new records.
	 * @throws Exception
	 */
	public void loadCSV(String csvFile, String tableName,
			boolean truncateBeforeLoad) throws Exception {

		CSVReader csvReader = null;
		if(null == this.connection) {
			throw new Exception("Not a valid connection.");
		}
		try {
			
			csvReader = new CSVReader(new FileReader(csvFile), this.seprator);

		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error occured while executing file. "
					+ e.getMessage());
		}

		String[] headerRow = csvReader.readNext();

		if (null == headerRow) {
			throw new FileNotFoundException(
					"No columns defined in given CSV file." +
					"Please check the CSV file format.");
		}

		String questionmarks = StringUtils.repeat("?,", headerRow.length);
		questionmarks = (String) questionmarks.subSequence(0, questionmarks
				.length() - 1);

		String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
		query = query
				.replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, ","));
		query = query.replaceFirst(VALUES_REGEX, questionmarks);

		System.out.println("Query: " + query);

		String[] nextLine;
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = this.connection;
			con.setAutoCommit(false);
			ps = con.prepareStatement(query);

			if(truncateBeforeLoad) {
				//delete data from table before loading csv
				con.createStatement().execute("DELETE FROM " + tableName);
			}

			final int batchSize = 1000;
			int count = 0;
			Date date = null;
			while ((nextLine = csvReader.readNext()) != null) {

				if (null != nextLine) {
					int index = 1;
					for (String string : nextLine) {
						date = DateUtil.convertToDate(string);
						if (null != date) {
							ps.setDate(index++, new java.sql.Date(date
									.getTime()));
						} else {
							ps.setString(index++, string);
						}
					}
					ps.addBatch();
				}
				if (++count % batchSize == 0) {
					ps.executeBatch();
				}
			}
			ps.executeBatch(); // insert remaining records
			con.commit();
		} catch (Exception e) {
			con.rollback();
			e.printStackTrace();
			throw new Exception(
					"Error occured while loading data from file to database."
							+ e.getMessage());
		} finally {
			if (null != ps)
				ps.close();
			if (null != con)
				con.close();

			csvReader.close();
		}
	}

	public char getSeprator() {
		return seprator;
	}

	public void setSeprator(char seprator) {
		this.seprator = seprator;
	}

}

DateUtil.java




import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
 
public class DateUtil {
 
    // List of all date formats that we want to parse.
    // Add your own format here.
    private static List<SimpleDateFormat> 
            dateFormats = new ArrayList<SimpleDateFormat>() {
		private static final long serialVersionUID = 1L; 
		{
            add(new SimpleDateFormat("M/dd/yyyy"));
            add(new SimpleDateFormat("dd.M.yyyy"));
            add(new SimpleDateFormat("M/dd/yyyy hh:mm:ss a"));
            add(new SimpleDateFormat("dd.M.yyyy hh:mm:ss a"));
            add(new SimpleDateFormat("dd.MMM.yyyy"));
            add(new SimpleDateFormat("dd-MMM-yyyy"));
        }
    };
 
    /**
     * Convert String with various formats into java.util.Date
     * 
     * @param input
     *            Date as a string
     * @return java.util.Date object if input string is parsed 
     *          successfully else returns null
     */
    public static Date convertToDate(String input) {
        Date date = null;
        if(null == input) {
            return null;
        }
        for (SimpleDateFormat format : dateFormats) {
            try {
            	format.setLenient(false);
                date = format.parse(input);
            } catch (ParseException e) {
                //Shhh.. try other formats
            }
            if (date != null) {
                break;
            }
        }
 
        return date;
    }
    
}

PostgreSQLJDBC.java

import java.sql.Connection;
import java.sql.DriverManager;





public class PostgreSQLJDBC {

   public static void main(String[] args) {
		try {

			CSVLoader loader = new CSVLoader(getConnection());
			loader.loadCSV("E:\\student_details.csv", "student", true);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
   private static Connection getConnection() {
      Connection c = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/postgres",
            "postgres", "12345");
            System.out.println("Opened database successfully");
      
      } catch (Exception e) {
         e.printStackTrace();
         System.err.println(e.getClass().getName()+": "+e.getMessage());
         System.exit(0);
      }
      
      return c;
   }
}

You don’t need to know everything, focus on these lines in PostgreSQLJDBC.java.  And change the database name and user name in this file according to your need.   

CSVLoader loader = new CSVLoader(getConnection());
loader.loadCSV("E:\\student_details.csv", "student", true);

The above two lines help in getting a connection to the database and load the CSV files into a table.

• loadCSV( ) : method accepts a three-parameter, the path of the CSV file, the table where you want to load the CSV file, and do you want to truncate the table or not.
• Run PostgreSQLJDBC.java file, your CSV file is loaded into the table successfully.

Read: PostgreSQL drop all tables

How to import csv file into postgresql using command-line

To import CSV file into Postgresql using command-line, follow these steps:

1. Open command prompt by WIN + R keys, type cmd and press Enter.
2. Enter the given command to import csv file into student table of postgres database.

psql -d dbname -U username -c "query to import csv file into table"

                          or

psql -d postgres -U postgres -c "\copy student from E:/student_details.csv delimiter ',' csv header;"

• psql -d dbname -U username -c: It enables you to issue queries from your command prompt or terminal to the Postgresql database directly.
• It shows the result of queries in the same terminal where:
• -d for the name of the database,
• -U for user of the database and
• -c for the command that you want to perform on the database.

Also read: PostgreSQL DROP TABLE

How to import csv file into postgresql using pgadmin

To import CSV file into Postgresql using pgadmin, follow these steps:

• First, open pgAdmin and select the databse that contains your table.

• Next, select the table that you want to use for loading CSV files by expanding the Schemas section of the Postgres database. And then right-click on the table to select the “Import/Export” option.

• A dialog box appears after clicking on Import/Export and fill the information according to your file location and type of file.

In the above picture, we have implemented the following tasks.

( 1 ) change from Export to Import, ( 2 ) Browse the path of CSV file, ( 3 ) Select the format of the file, ( 4 ) Toggle header if it contains a header, ( 5 ) Select delimiter from the list that separates the data of the CSV file.

• Now, click on the OK button or hit Enter from your keyboard.
• You have imported data into the table, verify it by a query select * from student.

You may also like reading the following Postgresql articles.

• PostgreSQL DROP COLUMN
• Postgresql date comparison
• Postgresql set user password
• PostgreSQL INSERT Multiple Rows
• PostgreSQL INSERT INTO table
• Postgresql change column data type
• How to find primary column name in Postgresql

So in this tutorial, we have learned about “How to import CSV file into Postgresql database” using different approaches with examples. And we have covered the following topic:

• How to import csv file into postgresql database
• How to import csv file into postgresql database using python
• How to import csv file into postgresql database using java
• How to import csv file in postgresql using command line
• How to import csv file in postgresql using pgadmin