Skip to main content
HomeAbout SQLLearn SQL

Working with Spreadsheets in SQL

In this tutorial, learn how to import a spreadsheet into PostgreSQL and perform analysis on it.
Feb 2019  · 5 min read

Spreadsheets are probably one of the most pertinent elements in an organization (be it of any kind). Often, there are situations where you need to have a very important spreadsheet(s) imported in a database server so that it can effectively be queried. In this tutorial, you will learn how to do this in PostgreSQL.

This tutorial assumes that you are already familiar with SQL and how to write simple SQL queries in PostgreSQL. If you are new to SQL, following are some resources which might help you -

This tutorial is divided into following two major sections -

  • Converting a spreadsheet into a .csv file
  • Importing a .csv file into a PostgreSQL database

Converting a spreadsheet into a .csv file

To get started you will need a spreadsheet to proceed with. You can create a spreadsheet in any Excel editor like Google Sheets, MS-Excel, etc. To learn the basics about spreadsheets, you can take DataCamp's Spreadsheet Basics course or read this beginner spreadsheet tutorial. To be able to follow along with this tutorial, you can use this spreadsheet containing the following columns -

  • ID
  • Name
  • Age
  • Percentage Marks

The spreadsheet has a total of 20 records.

Let's quickly look at snapshot (containing the first 10 rows) of the spreadsheet - spreadsheet

The spreadsheet contains information of a few college students.

We can infer the probable datatypes of the columns from the snapshot -

  • ID, Age and Percentage Marks are numeric columns. More specifically, ID and Age columns can be of integer type and the column Percentage Marks can be of float type.
  • The column Name is of character datatype.

The easiest way to import this spreadsheet (you can refer to it as dataset as well) into a PostgreSQL database is first to convert the spreadsheet into a .csv file and then import it in a PostgreSQL database. CSV stands for comma-separated values. This is two main advantages -

  • .csv files are much lighter than spreadsheets and therefore easier to handle in low memory.
  • There are many in-built utilities in PostgreSQL to handle .csv files in efficient ways.

Every Excel editor lets you convert a spreadsheet into a .csv file. On Google Sheets, you can do it using this navigation - File -> Download as -> Comma-separated values.

Importing a .csv file into a PostgreSQL database

Now to be able to import the .csv file you just created into a PostgreSQL database, you will need a PostgreSQL database first. Let's quickly create one using the pgAdmin interface.

You can create a database with any name with your choice. When you are done with that, you will need to create a table in that database which will hold the entries of the .csv file which you are going to import. Let's create a table named Student_Records in the database you just created.

CREATE TABLE Student_Records
(
  ID serial NOT NULL,
  Name varchar(50),
  Age SMALLINT,
  Percentage_Marks float8
);

You kept the datatypes of the columns as discussed above. For convenience, you kept the column names the same as well. Now navigate to the table you just created in pgAdmin and right click on the table and select Import from the options. For your reference - https://www.loom.com/share/2eeb1a2c94bf4f928c3ffa5b1b72eef4.

You can ignore the errors that happened during the imports as seen in the screencast. You can verify if the records were correctly imported or not by running a select query.

Let's now how the above can be done using the psql prompt. For doing that let's first truncate the table Student_Records using truncate table Student_Records; query.

Now, open up a psql prompt which should look like - psql prompt

Now switch to the database that you created for importing the spreadsheet initially. You can switch to a database in PostgreSQL using the following command -

\c database_name

If it was successful the psql prompt will get changed to the name of the database you specified. You can now issue the following command to have the .csv file imported into Students_Records table -

COPY Student_Records(ID,Name,Age,Percentage_Marks) FROM 'D:\Students.csv' DELIMITER ',' CSV HEADER;

Now execute a select query to verify if the import was done right.

You find more about the Copy command from here and play with its different options.

Conclusion

That is all for this tutorial. You can now import a spreadsheet into PostgreSQL and do your analysis just as you do with standard SQL tables.

SQL and Spreadsheets Courses

Introduction to SQL

BeginnerSkill Level
2 hr
372.4K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

10 Portfolio-Ready SQL Projects for All Levels

Select your first—or next—SQL project to practice your current SQL skills, develop new ones, and create an outstanding professional portfolio.
Elena Kosourova's photo

Elena Kosourova

11 min

How to Choose The Right Data Science Bootcamp in 2023 (With Examples)

Learn everything about data science bootcamps, including a list of top programs to kickstart your career.
Abid Ali Awan's photo

Abid Ali Awan

10 min

DataCamp Portfolio Challenge: Win $500 Publishing Your Best Work

Win up to $500 by building a free data portfolio with DataCamp Portfolio.
DataCamp Team's photo

DataCamp Team

5 min

What is Microsoft Fabric?

Discover how Microsoft Fabric revolutionizes data analytics and learn about how its core features empower businesses to make data-driven decisions.
Kurtis Pykes 's photo

Kurtis Pykes

10 min

Chroma DB Tutorial: A Step-By-Step Guide

With Chroma DB, you can easily manage text documents, convert text to embeddings, and do similarity searches.
Abid Ali Awan's photo

Abid Ali Awan

10 min

Performance and Scalability Unleashed: Mastering Single Table Database Design with DynamoDB

One table to rule them all: simplify, scale, and supercharge your NoSQL database!
Gary Alway's photo

Gary Alway

16 min

See MoreSee More