PostgreSQL

Can I Upload Excel to PostgreSQL?

We all know (especially if we are frequent or regular users of the PostgreSQL RDBMS) that the PostgreSQL RDBMS deals with data from various formats. However, there are still some formats whose compatibility with the PostgreSQL RDBMS is questionable. One such file format is the Excel format, i.e. xls or xlsx. Many people wonder if we can directly import data from an Excel sheet to a PostgreSQL table in Windows 10 or not. Well, to explore the answer to this question, you will have to go through this article. Moreover, in this article, we have also shared a method that will let you use your Excel sheet data within a PostgreSQL table in Windows 10.

Can I Upload Excel to PostgreSQL in Windows 10?

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:

# CREATE TABLE student_Data (StudentName VARCHAR (255) NOT NULL, StudentAge  INT NOT NULL, StudentGrade VARCHAR (255) NOT NULL);

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:

# COPY student_Data FROM ‘C:\Excel.csv’ DELIMITER ‘,’ CSV HEADER;

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:

# SELECT * FROM student_Data;

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.