Skip to main content

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.

Intermediate SQL Queries

Beginner
4 hours
1,484,916
Master the basics of querying tables in relational databases such as MySQL, SQL Server, and PostgreSQL.
See DetailsRight Arrow
Start Course

SQL for Joining Data

Beginner
5 hours
353,078
Join two or three tables together into one, combine tables using set theory, and work with subqueries in PostgreSQL.

Intermediate Spreadsheets

Beginner
4 hours
38,326
Expand your spreadsheets vocabulary by diving deeper into data types, including numeric data, logical data, and missing data.
See all coursesRight Arrow
Related
Data Science Concept Vector Image

How to Become a Data Scientist in 8 Steps

Find out everything you need to know about becoming a data scientist, and find out whether it’s the right career for you!
Jose Jorge Rodriguez Salgado's photo

Jose Jorge Rodriguez Salgado

12 min

How to Become a Data Analyst in 2023: 5 Steps to Start Your Career

Learn how to become a data analyst and discover everything you need to know about launching your career, including the skills you need and how to learn them.
Elena Kosourova 's photo

Elena Kosourova

18 min

What is SQL Used For? 7 Top SQL Uses

Discover the uses of SQL in industries and specific jobs. Plus, learn why the SQL language is so versatile and in demand.
Natassha Selvaraj's photo

Natassha Selvaraj

11 min

DC Data in Soccer Infographic.png

How Data Science is Changing Soccer

With the Fifa 2022 World Cup upon us, learn about the most widely used data science use-cases in soccer.
Richie Cotton's photo

Richie Cotton

Sports Analytics: How Different Sports Use Data Analytics

Discover how sports analytics works and how different sports use data to provide meaningful insights. Plus, discover what it takes to become a sports data analyst.
Kurtis Pykes 's photo

Kurtis Pykes

13 min

How to Write a Bash Script: A Simple Bash Scripting Tutorial

Discover the basics of bash scripting and learn how to write a bash script.
Kurtis Pykes 's photo

Kurtis Pykes

5 min

See MoreSee More