Export PostgreSQL database table data to spreadsheet

Submitted by عمرو نصر on Fri, 11/09/2018 - 16:32

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

) ;

pgadmin create customers table postgresql database
Creating customers table in PostgreSQL database using pgAdmin III

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.

pgadmin insert sql dml postgresql database
pgadmin insert sql dml postgresql database

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


SELECT * FROM customers;

sql query customers database table
pgAdmin III executing SQL queries against postgresql database table

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.

customers csv data as viewd in plain text editor
Customers CSV data as viewd in plain text editor.png

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.

import csv file to liberoffice calc
Import CSV file to LibreOffice calc

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

open csv file to liberoffice calc
Open 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

ms excel import csv data to excel
MS Excel import csv data to excel

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

ms excel import csv data to excel specifing delimiters
Specifing Delimiters

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.

ms excel import csv data to ms excel

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

ms excel import csv data to ms excel
The Imported CSV data into MS Excel

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.

Comments

GuestAbiny (not verified)

Sun, 12/02/2018 - 12:06

dating foreign ladies online dating messaging rules is online dating destroying love guardian speed dating gothenburg dating divorce attorney senior dating london ontario speed dating berlin 50 plus bhilai dating site dating sims for androids best dating apps in nyc dating yakuza 4 i am dating my boss dating rooms online writing my profile online dating disturbingly honest online dating confessions how is radioactive dating important for providing evidence for evolution answers com iol dating site free online dating ottawa ontario salisbury journal dating dcuo dating free mobile dating sites australia

Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.