📄 Format & Conversion · 7 min read

Convert Text to CSV: Tabs, Spaces and Fixed-Width Files

A text file becomes a CSV the moment you decide what character separates the fields. If the file already uses commas between values, renaming it to .csv is genuinely all that is required. If it uses tabs or runs of spaces, you have to tell the converter which character to split on, and if the columns are aligned by position instead of by a character, you need a fixed-width tool because no delimiter setting will work. The three cases look identical in a text editor, which is why the same file converts cleanly in one tool and collapses into a single column in another.

The question that brings most people here is a version of this one, from r/excel: “How to convert text file with various white space to csv?” Uneven whitespace is the difficult half of this problem, and the reason is structural: in a space-separated file the separator and the data look the same. A single space between two words is a value; a single space between two columns is a delimiter.

Why renaming .txt to .csv sometimes works

RFC 4180, the informal spec that most software follows, defines a CSV as comma-separated fields where any field may be wrapped in double quotes, and a quoted field may legally contain the delimiter, a double quote, or a line break. Nothing in that definition mentions the file extension. So a .txt file whose lines already read name,email,country is a CSV that happens to be named .txt, and changing the extension is a correct conversion.

The same logic runs in reverse and causes the confusion. Rename a tab-separated file to .csv and the extension now promises commas that are not there. Excel, pandas and most importers will read each line as a single field. If your file is tab-separated, the honest label is TSV, and there is a separate walkthrough for converting TSV to CSV.

Which of the three problems do you actually have?

The third row is the one that catches people. Open the file in a text editor with the ruler on and compare the second line of data with the header. If the values start at the same character positions as the header words, the file is fixed-width and aligned for human eyes. If the gaps between values vary in length as you scroll down, the file is space-delimited and the multiple spaces are just padding.

Method 1: Excel, for files under a million rows

Do not double-click the file. That path assumes a comma and hides the settings you need. Use Data > From Text/CSV instead, which opens a preview and lets you set three things before anything is written into the grid: the delimiter, the file encoding, and whether to treat consecutive delimiters as one.

That last checkbox is the fix for uneven spaces. With it ticked, three spaces count as a single separator, which is usually what a padded report needs. Untick it and Excel creates an empty column for every extra space, which is the most common way a converted file ends up with 40 columns instead of 6.

Two limits bracket this method. Excel tops out at 1,048,576 rows and 16,384 columns per sheet, and a file that exceeds either is truncated on import, sometimes with a warning and sometimes without. The character limit on a single cell is 32,767, which matters for text exports where one field holds a paragraph or a JSON blob.

Text to Columns, if the data is already in Excel

When the text is already sitting in column A, Data > Text to Columns does the same job in two clicks. It splits one column by one delimiter set at a time, so a file that has both a comma and a pipe needs two passes. It also cannot read a fixed-width file from inside the grid, because the positions were lost when the text was imported.

Method 2: Python, when the whitespace is inconsistent

Python is the tool for messy spacing, because its standard library will guess and its data library will accept a pattern. The sniffer in the standard library reads a sample of the file and reports the delimiter and the quoting character it found:

import csv, io

sample = open("export.txt", encoding="utf-8").read(4096)
dialect = csv.Sniffer().sniff(sample)
print(dialect.delimiter, dialect.quotechar)

# then parse with that dialect instead of a guess
with open("export.txt", encoding="utf-8", newline="") as f:
    rows = list(csv.reader(f, dialect))

When the gaps vary in width, a regular expression handles it. pandas accepts a separator, and any separator longer than one character is treated as a regular expression, so \s+ means one or more whitespace characters:

import pandas as pd

# one or more spaces or tabs between fields
df = pd.read_csv("export.txt", sep=r"\s+", engine="python")

# fixed-width instead: give the column boundaries
widths = [10, 24, 8, 12]
df = pd.read_fwf("report.txt", widths=widths)

# write back with a BOM so Excel picks up UTF-8
df.to_csv("clean.csv", index=False, encoding="utf-8-sig")

The utf-8-sig detail is not cosmetic. Excel on Windows does not reliably detect UTF-8 without a byte order mark, so accented names and currency symbols from a UTF-8 export arrive as mojibake. There is a separate guide to fixing garbled characters in Excel if the file is already past that point.

Method 3: a one-line terminal command

For a tab-separated file, awk splits on runs of spaces and tabs by default, which makes it a converter with no setup at all:

# tabs to commas
awk -F'	' 'BEGIN{OFS=","} {$1=$1; print}' export.txt > export.csv

The caveat matters more than the command. A field-by-field replacement such as sed 's/ /,/g' does not know what a quoted field is, so a value like "Smith, John" becomes two fields and the whole row shifts. This is the exact failure the quoting rules in RFC 4180 exist to prevent, and it is the reason a parser beats a find-and-replace whenever the data contains addresses, names or notes. If your file has that kind of content, use a parser and check the result against a row count of the original.

Method 4: a browser converter, when you cannot install anything

Online converters are the fastest route on a locked-down machine, and the slowest to trust. Any of them will turn a .txt into a .csv in a few seconds, but the file leaves your computer, so customer lists, payroll exports and anything covered by an NDA belong in a local tool. If the file is a public dataset or a sample, an online converter is fine.

Which method to use

One habit makes all of this cheaper. Convert, then check the result against the source before you delete the original: same row count, same number of columns in every row, and a spot check on the field that contains a comma or an accent. A conversion that silently drops or shifts rows is worse than no conversion, because the file looks fine until somebody sums a column. If the source file is messy beyond delimiters, the notes in cleaning dirty CSV data cover the checks worth running afterwards, and if the delimiter itself is the thing being changed, switching a CSV delimiter is the shorter version of this problem.

Frequently asked questions

Can I just rename a .txt file to .csv?

Only when the file already uses commas between fields and quotes any field containing a comma, a quote or a line break. The extension is a label, not a conversion, so renaming a tab-separated file gives you a file that still contains tabs.

How do I convert a text file with uneven spaces to CSV?

In Excel, tick Treat consecutive delimiters as one. In Python, read it with pandas and a \s+ separator. The awkward case is a value that contains a single space, because then the space is data and not a separator.

How do I convert a fixed-width text file to CSV?

There is no delimiter to set, so use column positions: insert break lines at each boundary in the Excel Text Import preview, or use pandas read_fwf with the widths of each column taken from the header.

Why does Excel put all my text data into one column?

Because it assumed a comma and the file does not use one, or because the encoding or the column count confused it. Import through Data > From Text/CSV so you can set the delimiter and the encoding before the data reaches the grid.

How do I keep leading zeros when converting text to CSV?

Read the column as text, not as a number. Import it as Text in Excel or with a string dtype in Python, and quote the values when writing the file so the next program does not strip the zero instead. See keeping leading zeros in CSV for the full set of options.

What delimiter should I choose?

Comma, because that is what RFC 4180 and most import tools expect. Semicolon is the regional exception where the comma is used as a decimal mark. A tab-separated file is a TSV, which is fine as long as everyone downstream knows it.

Is there a free converter that does not upload my file?

Yes. Excel, LibreOffice Calc, Python and awk all run locally and send nothing anywhere. Browser converters are convenient but the file leaves your machine, so keep confidential data on your own computer.