PgAdmin Import CSV: Step-by-Step Process for PostgreSQL

CSV is a file format that displays tabular records in plain text. Each entry in the records is separated from another by commas. This is the origin of the name CSV (Comma Separated Values). CSVs are used to transfer data across incompatible database programs, such as between open-source databases and proprietary database formats.

PostgreSQL is a Data Warehouse that stores data from Web-based and Mobile Applications. Many developers prefer this database system because it is compatible with both Relational and Non-Relational Databases.

How to Import CSV into PostgreSQL?: PgAdmin Import CSV

Before executing the PgAdmin Import CSV into the PostgreSQL process, you must convert the file into a compatible format for PostgreSQL databases. There are two ways to do this.

• Method 1: CSV to PostgreSQL using PgAdmin
• Method 2: CSV to PostgreSQL using SQL

Method 1: CSV to PostgreSQL using PgAdmin

To import CSV using this PgAdmin Import CSV method, you have to do the following:

• Click on the Tools tab at the top of your PgAdmin Home Page.
• Select the Query Tool in the drop-down menu that appears.
• Enter the title and columns in your CSV file as an SQL Query. In this tutorial, you’ll use a student record file as an example. 

Let’s say your CSV file with the filename ‘ students.csv’ looks like this:

First Name, Last Name, Grade
1, James, Sullivan, 54
2, Clara, Matthews, 73
3, Peter Garricks, 81

Your SQL Query for your PgAdmin table should be:

CREATE TABLE  students
(id serial,
first_name james,
last_name Sullivan,
grade 54 
);

• Click the “Import/Export” button located in the toolbar.

• Within the “Import/Export” dialog box, opt for the “Import” option.

• Choose the desired CSV file for import and define both the table name and the delimiter.
• Finalize the process by clicking the “Import” button to transfer the CSV data into the designated table.

At the end of this PgAdmin Import CSV method, the entire CSV file should now appear as a table in your PgAdmin table list. Click on the File tab at the top of the page to find the table. 

This is how you can use the PgAdmin Import CSV option to insert data with ease.

Method 2: CSV to PostgreSQL using SQL

You can also carry out PgAdmin Import CSV by writing an SQL Query that references your CSV file. This method requires you to use the COPY command to import data from the CSV file into PgAdmin.

Here’s how to do it: 

• Open the Internal Query tool by clicking on the ‘Tools’ tab at the top of your screen. 
• Write your SQL Query. The SQL Query for the CSV file ‘students.csv’ would take this format:

COPY students
FROM ‘C: astudents.csv 
DELIMITER ‘,’
CSV Header;

The ‘students’ entry after copy refers to the CSV filename in the query above. The entry written after FROM is the CSV file path. 

DELIMITER refers to the comma that separates the fields in the CSV file. The CSV Header instructs the query to skip the header (the column titles) when importing the CSV file. 

• Now, click on the Query Editor tab at the top of the page. The SQL Query you just created will appear. Then, tap on Messages to view your results.
• Since the sample CSV file we used has 3 columns, you should see the message 

COPY 3

After applying your preferred method to import the CSV file, you still need to confirm that the query was successful. To do this, you need to enter the following query in the Query tool:

SELECT  *  FROM students

To verify this PgAdmin Import CSV method, click on the Query Editor tab. In the Query Editor, tap Data Output. Your query is successful if the CSV file appears in a tabular format. 

This is how you can use the PgAdmin Import CSV option to insert data by specifying a file path.

Unlocking Data Transfer: Leveraging PgAdmin for Seamless Interaction with PostgreSQL Databases

PgAdmin is a Graphical User Interface built for interaction with PostgreSQL databases. This tool helps in the PgAdmin Import CSV process. This tool contains icons that allow PostgreSQL users to navigate database querying, storage, and application building on PostgreSQL databases.

If you run a tech company, you are probably storing your data in a proprietary format to secure it. Nevertheless, you might want to share some of your data with your subscribers. With an Open-Source database like PostgreSQL to store your app data, you won’t be able to import your data directly from your company database. This is because PostgreSQL databases are incompatible with proprietary database formats. 

Therefore, you need a file system like CSV, which supports most database formats to perform the transfer. Once you store your data from your company database in a CSV, you can implement the method, PgAdmin Import CSV.

Additional Reads:

• How to Export Data from MySQL into a CSV File?
• Export BigQuery Table to CSV: 3 Easy Methods
• Tableau CSV Import 101: How to Import CSV Files? | 4 Easy Steps

Conclusion

You have just carried out PgAdmin Import CSV methods to import CSV into the PostgreSQL database. Storing your databases on PostgreSQL is a convenient way to manage company data. In case you want to export data from a source of your choice into your desired Database/destination like PostgreSQL, then Hevo Data is the right choice for you! Sign up for Hevo’s 14-day free trial and experience seamless data migration.

FAQ on using PgAdmin to import CSV file