技术咨询：如何去除空白字符且保留数据列结构？

Looks like you've got messy, inconsistent whitespace in your tabular data—no worries, there are several straightforward ways to clean this up while keeping your column structure intact. Below are some common methods depending on the tools you prefer:

Using Awk (Command Line) #

This is my go-to for quick tabular data cleanup. Awk automatically splits lines into fields using any whitespace as a separator, so we can just rebuild the line with single spaces between fields:

awk '$1=$1' input.txt > cleaned_output.txt

How this works: Assigning $1=$1 forces Awk to reprocess the entire line, replacing all sequences of whitespace with the default Output Field Separator (OFS), which is a single space. It also trims leading and trailing whitespace automatically.

Using Sed (Command Line) #

If you prefer regex-based manipulation, Sed can replace multiple spaces with a single one, plus trim leading/trailing spaces:

sed -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//' -e 's/[[:space:]]\+/ /g' input.txt > cleaned_output.txt

Breakdown of the commands:

• s/^[[:space:]]*//: Removes leading whitespace
• s/[[:space:]]*$//: Removes trailing whitespace
• s/[[:space:]]\+/ /g: Replaces any sequence of one or more whitespace characters with a single space

Using Python Script #

If you want a more flexible approach (or need to integrate this into a larger workflow), a simple Python script works great:

# Read input file and write cleaned output
with open("input.txt", "r") as infile, open("cleaned_output.txt", "w") as outfile:
    for line in infile:
        # Strip leading/trailing whitespace, split on any whitespace, then join with single spaces
        cleaned_line = " ".join(line.strip().split())
        outfile.write(cleaned_line + "\n")

This script processes each line by splitting it into a list of columns (ignoring any amount of whitespace between them), then joins those columns back together with single spaces.

Using Spreadsheets (Excel/Google Sheets) #

If you're working with the data in a spreadsheet:

• Google Sheets: Use this formula in a new column (replace A1 with your cell containing the messy line):

  =TEXTJOIN(" ", TRUE, SPLIT(A1, " "))
  

  SPLIT breaks the line into parts using spaces, TEXTJOIN combines them back with single spaces, and the TRUE ignores empty values from extra spaces.

• Excel 365/2021: Use TEXTSPLIT and TEXTJOIN:

  =TEXTJOIN(" ", TRUE, TEXTSPLIT(A1, " ", , TRUE))
  

  The final TRUE in TEXTSPLIT skips empty cells created by extra spaces.

All these methods will turn your inconsistent whitespace-separated data into clean, uniformly spaced columns without losing any of your original column values.

内容的提问来源于stack exchange，提问作者CaroV1x3n