Transpose a CSV file

Some exports arrive the wrong way round. Each metric is a row and each period is a column, when the tool you are feeding wants each period to be a row. Or a report is laid out for reading, wide and short, and the chart needs it tall and narrow.

Transposing is the fix, and it is one of those operations that is trivial to describe and tedious to do by hand the moment the file is larger than a screen.

What transposing actually does

The first row becomes the first column. The second row becomes the second column. A file of 3 rows and 40 columns becomes a file of 40 rows and 3 columns, and every value keeps its neighbours: whatever was in row 2 column 7 is now in row 7 column 2.

The header row is not treated as a special case. It becomes the first column along with everything else, which is what the word means and what makes the operation reversible. Transposing a file twice returns exactly the file you started with, and a tool that quietly held the header aside would break that.

That reversibility is worth more than it sounds. It means you can transpose an export, look at it, decide it was the wrong idea, and transpose it back without having lost anything to a helpful interpretation of your first row.

Rows of different lengths

Real exports are not always rectangular. A row can be short because a trailing empty field was dropped by whatever wrote the file, and that is legal enough that plenty of readers accept it.

It cannot survive a transpose, because a short row becomes a column with a hole in the middle of the file rather than at the end of a line. So every row is padded to the width of the widest before flipping, and the result is always rectangular. Nothing is discarded and nothing is invented beyond the empty cells that were already implied.

The one thing to watch

A transpose swaps the two dimensions, and spreadsheets are not symmetric about them. Most will open a file with a million rows and stop at a few thousand columns, so transposing a long export produces something technically correct that your spreadsheet cannot display.

If the file is tall, look at how tall before flipping it. Ten thousand rows is a perfectly ordinary export and ten thousand columns is not a thing most tools will show you. Where the goal is a summary rather than a genuine flip, grouping the data is usually the operation actually wanted, and querying it with SQL will do that in one step.

Everything happens in this browser tab. The file is read, flipped and offered back as a download, and it is never sent anywhere.