The table contains no data as implies here Let's examine the data exists in the table by running SELECT SQL statment. To execute SQL COPY statement you must be a super user alternatively you can use \COPY command from PostgreSQL console.Ĭonnect to postgresql database as superuser (here I’m connecting from pgAdmin III using the user postgres). import data from files into database tables or export data from database table into file).
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. 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.Ĭonnect 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.ģ- Load the spreadsheet (Excel - Libre Office calc) data from the CSV file into the PostgreSQL database table using COPY SQL statement. 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.
If we open the csv file saved earlier in text editor it will look like the following image.Ģ- Define the structure of the PostgreSQL database table and create it. Now we have prepared the CSV file which will be used by PostgreSQL COPY statement to load the data into database table. Īn options dialog appears in this dialog select “,” as Field delimiter and choose “ as a String delimiter and hit OK button. 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 …įrom the Save File dialog add the file name then choose the Text CSV from format, the following image shows this step.Ī confirmation message pops up choose use “Text CSV Format”. 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. We are going to save the data exists in this spreadsheet into CSV format (Comma Separated Values). Suppose that we have a spreadsheet file contains customers information with the following structure
Note that you can skip this step if you already have the data in CSV file format. ġ- Prepare the spreadsheet (Excel - LibreOffice calc) file and convert it to CSV format. Load the data from the CSV file we have prepared from the spreadsheet (Excel - LibreOffice calc) file in to the postgresql database table using postgresql COPY SQL statement.Define the structure of the PostgreSQL database table and create it.Prepare the spreadsheet (Excel - LiberOffice calc) file and convert it to CSV format.In this tutorial we will import data from spreadsheet (Excel, LibreOffice Calc) file into PostgreSQL database table using PostgtreSQL SQL COPY command.įor PostgreSQL 9.0 or heigher.