CSV data preparation: inspect, clean, sort and split

Prepare a CSV by inspecting its columns before changing the data. Confirm the delimiter, header, row count, identifiers, and numeric fields. Keep an original copy, apply the smallest required transformation, and compare the result before importing it.

What should a CSV column profile tell you?

A useful first pass counts data rows, empty fields, distinct non-empty values, and recognized numeric cells. The CSV column profiler also shows minimum, maximum, and mean for the recognized numbers in each column.

For a column containing 10, 20, an empty cell, and 10, the numeric count is 3, the distinct non-empty count is 2, and the mean is 40/3, approximately 13.33. The empty cell is excluded from the mean. That is different from replacing the empty cell with zero, which would produce 10.

Leading-zero identifiers such as 00123 are treated as text. A column full of identifiers does not need a numeric mean. The profiler recognizes decimal-point numbers without currency symbols or thousands separators; review its counts before assuming every numeric-looking field was included.

When should you trim spaces or remove duplicates?

Use the CSV cleaner for unintended surrounding whitespace. It leaves internal spaces and letter case intact. If spaces at the edges are meaningful, disable trimming. Microsoft’s data cleanup guide discusses why text and number cleanup require different checks.

The duplicate row remover compares complete records and keeps the first occurrence. Two rows with the same customer ID but different addresses are distinct rows. Resolve duplicate keys according to the source system’s rules; removing full duplicates does not decide which customer record is authoritative.

Cleanup order matters. Ada and Ada become the same field only after trimming. Record which transformations you applied so someone can reproduce the result.

How should you sort numeric values and identifiers?

Text sorting and numeric sorting answer different questions. Numeric order places 2 before 10; identifier order preserves the source strings. The CSV sorter requires a column number and comparison mode, retains the header, preserves tied-row order, and leaves empty values last.

Numeric mode rejects a column containing leading-zero IDs or nonnumeric text. Check the preview instead of coercing a code into a number to make sorting succeed.

How do you split a CSV without breaking quoted records?

A quoted field can contain a line break. Splitting a text file on physical lines can therefore cut one record in half. The CSV splitter parses records first, repeats a selected header in each part, and downloads a ZIP of the resulting CSV files.

If five records are split into parts of two, the parts contain 2, 2, and 1 data records. Headers are additional rows. After splitting, add the data-row counts across all parts and compare that sum with the original count.

What should you check after CSV preparation?

Compare column names, data-row counts, empty-field counts, and known sample records. Expect row counts to change only when removing rows or splitting into parts. Reopen a result in the actual receiving application and confirm identifiers, accented text, and dates.

All tools described here process files in the browser. That keeps the file on the device; it does not remove the need to verify the receiving application’s type inference and import settings.