Export PostgreSQL database table data to spreadsheet

In this tutorial I’ll show you the easiest way to export PostgtreSQL table data to CSV format on an external file (The CSV format is potable and can be used across multiple applications especially spreadsheet applications like Excel, LibreOffice Calc) using PostgtreSQL SQL COPY command.

Steps followed by this tutorial are

1.     Define the structure of the database table and create it and populate it with a data sample.

2.     Export the PostgreSQL database table to an external CSV file using PostgreSQL COPY SQL statement.

1- Define the structure of the database table and create it and populate it with a data sample.

A- Define the structure of the database table and create it

In this step we are going to define the structure of the customers database table and add the SQL DDL statement to create the table in PostgreSQL database.

The customers table is a simple database table constructs of 6 columns contains basic customers data first name, last name, phone, email address, address and the primary key column cust_id.

Connect to your PostgreSQL database server using a user that has create table privilege with the tool you prefer (shell , pgadmin, phppgadmin or any other tool ) and execute the following SQL DDL statement to create the table customers.

CREATE TABLE public.customers

(

cust_id integer,

first_name character varying(100),

last_name character varying(100),

phone character varying(20),

email character varying(20),

address character varying(300),

PRIMARY KEY (cust_id)

)

WITH (

OIDS = FALSE

) ;

B- Populate customers table with a data sample.

In this step we are going to add data to the customers database table using INSERT SQL statement.

Run the following code to insert some rows in the newly created customers database table.

 

INSERT INTO customers (cust_id,first_name,last_name,phone,email,address)
VALUES (1,'Amr','Nassr','+2011009227','amr@example.com','25 ku street, Zahraa, Sohag, Egypt.') ;

INSERT INTO customers (cust_id,first_name,last_name,phone,email,address)
VALUES (2,'Saeed','Osman','+3097863637289','saeed@example.com','25 ku street, Zahraa, Cairo, Egypt.') ;

INSERT INTO customers (cust_id,first_name,last_name,phone,email,address)
VALUES (3,'Waleed','Sayed','+3233434656','waleed@example.com','25 ku street, Zahraa, Giza, Egypt.') ;

INSERT INTO customers (cust_id,first_name,last_name,phone,email,address)
VALUES (4,'John','Smith','+9087654323','john@example.com','25 ku street, London, UK.') ;

INSERT INTO customers (cust_id,first_name,last_name,phone,email,address)
VALUES (5,'Tom','Louis','+1997656675','tom@example.com','25 ku street, California, US.') ;

INSERT INTO customers (cust_id,first_name,last_name,phone,email,address)
VALUES (6,'Mark','King','+56876878','mark@example.com','25 ku street, California, US.') ;

INSERT INTO customers (cust_id,first_name,last_name,phone,email,address)
VALUES (7,'Jacob','Smith','+7899754322','jacob@example.com','25 ku street, California, US.') ;

INSERT INTO customers (cust_id,first_name,last_name,phone,email,address)
VALUES (8,'Nick','Mier','+655788654','nick@example.com','25 ku street, Berlin, Germany.') ;

INSERT INTO customers (cust_id,first_name,last_name,phone,email,address)
VALUES (9,'Alberto','Gonza','+6554322897','alberto@example.com','25 ku street, Rome,Italy.') ;

The image shows executing sql insert statements using pgAdmin III.

Let's query the customers table to view the newly inserted data.

SELECT * FROM customers;

2- Export the PostgreSQL database table data to an external CSV file using PostgreSQL COPY SQL statement.

Note COPY is specific to PostgreSQL database and it’s not a SQL standard, COPY command is used to copy data between postrgesql database tables and files (i.e. import and export data).

To execute SQL COPY statement you must be a super user alternatively you can use \COPY command from PostgreSQL console.

Connect to PostgreSQL database as superuser (here I’m connecting from pgAdmin III using the user postgres) then execute the following COPY statement .

Note: Change the ‘D:\customers.csv’ to the path where you want to save the result csv file.

COPY customers TO 'D:\customers.csv' WITH (FORMAT csv,HEADER true);

The COPY statement include two options as follows.

FORMAT Option.
This option selects the data format to be read and this option can be one of following.: text, csv, or binary. Default option is text.

HEADER Option.
This option is allowed only when using CSV format. This option specifies that the first line in file contains a header of columns (i.e. include column names).

Also note that there are many other options but they are not mentioned here as they are not used.

Now let's open the file ‘D:\customers.csv’ to see if the data were exported or not.

To open the exported CSV file using LibreOffice Calc follow the next steps.

Go to File->Open then browse to location of the customers.csv file and hit Open.
When opening a CSV file in LibreOffice Calc an import dialog pops up, Choose the delimiters as displayed in the next image.
Note that if you may need to change the Character Set according to the encoding of the CSV file.

Then hit OK button to import the CSV file to LibreOffice Calc.

To open the exported customers CSV file using MS Excel follow the next steps.

First open MS Excel, From the Data menu select then hit From Text

Open the CSV file and set the delimiters as specified in the image below. Then hit Finish button.

From the next dialog choose existing worksheet if you want to open the imported csv data in the current worksheet  or new worksheet to open the imported csv data in a new worksheet then hit Ok button.

Finally the MS Excel will import the CSV data in the selected worksheet.

You may also interested in Importing data from spreadsheet (Excel, LibreOffice Calc, CSV) file into PostgreSQL database table.

You may also interested in Oracle reading data from spreadsheet file using external tables.