How to import CSV file data into a PostgreSQL table?

My first task was to import CSV data to PostgreSQL after finishing the schema/table design. It was very challenging for me because I was new on PostgreSQL, but I was familiar with SQL Server. I searched on Google and found some great solutions, and I want to share it with you.

 

Using Command Prompt - psql -U postgres

 

I will first create a table to illustrate how to import a CSV file.

 

Test=# create table employee (id serial not null, name text, address text, phone text);

Here "Test" is the database

 

Execute following command to fetch records from the employee table.

 

Test=# select * from employee;

 

Now I will prepare the CSV data file using Microsoft excel. I will save this file at D: drive, so the path will be D:\employee.csv.

 

Import CSV file into a table using COPY statement

To import this CSV file into the employee table, you use the COPY statement as follows:

 

Test=# COPY employee(name,address,phone) FROM 'D:\employee.csv' DELIMITER ',' CSV HEADER;

To fetch the records from the employee execute the following records

 

Test=# select * from employee;

 

 

Some important things are given below, read them carefully.

1- If the columns name of the table and headers name of the CSV file are the same then no need to specify the name of columns in the query you can write simply as below.

Test=# COPY employee FROM 'D:\employee.csv' DELIMITER ',' CSV HEADER;

2- Type the column name of the table in the order of the CSV header in the COPY query. For example, see the following screenshot of the CSV file.

 

So you will specify the column in the query as #COPY employee name, address, phone. You cannot change the order of the column to the opposite of the CSV header.

Import CSV file into a table using pgAdmin

If you don't have the privileges of a superuser or you need to    import CSV data file from a client machine into a table in PostgreSQL server, you can use pgAdmin GUI tool to do that.

First of all, I will remove all data from the employee table using the following query.

 

TRUNCATE TABLE employee;

 Step 1- Right-click on the employee table and choose the import/export... menu as follows.

 Step 2- import/export --> a popup window will open as below, select the option carefully as shown in the screenshot.

 

 Column setup

 

Copy Process started

 

 Step 3- Execute the statement "SELECT * FROM employee" the result will show as below.

 

 

Created a function to import CSV data to the PostgreSQL table

Above solutions are the manual process means you have to create a table manually, if you are importing a CSV file that doesn't have fixed column or lots of columns, In that scenario, the following function will help you. First of all the following function import data into the table (choose a table name where you want to import the data) after some transformation you want to push data in the real table.

 Create the function using the following PostgreSQL statements.

 

create or replace function "public"."fn_load_csv_file"     (         target_table  text,          csv_file_path text,         col_count     integer     )        returns void as $$ declare     iter      integer;      col       text;      col_first text; begin     set schema 'public';     create table temp_table ();     for iter in 1..col_count     loop         execute format ('alter table temp_table add column col_%s text;', iter);     end loop;     execute format ('copy temp_table from %L with delimiter '','' quote ''"'' csv ', csv_file_path);     iter := 1;     col_first := (select col_1                   from temp_table                   limit 1);     for col in execute format ('select unnest(string_to_array(trim(temp_table::text, ''()''), '','')) from temp_table where col_1 = %L', col_first)     loop         execute format ('alter table temp_table rename column col_%s to %s', iter, col);         iter := iter + 1;     end loop;     execute format ('delete from temp_table where %s = %L', col_first, col_first);     if length (target_table) > 0 then         execute format ('alter table temp_table rename to %I', target_table);     end if; end; $$ language plpgsql;

 Now, your function is ready to use, execute the following query a see the results.

 

DROP TABLE employee; SELECT public.fn_load_csv_file('employee', 'D:\\employee.csv', 3); SELECT * from employee;\

 target_table: pass here table name, 

csv_file_path: pass the pathe of csv file here,

col_count: pass total number of columns you want to import from CSV.