Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Clean a messy HR CSV in PostgreSQL by preserving the original file, importing uncertain fields into a text-based staging table, profiling the values, applying explicit repair rules, and validating the result. This workflow avoids guessing what a blank, unusual label, or repeated employee number means—and keeps you from presenting unverified changes as facts about a dataset.
Start with the source and protect the original
Record where the CSV came from, when you obtained it, the terms governing its use, and a checksum if reproducibility matters. Keep an untouched copy and work from a separate file or database table. Do not publish real employee information or credentials.
The IBM HR Analytics Employee Attrition & Performance file is one possible practice dataset, not a confirmed input for this walkthrough. Its Kaggle listing describes it as fictional and shows fields including Age, Attrition, BusinessTravel, Department, EducationField, and EmployeeNumber. View the dataset listing.
Inspect the CSV before importing
Check the header names and order, delimiter, encoding, line endings, quoting, and representative records. Confirm whether a blank-looking field means missing data or an intentional empty string. A CSV can contain embedded newlines inside quoted fields, so counting physical lines is not necessarily the same as counting records.
#1 Best Overall
PostgreSQL’s CSV rules make the distinction between nulls and empty strings important: with the default CSV convention, an unquoted empty field is NULL, while a quoted empty field is an empty string. Whitespace inside a quoted value is data too. PostgreSQL states, “In CSV format, all characters are significant.” Do not trim every field indiscriminately; decide which columns should have surrounding whitespace removed. See the PostgreSQL 17 COPY documentation.
Import into a raw staging table
When formats and value conventions are uncertain, stage columns as text first. This keeps PostgreSQL from silently interpreting a value according to an assumed type before you have examined it.
Rank #2
CREATE TEMP TABLE hr_raw (
age text,
attrition text,
business_travel text,
department text,
employee_number text,
monthly_income text
);
COPY hr_raw (age, attrition, business_travel, department, employee_number, monthly_income)
FROM '/path/to/hr.csv'
WITH (FORMAT csv, HEADER true);This is an adaptable example, not a tested script for a particular file. Replace the columns with the actual CSV header and order. In server-side COPY, the path is read by the database server process; when using psql, copy is a client-side alternative. Consult PostgreSQL’s documentation for header, CSV, null, and import options.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
COPY FROM runs destination triggers and check constraints. Its default error behavior is to stop when it encounters an error. PostgreSQL’s documentation discusses alternate error handling for supported versions; do not use an option that skips bad rows without recording and reviewing what was excluded.
Profile the values before changing them
Establish a baseline for row counts, missing values, blanks, labels, and candidate keys. In the examples below, btrim is used to detect values that are empty after trimming; it does not change the stored data.
SELECT count(*) AS rows FROM hr_raw;
SELECT
count(*) FILTER (WHERE age IS NULL) AS age_nulls,
count(*) FILTER (WHERE btrim(age) = '') AS age_blanks,
count(*) FILTER (
WHERE employee_number IS NULL OR btrim(employee_number) = ''
) AS missing_employee_number
FROM hr_raw;
SELECT department, count(*)
FROM hr_raw
GROUP BY department
ORDER BY count(*) DESC, department;
SELECT employee_number, count(*)
FROM hr_raw
GROUP BY employee_number
HAVING count(*) > 1;These queries show how to investigate a table; they are not findings about any specific HR file. A repeated employee number might be a duplicate, a multi-row history model, or a source-specific identifier convention. Inspect the associated records and confirm the key’s meaning before deleting anything.
Write field-specific cleaning rules
Decide what each transformation means before applying it. Trimming a department label may be reasonable, while changing an unusual job title or filling a missing satisfaction score requires a domain decision. Preserve original values in the raw table or in separate raw columns, and write cleaned output separately so changes can be traced.
Recommended Free Tools
- Whitespace: Trim only columns where surrounding spaces are accidental, and retain the raw value for comparison.
- Categories: Query distinct values first, then map known variants with an explicit mapping. If a field uses
Yes/No, confirm the observed labels before converting them. Do not turn every unexpected value intoNoor NULL. - Numbers: Check the source format and plausible range before casting. Route unparseable values to review rather than assuming a replacement.
- Missing and rejected values: If a value is converted to NULL or excluded, record the original value and the number of affected rows.
- Identifiers: Confirm the uniqueness rule and whether repeated IDs are valid before enforcing a key.
A typed destination can express rules after you have confirmed that they fit the source:
CREATE TABLE hr_clean (
employee_number integer PRIMARY KEY,
age integer CHECK (age BETWEEN 14 AND 100),
attrition boolean,
department text,
monthly_income numeric CHECK (monthly_income >= 0)
);This schema is illustrative, not a validated definition for a particular dataset. Confirm the field meanings, acceptable ranges, uniqueness rules, and missing-value policy with the data owner before adding constraints. Because COPY FROM invokes destination check constraints and triggers, a constraint can make an import fail when the incoming data violates it.
Validate the cleaned table and keep an audit trail
Repeat the baseline checks against the cleaned table. Compare row counts, missingness, category domains, and key uniqueness; inspect every category or value that changed, was rejected, or became NULL. Keep a simple record of each rule, the number of affected rows, and any unresolved records.
Do not claim a clean-data percentage or an attrition rate unless you have calculated it from the exact file and defined the denominator. A successful import alone does not establish that the values are complete or analytically sound.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Use HR analyses without overstating what they show
The Kaggle listing describes the IBM example as fictional, so it can illustrate SQL cleaning and exploration but should not be presented as a representative real-world workforce without independent evidence. The listing suggests questions such as grouping distance from home by job role and attrition, or comparing average monthly income by education and attrition. Those are examples of analyses to run after checking the relevant fields and their meanings, not conclusions about any particular workforce.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
