PostgreSQL

How Do I Import a CSV File Into PostgreSQL?

The PostgreSQL RDBMS can be used to perform a large number of different operations on the data that include the creation of database, and deletion of tables, and the insertion, deletion, modification, and updating of the data, as well as importing and exporting data, etc. The data can be imported into a PostgreSQL table very conveniently. Moreover, this RDBMS supports a wide range of different import file formats. This tutorial will show how a CSV file can be imported into PostgreSQL in Windows 10.

The CSV File Format:

CSV stands for Comma Separated Values. It is a file format used to store records of data, and each attribute of a record is separated by a comma. This is one of the most frequently used file formats, along with many others, such as JSON and xlsx.

The Procedure of Importing a CSV File Into PostgreSQL in Windows 10:

If you want to import a CSV file into PostgreSQL in Windows 10, then you will have to perform the following four main steps:

Step # 1: Creating a CSV File in Windows 10:

First, you need to create a CSV file in Windows 10 (if you do not already have a CSV file created that you want to import into PostgreSQL). A CSV file in Windows 10 can be created very easily. You just need to go to the preferred directory to create this file and right-click on any available empty space. Doing this will launch a menu from which you need to proceed, as shown in the image below:

Following this process will create an MS Excel Sheet in the desired directory. Now, you need to populate this MS Excel Sheet with the data shown in the following image:

You can also populate it with your desired data. However, our MS Excel Sheet consists of three different columns, i.e., Name, Age, and Gender. Moreover, this MS Excel Sheet has nine different records, as shown from the image above.

Once you have populated your MS Excel Sheet with the desired data, the next step is to save it in the CSV format. For that, you will have to click on the File option from MS Excel Menu Bar, as shown in the image above.

Now, you need to tap on the “Save As” button:

This will launch a window on your screen from where you will be able to select the CSV file format from the available list, as highlighted in the image that follows:

After selecting the CSV file format, click on the “Save” button to save the newly created CSV file to the desired location.

Now, our CSV file has been prepared that we will be importing later into PostgreSQL. We have named this CSV file as CSV.csv.

Step # 2: Creating a Table in PostgreSQL in Windows 10:

Once a CSV file has been created, you need to create a table in PostgreSQL which can hold the data imported from this CSV file. For continue, you need to access the PostgreSQL environment through the Windows 10 command prompt first (the procedure of doing so has been discussed in detail in our previous articles on PostgreSQL). Once you are within the PostgreSQL environment, you need to create a table with a suitable format that can hold all the records from the CSV file that we have just created. The query for creating a table in the PostgreSQL environment is shown below:

# CREATE TABLE bio_Data (Name VARCHAR (255) NOT NULL, Age INT NOT NULL, Gender VARCHAR (255) NOT NULL);

In this query, we want to create a table named bio_Data, which has three different columns or attributes, i.e., Name, Age, and Gender. These three attributes will hold all the records of our CSV.csv file.

After successful execution of this query, the “CREATE TABLE” response will be displayed on the console as shown in the following image:

At this stage, a table in PostgreSQL has been created to which we are going to import the target CSV file in the next step of this article. The only thing that is needed to be taken care of while creating this table is that it should have the same number of columns as that of our target CSV file. Only then, the import process will take place successfully.

Step # 3: Importing Data From a CSV File to a PostgreSQL Table in Windows 10:

To import the data of the target CSV file to the newly created PostgreSQL table, the following query needs to be executed in the console:

# COPY bio_Data FROM ‘C:\CSV.csv’ DELIMETER ‘,’ CSV HEADER;

Now, we will explain to you this query in detail. The “COPY” statement of this query will simply perform the whole import process. The “bio_Data” represents the name of the table to which the target CSV file will be imported. Then, there is a “FROM” keyword, after which we have stated the path from where the target CSV file is to be read. Next, there is a “DELIMITER” keyword which will follow the delimiter that has been used within the target CSV file. Since we had created a comma-separated CSV file, therefore, the delimiter in our case will be ‘,’. Then, the “CSV” keyword will tell which file format we are importing into our PostgreSQL table. Finally, the “HEADER” keyword represents that the target CSV file comprises a header row at the beginning of the records that will be skipped during the import process.

If you have specified all of the previously mentioned parameters correctly, executing this query will display the “COPY 9” success response on the console.

By now, the target CSV file has been successfully imported to the PostgreSQL table. However, this will be confirmed with the help of the next step.

Step # 4: Viewing the PostgreSQL Table in Windows 10 to See If the Data Has Been Imported Successfully or Not:

We can view our PostgreSQL table to confirm whether the import process has taken place successfully or not. If it contains all the records of our CSV file, then the data from our target CSV file has been imported successfully to our PostgreSQL table. Otherwise, it was not successful. To view our PostgreSQL table on the console, we will execute the following query:

# SELECT * FROM bio_Data;

This query will display all the records from the bio_Data table of PostgreSQL. You can verify from the records of this table shown in the following image that the data from our target CSV file has been imported correctly to our PostgreSQL table in Windows 10.

Conclusion:

This article showed you a detailed method of importing a CSV file into PostgreSQL in Windows 10. It discussed all the steps in-depth, from creating a CSV file to importing it into a PostgreSQL table so that you can perform this procedure very conveniently. After carefully going through all of these steps, you will not find any difficulty importing any desired CSV file to your PostgreSQL tables in Windows 10.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.