Importing CSV file in the postgresql table

👤 Diwas Poudel 🕒 06 Aug 2019 📁 TECH

In this tutorial, we will learn how to import a CSV formatted file using pgAdmin into our PostgreSQL database.

Lets first know what is CSV files.

CSV File: A Comma Separated Values (CSV) file is a plain text file containing a list of information. Generally, CSV files use comma(,) separated delimiter to separate the data.ie. a comma is used between the data. These files are often used to exchange information between different application.

fig: CSV file

Let's discuss two way for importing a CSV file.

Tools used :

1) PostgreSQL 10.4, compiled by Visual C++ build 1800, 64-bit

2) pgAdmin 4 with version 3.0

Ways 1: Using pgAdmin GUI tools

First, create a table in your database to import the CSV file. I have already created a table name 'student' where I have inserted a few data as shown below:

Here, I have created 'student' table inside the 'public' schema.

student table

Some data in student table:

data in student table

Here, We have a student table with 2 data.

2. Now, We are going to add more data to the table from the CSV file. So for this first of all open your desired excel file

excel file

Then save this file as CSV file as shown below.

save as csv

Then open this 'student.csv' file in notepad. It will be like:

3. Now go to your pgAdmin Tool. Then right-click on desire table where you want to import the data. In my case, it is 'student' table.

So right-click on student table then click on 'Import / Export' as shown below:

import and export

4. Then we get this below screen. Here, First, navigate to "Column" tab, initially we will find all the column selected.

all column selected

But we want an only name and course field data as our CSV file has those field only.So let's select 'name' and 'course' field from the multi-select box.

name and course

Leave Null Strings field empty if you want to accept a null value.

5. Then in same windows navigate to "Options" tabs.

a) Here we will select "Import" by pushing the button to indicate that the server should import information from a file into a table.

b) Then use the text fields in the File Info box to indicate source CSV file.

Note: File can be any of these extension .txt, .csv ,.bin etc

c) Next select "header" to "yes" if your CSV file has a header.

d) Then, select delimiter as "comma" as my data in CSV is separated by a comma. Other delimiters present in the dropdown are a colon, semicolon, a vertical bar, or a tab

Then click on the Ok button.

options tab Then we must get the message as below

success message 6)Now, let us navigate to see the data in the "student" table.

now and then

Note: Here, we have no "batch" in a CSV file so we are getting empty for the batch field.

Ways 2: Using Script in PgAdmin

1) First Open Query Tools and write the query in below format:

COPY tablename (c1 opt, c2 opt, ...) FROM 'csv file location with path' WITH DELIMITER ',' CSV HEADER;

Here, we are using "CSV Header" for just ignoring the header of the CSV file.

Note: The default delimiter is a comma, in the absence of the option WITH DELIMITER.

My case:

querying in pgadmin query tool

Note: When data is quoted, by default data quoting characters will be double-quotes.If the data is quoted using any other character use the QUOTE option; however, this option is allowed only when using CSV format.

2) Click on the Run button.

Then you may get an error like this:

ERROR:  could not open file "C:\Users\pc\Desktop\student.csv" for reading: Permission denied
HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.
SQL state: 42501

Why this error?

tell me why

Okay, don't worry I am here.

To solve this problem you must give permission to the CSV file because that CSV file present in a COPY command are read directly by the server, but not client application. So to make this file accessible to a server we must give full read-write permission so that Postgresql user can read and write on that file.

So, let give permission to 'student.csv' file.

3) Right-click on the required CSV file, my case 'student.csv' file.

4) Then under security tab select your username. My case: pc(Desktop-OVRUUAF\pc)

5) Then click on the edit button.

select your username

6)A dialogue box will pop up, there select your username and click on 'Add' button.

add user

7)Then for simplicity create new object name as 'Everyone' then clicks on the OK button.

set to everyone permission

8)Then set full control permission to that particular user and then press 'Apply' button then 'ok' button. So, now our database server can access the file.

set full persmiiion