Pad a CSV Column

Padding puts every value in a column at the same length by adding a fill character until it gets there, which is how 123 becomes 000123 again after a spreadsheet ate the zeros. Pick the column, the width and the character; blanks stay blank and long values stay whole. It runs in your browser, so nothing is uploaded.

Not sure how wide the ids used to be? The profiler shows the length spread.

How 00123 turns into 123

The sequence is always the same. A source system stores an employee number, a store code or an invoice reference as a fixed-width string: 00123, five characters, and the zeros carry meaning because the format is part of the contract. Somebody exports it to CSV, which has no way of saying "this is text". Somebody else double-clicks the CSV, Excel looks at 00123, decides it is the number one hundred and twenty three, and displays 123. The moment that file is saved, the zeros are gone from the bytes as well as from the screen.

Nothing about that is loud. There is no warning, no error, no red cell. The failure arrives later, when the file is loaded back into the system it came from and every lookup misses, or when a reconciliation between two extracts reports thousands of unmatched rows because one side kept its zeros and the other did not.

The repair is mechanical and this page does it. The important caveat is up front: padding works when the original width was constant. If some codes were four characters and some were six, the file no longer records which was which, and no tool can invent the answer. Pad only when you know the width.

Worked example: four ids, four outcomes

A deliberately awkward input, with a short id, a medium one, a missing one and an over-long one:

id,name
7,Ada
123,Grace
,Katherine
1234567,Alan

Tick id, leave Width at 6, Pad with at 0, Pad on the at Left, and both Longer values and Blank cells on their defaults:

id,name
000007,Ada
000123,Grace
,Katherine
1234567,Alan

With this strip above it:

4 rows · padded id · to 6 characters · with "0" on the left
original replaced · 2 cells changed · 1 blank left blank

Two cells changed, not four, and the count tells you so before you download. Katherine's missing id is still missing rather than being turned into 000000, which would have been a key that looks real and matches nothing. Alan's seven-character id is untouched, so the column now holds two widths and you can see that it does. Both of those are choices you can reverse, and both defaults exist because the reverse is worse when you were not paying attention.

Switch Longer values to Cut them and Alan's id becomes 234567, with a warning saying one value was cut and suggesting you switch it back. Note which end went: the leading 1 was dropped and the tail was kept.

Which end gets cut, and why

Truncation has to pick an end, and picking the wrong one destroys the value rather than shortening it. The rule here follows the padding direction, because the padding direction already says where the meaningless part of the string lives.

Pad on the Left,  width 6, cut on:   1234567  →  234567
Pad on the Right, width 6, cut on:   1234567  →  123456

Left padding means the front of the value is filler, so the back is what identifies the row and the front is what gets sacrificed. Right padding means the opposite: the value starts at the left, the tail is filler, and the tail is what goes. In practice this matters most for numeric identifiers, where the last digits vary and the first ones are often a fixed prefix, and it matters less for text codes, where you should probably not be truncating at all.

A value already at exactly the width is returned untouched whether truncation is on or off, so switching the option on does not disturb the rows that were already correct. The count of truncated values is reported separately from the count of padded ones, which makes it easy to notice that a width you thought was right is cutting a hundred rows.

Padding for sorting, and padding for layout

There are two different reasons to reach for this page, and they want opposite settings.

Sorting wants zeros on the left. A CSV column is text, so a text sort compares character by character and puts 10 before 9, which then puts invoice 100 between invoice 1 and invoice 2 in every report built on the file. Pad the column to a common width and the comparison starts working: 009 genuinely does come before 010. This is also the fix for a sequence column that sorts wrongly in a spreadsheet after it has been formatted as text.

Fixed-width layout wants spaces on the right. Plenty of systems still exchange data as columns of characters at known offsets: banking file formats, EDI-adjacent feeds, mainframe extracts, and the print files that produce statements. Set Pad with to a space and Pad on the to Right, pad each field to its declared length, and the CSV becomes something a fixed-width reader can consume once the delimiters are dropped.

The fill character is a single character by design. Typing more than one is not an error, but only the first is used, because a multi-character filler makes the final width unpredictable whenever the gap is not an exact multiple of the filler's length.

Practical notes

Width counts characters, not bytes. An accented letter or an emoji counts as one, which is what a person means by width and what a fixed-width text format usually means too. A UTF-8 byte count would give a different answer.
Several columns can be padded together. Ticking three columns pads all of them to the same width with the same character. For different widths, run the tool again on the result.
Keeping the original is one switch. Set Original column to Keep, add a new one and an id_padded column appears beside id, which is useful when the unpadded version is still a valid key somewhere else.
Zeros added here stay added. Everything is a string from read to write, so the padded value is written to the CSV exactly as it appears in the preview. What happens next depends on the program that opens it, not on this page.
The width is clamped to a sane range. Anything between 1 and 200 is accepted; typing something that is not a number falls back rather than producing an empty file.
Your source file is never overwritten. A file called contacts.csv downloads as contacts-padded.csv.

Frequently Asked Questions

Excel removed the leading zeros from my ids. Can I get them back?

If every id was the same width to begin with, yes, and that is what this page is for. Set Width to that number and the zeros come back on every row. If the ids were different widths, the information is gone and no tool can recover it, because 123 could have been 0123 or 00123 and the file no longer says which.

Will the zeros survive being opened in Excel again?

Not by themselves. A CSV holds no type information, so Excel guesses again on the next open and strips them again. Import the file through Data then From Text/CSV and set the column to Text, or keep the file out of a spreadsheet entirely and load it straight into whatever needs it.

What happens to a value that is already longer than the width?

It is left exactly as it was. Longer values is set to Leave them alone, because a column with two widths in it is a smaller problem than an identifier that has been silently shortened. Switch it to Cut them only when you are certain the extra characters are noise, and read the warning that appears.

When a long value is cut, which end is kept?

The end that matters for the padding direction. With Pad on the left, the last characters are kept, because that is the significant part of a zero-padded identifier: 1234567 cut to six becomes 234567. With Pad on the right, the first characters are kept instead.

Are empty cells padded too?

Not by default. Blank cells is set to Leave blank, because turning a missing id into 000000 invents a key that looks real and joins to nothing. Switch it to Pad them too if the column is a fixed-width code where a row of fill characters is the correct representation of nothing, and a warning will remind you what you just did.

Can I pad with something other than a zero?

Yes. Pad with takes any single character. A space on the right gives you the fixed-width text layout that mainframe and COBOL feeds expect, a dot makes an alignment guide readable in a terminal, and an asterisk is a common filler in banking formats. Only the first character you type is used.

Does padding help with sorting?

Yes, when the sort is a text sort, which is what a CSV usually gets. Unpadded, 10 sorts before 9 because the comparison is character by character. Padded to the same width, 009 sorts before 010 and the order is finally the one you expected.

Is the file uploaded anywhere?

No. The padding happens in JavaScript in your own tab, with no server involved and nothing stored between visits. Cells are treated as strings throughout, which is the whole reason the zeros stay put once they are added.

Put the zeros back

Free, no account, no upload. Pick a width, check the counts, take the CSV.

Back to the padder