Importing data from spreadsheet (Excel, LibreOffice Calc, CSV) file into PostgreSQL database table.

Submitted by عمرو نصر on Mon, 11/05/2018 - 11:21

In this tutorial we will import data from spreadsheet (Excel, LibreOffice Calc) file into PostgreSQL database table using PostgtreSQL SQL COPY command.

Steps followed by this tutorial are

  1. Prepare the spreadsheet file and convert it to CSV format.
  2. Define the structure of the database table and create it.
  3. 3- Load the data from the CSV file we have prepared from the spreadsheet file into the postgresql database table using postgresql COPY SQL statement.

1- Prepare the spreadsheet file and convert it to CSV format.

Note that you can skip this step if you already have the data in CSV file format.

Suppose that we have a spreadsheet file contains customers information with the following structure

 

cust_id

First Name

Last Name

Phone

Email

Address

1

Amr

Ahmad

+201155487100

amr@yahoo.om

Egypt, Sohag

2

Sameh

Hamdeen

+201002544328

sameh@yahoo.com

Egypt, Mansoura

3

Waleed

Nassr

+982009926543

 

Saudi Arabia, Riyadh

4

Mohammed

Nassr

+928764940400

m32@gmail.com

Qatar, Doha

5

Maher

Saad

+97394393939

 

Bahrain, Manama

6

Ammir

Saad

+98363636622

 

Saudi Arabia

7

John

Smith

+199865445098

 

US, Washington DC

We are going to save the data exists in this spreadsheet into CSV format (Comma Separated Values).

Here (in this tutorial) I’m using LibreOffice Calc you can use Microsoft Excel or any other spreadsheet application you prefer and you will find options corresponding to those used in this example with LibreOffice Calc.

Open the spreadsheet file that contains the data you want to save as CSV in the LibreOffice Calc and then menus choose File → Save As …

open customers spreedsheet data in liberoffice calc

From the Save File dialog add the file name then choose the Text CSV from format, the following image shows this step.

 

convert spreadsheet data int CSV format using liberoffice calc

A confirmation message pops up choose use “Text CSV Format” .

 

confirm using CSV format from liberoffice calc spreadsheet

An options dialog appears in this dialog select “,” as Field delimiter and choose “ as a String delimiter and hit OK button.

 

CSV options in liberoffice calc

Now we have prepared the CSV file which will be used by PostgreSQL COPY statement to load the data into database table.

If we open the csv file saved earlier in text editor it will look like the following image.

 

customers data in CSV format as showen in a plain text editor (gedit)

2- 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

) ;

3- Load the spreadsheet data from the CSV file into the PostgreSQL database table using COPY SQL statement.

Note that COPY is not SQL standard it’s related to PostgreSQL and used to move data between database tables and files ( Import or Export data i.e. import data from files into database tables or export data from database table into file).

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).

Let's examine the data exists in the table by running SELECT SQL statment.

 

SELECT * FROM customers;

The tab

le contains no data as implies here

pgadmin sql query for postgresql database table

Now we are going to import data from the CSV file to the customers database table. 

Note to change the D:\customers.csvto the path where your csv file exisits.

 

COPY customers FROM ‘D:\customers.csv’ WITH (FORMAT csv, HEADER true) ;

Running the COPY SQL statement
Running the COPY SQL statement in pgadmin III

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 query the database table customers to see if the data were imported or not.

 

SELECT * FROM customers;

And as we can see here the result of the query show that the data were imported successfully.

pgadmin III query select data from database table

 

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

 

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.