Excel into PostgreSQL
Continuing on data import into PostgreSQL database, this covers a quick way of loading Excel data while guessing (aka “imputing”) missing values. In this case, I’ll use RapidMiner’s Impute Missing Values operator with a k-nearest neighbors learning algorithm.
Import Excel with RapidMiner
Here’s a sample sales spreadsheet to import. Note, that some of the Region values are missing, but can be guessed at from other records.
The RapidMiner top-level process looks likes this:
However, the Impute Values operator takes a sub-process, a learning model that can make an educated guess at the missing values, based on surrounding data. Double-click into the ‘Impute Values’ operator. This will show it’s sub-process area. Drag a ‘k-NN’ operator over and connect the ‘exa’ and ‘mod’ ports.
Export to Postgres using RapidMiner
For the Write Database operator, creating the Postgres database connection is easy in RapidMiner:
Executing the process reads the spreadsheet, imputes the missing values and stores the data into a created ‘orders’ Postgres table:
Most importantly, you can see the missing values have been inserted as part of the data cleansing during this load process.
Importing tables from an Excel spreadsheet to PostgreSQL.
While working with large amounts of data, it is ideal to manage your data using a database management system. Database Management Systems are optimized for storing and managing large amounts of data. This simply means that you would need to move your datasets into the database management system.
In this tutorial, we will work through how to import data from a Microsoft Excel sheet, into the PostgreSQL Relational Database Management System.
Note that there are a thousand and one ways to go about this. This tutorial covers a step-by-step and an easily understandable way to import data from an Excel sheet into PostgreSQL.
- Convert your Excel sheet to a CSV file. To do this, click on file, click on save as, click on save as type in the drop down box that pops up and select CSV (Comma delimited), then click on save.
2. In PostgreSQL, create an empty table that contains the same column name, data type and column constraint as the columns in your Excel sheet.
3. In PostgreSQL, right click on the on the newly created table; a list of options would appear, click on the ‘import/export’ option.
4. At this point, a dropdown box appears. Here’s what to do with the dropdown box:
a. select the import option
b. on the file name tab, navigate to where your excel sheet is stored on your PC, by clicking the select file (…) button.
c. select ‘yes’ for the header option
d. select the comma (,) delimiter for the delimiter option.
5. An import job is created if all rules have been adhered to. A process watcher also appears, to help you track what is being done.
Finally! We have our table successfully imported into our RDBMS.
If you have followed through so far, give yourself a thumps up. Also leave me a comment and let me know if this was helpful. 🙂
Can I Upload Excel to PostgreSQL?
If we specifically talk about this question that “can I upload Excel to PostgreSQL in Windows 10?” then the answer to this question is a “No”. We cannot directly import the data from an Excel sheet to a PostgreSQL table in Windows 10. However, there are other workarounds available with the help of which we can easily import the data of an Excel file to a PostgreSQL table in Windows 10. We will be talking about one such workaround in the following sections of this article.
Then what can I do if I have a Large Excel Sheet whose Data I need within a PostgreSQL Table?
Now, you might wonder what you can do with an Excel sheet that you have and contains a large amount of useful data. Will that data go wasted as you will not be able to import it directly to a PostgreSQL table in Windows 10? Fortunately, not. You can still use this data by converting it into one such format supported by PostgreSQL in Windows 10. After converting your data into a relevant format, the import process becomes very simple, and you will be able to witness it through the steps that follow:
Step # 1: Conversion of an Excel Sheet to a CSV File in Windows 10:
Since the most commonly used file format while dealing with databases is CSV, therefore, we will first convert the target Excel sheet to a CSV file. There are other formats available out there too that PostgreSQL supports; however, over here, we chose to go with the most commonly used one and also the most convenient one, i.e. CSV. To convert an Excel sheet to a CSV file in Windows 10, you first need to locate that file by going to the target directory as shown in the image below and then open that Excel sheet with the MS Excel program.
In our target Excel sheet, we had some sample data, shown in the following image. The Excel sheet consists of three different columns, namely StudentName, StudentAge, and StudentGender. This Excel sheet has a total of five different entries or records. After opening the target Excel sheet, we will click on the “File” menu from the Menu Bar, as you can see from the image shown below:
In the “File” menu, we will locate and click on the “Save As” option as shown in the appended image:
In the “Save As” dialogue box, we will expand the “Save as type” dropdown list and select the “CSV (Comma delimited)” format as shown in the image below:
After that, we will select the location where we want to save our CSV file and hit the “Save” button.
Once the Excel sheet has been converted into a CSV file, the process of uploading it to a PostgreSQL table is exactly the same as that of a regular CSV file, and you will realize it by going through the following steps.
Step # 2: Creation of a Relevant Table in PostgreSQL in Windows 10:
Now, since we have a CSV file containing the desired data to be imported, we will create a PostgreSQL table to hold that data with the following query:
This query will create a table with the name student_Data with three different columns, i.e. StudentName, StudentAge, and StudentGrade, respectively. These three columns will hold all the data that will be imported from the CSV file.
When a PostgreSQL table is created successfully, the “CREATE TABLE” response will appear on the console.
Step # 3: Importing the CSV File Data to the PostgreSQL Table in Windows 10:
After creating a table in PostgreSQL in Windows 10, we will carry out the importing process of the CSV file data to this PostgreSQL table by running the query shown below:
The “COPY” keyword in this query will import the data from the CSV file to the PostgreSQL table. “student_Data” is the name of the PostgreSQL table where the CSV file data is going to be imported. Then the “FROM” keyword is proceeded by the path from where we will read the CSV file. Our CSV file name was “Excel.csv”. The “DELIMITER” keyword follows the delimiter used within the CSV file, which was a comma. The “CSV” keyword represents the format of the file that this query is going to read. Finally, the “HEADER” keyword specifies that there is a header in the CSV file that this query will read. This header row will be skipped once the data is imported into the PostgreSQL table, and the table header, i.e. the names of the table columns, will be utilized instead.
The successful execution of this query will display the “COPY 5” message on the console since there were a total of five different records to be imported from the CSV file to the PostgreSQL table in Windows 10.
Step # 4: Displaying the Contents of the PostgreSQL Table in Windows 10:
Finally, to confirm if our Excel sheet data (which then became the CSV file data) has been uploaded successfully into the PostgreSQL table or not, we will view the contents of the PostgreSQL table with the following query:
You can see from the image below that all the entries of the student_Data table are exactly the same as those of our Excel.csv file records. It means that uploading an Excel sheet (that was converted into a CSV file) to a PostgreSQL table in Windows 10 has taken place successfully.
Conclusion:
This article dealt with a very important question regarding the PostgreSQL RDBMS, i.e. whether we can upload Excel to PostgreSQL or not. This article satisfactorily provided the answer to this very common question. Moreover, it also provided a very safe method of actually using the Excel sheet data within a PostgreSQL table in Windows 10. After using this method, your Excel sheet data will never go wasted; rather, you will be able to use it effectively in any of 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.
PostgreSQL Excel Data Imports Using DBeaver
There are many ways to import data into a database, some are pretty straightforward, and others are more complex depending on the type of data you receive and which format it is in. I will show you how to import data from an excel file using the DBA tool DBeaver.
File Conversion
When you receive a file in Excel format, you must convert this data into a readable format. This format is usually .csv (Comma Delimited) but can often be in a format that has a delimiter that isn’t so common.
Excel file Conversion
Excel file extensions are usually xls or xlsx, open your file in Excel. You’ll need to convert this into a CSV file, which is extremely easy. All you need to do is Save As and change the “Save as type:” to “CSV (Comma delimited) (*.csv)” and click Save. The file name will change automatically, no need to change it to .csv.
Create csv Connection
Right-click your database connection (or anywhere if your connections are empty), click Create and then click Connection.
Make sure the All tab is selected on the left and scroll down to CSV. Click Next.
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.
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.
Data Import
Once the connection is made, you’re able to query the data just like you would any other table with sql syntax.
You can now import this data into your PostgreSQL table. Right-click the table you want to import into PostgreSQL and click Export Data.
Select Database and click Next.
Change the target container to the schema where your table is in PostgreSQL. Click Ok.
Select the target (table) where you want to import the data.
Click Columns and set the target column for each column of data. If you have headers already in your data that match the table columns, then this will automatically map. Click Ok. Click Next.
Set the options for connections and data load. If you only want to add data to the table and not overwrite the data, you will want to remove the option for truncating target table. Click Next.
Check to make sure the source and target are correct and then click Start to begin the import.
You can now select the data from your PostgreSQL table.
You can also create an empty table structure and select create under mapping in the Tables Mapping window, and it will create the columns for you. This is best used when you have headers.
When you select data from the table after the import, the first row in the import data will be what the columns are named.
*NOTE*
When you import data and have headers in your data, it will be used as the first row if you already have a table structure in place. This could cause your import to fail, or it could cause your data to be inconsistent with your column data.