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.
Some 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
Then save this file as CSV file as shown below.
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:
4. Then we get this below screen. Here, First, navigate to "Column" tab, initially we will find all the 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.
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.
Then we must get the message as below
6)Now, let us navigate to see the data in the "student" table.
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.
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?
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.
6)A dialogue box will pop up, there select your username and click on 'Add' button.
7)Then for simplicity create new object name as 'Everyone' then clicks on the OK button.
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.
9)Then goto Pgadmin and again run above script. Then your CSV content will be inserted to the table of the Postgresql database.
Other Related Posts: