Categories
Uncategorized

PostgreSQL Excel Data Imports Using DBeaver

There are many ways to work with data, here is a quick walkthrough how to transfer data from an Excel file into a database.

Excel-File -> CSV-File -> DBeaver -> PostgreSQL

Excel file

We have a simple Excel file:

with a simple sample data structure:

Excel file conversion

We need to convert the Excel file into a .csv (Comma Seperated Values) file.

Just open the file -> Datei -> Exportieren > Dateityp ändern -> CSV
and save as sample.csv

Import from CSV

In DBeaver:

Open Database connection -> database -> schema -> Rightclick -> Data import:

Import from CSV:

Change delimiter (Spaltentrennzeichen) from , to ;

click "Auto assign", change target to "sample"

TADA! We have a new table "sample" in our database:

Create CSV connection

Let's try another way. Delete table "sample" and create a connection to the CSV file:

Click Browse… and select the folder where your csv file is that you saved from Excel. You’re selecting a folder here, not a file. The connection will load ALL csv files in that folder. Click Finish.

Download the driver, if using for the very first time:

You will now see a new connection; this connection is set up exactly like a regular connection. Each csv file in your folder will be set up as a table with columns.

But there is a problem with the columns, it's just one, not three:

Doublecheck delimiter, but it is already set to ;

Ah, in driver details we have to set the separator from , to ;

Looks better now:

Export CSV data into database:

Once again we have our sample table in our database:

Leave a Reply

Your email address will not be published. Required fields are marked *