Free tools Windows power users keep installed
One-click scans. No signup required.
Clean an HR CSV in PostgreSQL by preserving the original file, importing uncertain fields into a text-based staging table, profiling before changing anything, and writing documented repairs into a separate typed table. The exact defects and results depend on the file: no specific CSV, SQL transcript, or before-and-after counts are established here, so the examples below are a reproducible starting point—not claims that particular rows were fixed.
Start with the file and its provenance
Before importing, record where the CSV came from, when you obtained it, its license or permitted use, and a checksum if others need to reproduce the work. Keep an untouched copy; perform transformations on a staging or derived table rather than overwriting the source.
As an Amazon Associate I earn from qualifying purchases.
Do not expose real employee details or database credentials in a published example. If you use the commonly circulated IBM HR Analytics Employee Attrition & Performance dataset, its Kaggle listing describes it as fictional and includes fields such as Age, Attrition, BusinessTravel, Department, EducationField, and EmployeeNumber. That description applies to that listing, not to HR datasets generally. See the dataset listing.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Inspect the CSV before loading it
Check the header and a sample of records, then establish the delimiter, encoding, line endings, quoting rules, and how the source represents missing values. A CSV record can contain an embedded newline inside a quoted field, so counting physical lines is not necessarily the same as counting records.
#1 Best Overall
PostgreSQL’s CSV behavior makes blank-looking values worth checking: by default, an unquoted empty field is NULL, while a quoted empty field is an empty string. Quoted whitespace is data, too; the documentation notes, “In CSV format, all characters are significant.” Trim only when a field-specific rule justifies it. PostgreSQL 17 COPY documentation.
Load uncertain values into a raw staging table
When formats or meanings are not yet confirmed, load columns as text first. This keeps the imported representation available for inspection before numeric conversion or category normalization. Adapt column names and ordering to the actual CSV header.
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 illustrative SQL skeleton, not a tested script for a particular file. Server-side COPY reads the path from the database server’s environment. With psql, copy is a client-side alternative that reads from the client machine. Confirm the target column list and CSV structure before running either command. HEADER true tells PostgreSQL to skip the header record; it does not validate that the header names match your columns.
Profile the imported data before repairing it
Establish basic counts and inspect nulls, empty strings, whitespace-only values, category variants, and possible duplicate keys. These queries show how to investigate; they do not imply that any particular HR file has these defects.
SELECT count(*) AS rows FROM hr_raw;
SELECT
count(*) FILTER (WHERE age IS NULL) AS age_nulls,
count(*) FILTER (WHERE age = '') AS age_empty_strings,
count(*) FILTER (WHERE btrim(age) = '') AS age_blank_or_whitespace,
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;In the missingness query, the whitespace-inclusive condition also counts empty strings; it is useful as a combined blank check, while the separate counts help distinguish NULL from empty values. Review distinct category values before deciding which labels are equivalent. A repeated employee number is a signal to investigate, not proof that a row should be deleted: it may reflect a duplicated record, a history table, or a source-specific identifier convention.
Write explicit, reviewable cleaning rules
Make each transformation a documented decision tied to a field. Trimming surrounding spaces may be appropriate for a department label, but changing a satisfaction score, unusual job title, or missing value requires its own rule. Preserve the raw values, either in the staging table or in a separate audit structure, so that changes can be traced.
- For category variants, inspect the actual values and map only known equivalents. Do not turn every unexpected value into a familiar category.
- For numbers, check the format and plausible domain range before casting. Quarantine or report values that do not parse instead of silently coercing them.
- For missing values, decide whether the correct representation is NULL, an empty string, a documented category, or an unresolved record. Do not treat these as interchangeable.
- When values are changed, rejected, or converted to NULL, retain the original value and record the rule and affected-row count for review.
A typed destination table can express rules after the data owner confirms field meanings, legal ranges, missingness, and identifier semantics:
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 →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 HR source. A primary key and check constraints can reject records that violate the assumed rules. PostgreSQL’s COPY FROM invokes destination triggers and check constraints. Its default error action is to stop when an error occurs; do not silently discard invalid rows. Behavior and available options can depend on the PostgreSQL version, so verify the documentation for the server you use.
Validate the cleaned table and keep an audit trail
After transformation, repeat the checks that matter and compare them with the raw staging data. Validation should answer whether the output is usable for its intended analysis, not simply whether an insert completed.
- Compare source and destination row counts, explaining any rows deliberately excluded or held for review.
- Recheck missingness and category domains in the cleaned columns.
- Test key uniqueness only if the source’s identifier rules establish that uniqueness is expected.
- Inspect changed, rejected, or unparsed values against their preserved raw versions.
- Record each cleaning rule, its affected-row count, and any unresolved records.
Do not report a “clean” percentage or an attrition rate unless you calculate it from the exact file and state the denominator and handling of missing values. A reproducible result also needs the source file, PostgreSQL version, SQL used, and any assumptions about the fields.
Use synthetic HR data for SQL practice, not population claims
The IBM listing identifies its dataset as fictional. It can serve as an example for practicing SQL cleaning and exploratory analysis, but that provenance does not establish that its records represent a real workforce or a defined population. The listing suggests analyses such as grouping distance from home by job role and attrition, or comparing average monthly income by education and attrition. Interpret those as exercises on the listed data, not evidence about employees generally.
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.
