CSV is a universally accepted file data collection format, and many applications output their data in CSV form. This can range from simple shell scripts used to gather monitoring data to more complex web form submissions. It can also be a “data dump,” if want to move your data from one database server to another for testing or simply move to new hardware.
Here we will walk through the basic steps you would need to follow to import a .csv file successfully into a PostgreSQL database. We will explain it using two different options: first, when you are already logged into the database and then call the file from inside a psql prompt; and second, from the shell prompt itself.
Creating a .csv file
The first step is to create a .csv file. You can use any existing file, or you can use the data below that comprises a basic .csv file:
Capital_City,State,Abbreviation,Zip_Code
Montgomery,Alabama,AL,36104
Juneau,Alaska,AK,99801
Phoenix,Arizona,AZ,85001
Little Rock,Arkansas,AR,72201
This data is comma delimited, so that we can use each comma as an identifier. Let’s copy and save this data to a text file using any of the existing text editors on your system (VI, notepad, textedit) and save it as “usa.csv”.
Creating the table structure
To ensure that our data falls into the right places in the database we need to first create a table structure inside the database.
postgres=# CREATE TABLE usa
(Capital_city varchar,
State varchar,
Abbreviation varchar(2),
zip_code numeric(5)
);
CREATE TABLE
postgres=#
Importing from a psql prompt
Now that we have the data in a file and the structure in our database, let’s import the .csv file into the table we just created.
We will use the COPY command to copy all the records from the .csv file to the table “usa”. Here is the copy command for your reference:
\COPY <table name> FROM 'location + file_name' DELIMITER ',' CSV HEADER;
To understand the execution steps, let’s look at each item in this command:
\COPY: This is the command to copy the record to / from the .csv file.
<table name>: Provides the table name where you want to import the data.
FROM: Specifies that we are going to import from a file (we will also be using TO in order to export it to a file at a later stage).
'location + file_name': An absolute path to the file (make sure you have read access to the file).
DELIMITER ',': Specifies the delimiter, which in our case is a comma: ‘,’.
CSV Specifies the file type from which we are going to import.
HEADER Signifies that we have a header row in our .csv file and while importing we should ignore the first row (similarly, while exporting we can use this to specify whether we want to include or exclude the header file).
Now, let's add the information we need into the command at the database prompt (make sure you are already logged into the database and connected to the one where you created the table):
postgres=# \copy usa from '/Users/EDB1/Downloads/usa.csv' delimiter ',' csv header;
COPY 4
Importing from a shell prompt
Once again we will call the COPY command from within the shell prompt while referring to the same file:
./psql -d postgres -U postgres -c "\copy usa from /Users/EDB1/Downloads/usa.csv delimiter ',' csv header;"
Password for user postgres:
COPY 4
In this command, we have used the same syntax that we used while trying to import from the psql prompt, but this method can help us with scripting the import and creating a scheduled job when trying to import the data.
Exporting data to a .csv file
By now we should understand the COPY command and its execution parameters. Just like with importing, the COPY command can also export the contents of the table to a file. By replacing FROM to TO, we can simply reverse the process::
\COPY <table name> TO 'location + file_name' DELIMITER ',' CSV HEADER;
Using the same table as an example, we will export the data to a .csv file.
From a psql prompt use the following command:
\copy usa TO '/Users/EDB1/Downloads/amit.csv' DELIMITER ',' CSV HEADER;
COPY 4
From a shell prompt, it will work as follows:
./psql -d postgres -U postgres -c "\copy usa to /Users/EDB1/Downloads/amit.csv delimiter ',' csv header;"
Password for user postgres:
COPY 4