How To Fill A Csv Excel File For Import In The Sql

CSV files provide a great fashion to collect and store information, and they are often used as a reliable manner to move tabular array information between servers. In this article, we'll expect at 2 ways of importing and exporting PostgreSQL tables from and to CSV files – using the congenital-in import and export functionality of dbForge Studio for PostgreSQL and using the COPY command.

Creating a CSV file and preparing the table structure

Commencement of all, we'll demand a CSV file with information and a basic table structure that will be able to firm this data.

You can use any CSV file on your estimator for this example. If you have no CSV files to utilize, there are two ways you can create such a file:

• Creating it in a specialized application (e.g., Microsoft Excel)

A CSV file containing table information created in specialized software

• Using any text editor to create a new file, pasting some comma-delimited text data into information technology, and saving it equally a CSV file (for example, table_data_import.csv).

A CSV file containing table data created in a text editor

After that, we demand to create an empty table in your database. This table'south columns should represent how the data from the CSV file will exist interpreted. For case:

CREATE Table sample_table  (     proper noun varchar(l),     email varchar(255),     capital_city varchar,      land varchar(two) );

Executing a script that creates a tabular array structure in our database

Once we accept both the CSV file and the tabular array construction, we can really examination the import and export processes for PostgreSQL table data.

Importing a CSV file using a COPY statement

Ane manner of importing a CSV file into a table is using the Re-create statement. This command copies all records from a specified file into a target database table. Hither's the basic syntax of the Re-create command:

Copy  FROM 'location + file_name' DELIMITER ',' CSV HEADER;

<table name> – the name of the tabular array you desire to import information into.
'location + file_name' – the full path to the file you're importing data from.
DELIMITER ',' – specifies the delimiter we use as the comma symbol.
CSV – specifies the format of a file nosotros're importing data from.
HEADER – specifies that the target file contains a header row that should exist skipped during the import process.

In our case, the statement will look like this:

COPY sample_table FROM 'D:\csv_examples\table_data_import.csv' DELIMITER ',' CSV HEADER;

Executing a Re-create command to import data from a CSV file to a tabular array

Importing a CSV file in dbForge Studio for PostgreSQL

Alternatively, you tin import a CSV file into a table directly from dbForge Studio for PostgreSQL.

To practise this, get to Database → Import Data…
The Data Import wizard will be opened. Here, choose CSV and specify the path to the file in the File Name section located at the bottom of the window.

From here, the Data Import Wizard volition guide you through configuring the settings of the import procedure.

Importing data from a CSV file to a table using dbForge Studio for PostgreSQL's functionality

Exporting a CSV file using a Re-create argument

The process of exporting PostgreSQL data into a CSV file is very like to what we've been doing for importing. The but deviation here is that yous need to modify the FROM keyword in the Copy command to the TO keyword. By doing this, you will make the command export the data from the target table into a specified CSV file. Here's what the control will look similar in this instance:

Copy sample_table TO 'D:\csv_examples\table_data_export.csv' DELIMITER ',' CSV HEADER;

Executing a Copy command to export data from a table to a CSV file

Exporting a CSV file in dbForge Studio for PostgreSQL

You can also export data from a tabular array into a CSV file straight from dbForge Studio for PostgreSQL.

Outset, yous would simply need to navigate to Database → Export Data…
This will prompt the Data Export wizard to open, and from that indicate, information technology will provide yous a wide range of settings for the export process.

Exporting information from a tabular array to a CSV file using dbForge Studio for PostgreSQL'due south functionality

Decision

In this commodity, we looked at two ways of importing and exporting PostgreSQL tables from and to CSV files. To get a improve insight into the question, you can learn more about the data import and export functionality of dbForge Studio for PostgreSQL. You tin download dbForge Studio for PostgreSQL and check its full capabilities in a free 30-day trial version. If y'all wish to automate this process, feel free to bank check out the latest tutorial by Skyvia deject platform.

• Author
• Recent Posts

postgresql, postgresql tools, studio for postgresql

Source: https://blog.devart.com/how-to-import-and-export-csv-files-into-a-postgresql-database.html

Posted by: maureroback1967.blogspot.com

Share this post