Database Management · Intermediate level

Intermediate level · Module 4: Stored Logic and Data Import · Lesson 1 of 3

Importing and Cleaning Data

Load CSV data into a staging table, clean it with SQL and move it into the real tables.

In this lesson you will

  • Import CSV data into staging tables
  • Clean and standardise data with SQL

Key concepts

staging tableCSV importTRIM, UPPER and REPLACEstandardising values

The lesson

Import data first into a staging table whose columns are all text, so nothing is rejected on the way in. PostgreSQL's COPY and graphical tools' import wizards load CSV files quickly.

Clean in SQL: TRIM spaces, standardise case, fix known spelling variations of locations with UPDATE … CASE, convert text to dates and numbers and flag rows that cannot be converted.

Insert the cleaned rows into the real tables with INSERT INTO … SELECT. The constraints there act as a final quality check. Keep the cleaning steps in a script so the import can be repeated.

Step by step

  1. Create a staging table and import the CSV.
  2. Trim and standardise names and locations.
  3. Convert dates and numbers, flagging failures.
  4. Insert the clean rows into the real tables.

Practice activity

Clean and load the messy beneficiary spreadsheet into the NGO database.

Expected result: Clean data loaded without constraint errors, and a list of rows that need checking by staff.

Check your understanding

  1. Why import into a staging table first?

    Show answer

    So raw data can be loaded without rejection and cleaned before entering the real tables.

  2. Why keep cleaning steps in a script?

    Show answer

    So the import can be repeated and reviewed.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.