如何基于各列最大长度对齐TXT文件不同长度的列？含SQLite场景

Got it, let's solve this column alignment problem for your 18-column TXT file filled with SQLite Insert values. The trick is to properly parse columns that contain commas or escaped single quotes, calculate each column's maximum width, then reformat every line to match those widths. Here's a practical Python solution you can use right away:

Step-by-Step Solution #

1. Parse Columns Correctly #

The main challenge is splitting columns without breaking entries that have commas inside single quotes (like 'Pagrati/Athens,Attica,Greece') or escaped single quotes (like Vampire''s Crypt). We'll build a custom parser to handle these edge cases.

2. Calculate Maximum Column Lengths #

Once all columns are extracted, we'll compute the longest string in each column to know exactly how much padding each entry needs for alignment.

3. Reformat and Align Rows #

We'll left-align (adjustable) each column to its maximum length, then rebuild each line to match the original (col1, col2, ...) format you have.

Full Python Code #

def align_sqlite_insert_columns(input_file, output_file):
    # Read and parse all rows first
    parsed_rows = []
    with open(input_file, 'r', encoding='utf-8') as f:
        for line_num, line in enumerate(f, 1):
            # Clean up the line: remove leading/trailing parentheses, commas, and whitespace
            cleaned_line = line.strip().strip('(),')
            if not cleaned_line:
                continue
            
            # Custom column splitting to handle commas inside quotes and escaped single quotes
            current_column = []
            in_quoted_field = False
            columns = []
            
            for char in cleaned_line:
                if char == "'":
                    # Handle escaped single quotes (two consecutive ')
                    if current_column and current_column[-1] == "'":
                        current_column.append(char)
                        current_column.pop(-2)  # Merge two '' into a single '
                    else:
                        in_quoted_field = not in_quoted_field
                        current_column.append(char)
                elif char == ',' and not in_quoted_field:
                    # End of current column when comma is outside quotes
                    columns.append(''.join(current_column).strip())
                    current_column = []
                else:
                    current_column.append(char)
            
            # Add the last column
            if current_column:
                columns.append(''.join(current_column).strip())
            
            # Verify column count (optional but helpful for debugging)
            if len(columns) != 18:
                print(f"Warning: Line {line_num} has {len(columns)} columns instead of 18. Skipping.")
                continue
            
            parsed_rows.append(columns)
    
    # Calculate maximum length for each column
    max_col_lengths = []
    for col_idx in range(18):
        # Get the length of every entry in this column
        col_lengths = [len(row[col_idx]) for row in parsed_rows]
        max_col_lengths.append(max(col_lengths))
    
    # Generate aligned lines
    aligned_lines = []
    for row in parsed_rows:
        aligned_columns = []
        for idx, col in enumerate(row):
            # Left-align each column to its max length; change '<' to '>' for right-align or '^' for center
            aligned_col = f"{col:<{max_col_lengths[idx]}}"
            aligned_columns.append(aligned_col)
        # Rebuild the line in original format
        aligned_line = f"({', '.join(aligned_columns)}),"
        aligned_lines.append(aligned_line)
    
    # Write the aligned content to output file
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write('\n'.join(aligned_lines))

# Example usage
align_sqlite_insert_columns('your_input.txt', 'aligned_output.txt')

Key Notes #

• Special Case Handling: The custom parser correctly deals with commas inside quoted columns and escaped single quotes, which a standard split(',') would break.
• Alignment Control: Change the < in f"{col:<{max_col_lengths[idx]}}" to > for right alignment or ^ for center alignment if that fits your needs better.
• Encoding: Uses UTF-8 to support non-ASCII characters like Greek and Russian from your example data.
• Error Checking: Includes a warning for lines that don't have exactly 18 columns, so you can fix any malformed entries before alignment.

How to Use #

1. Save the code as align_columns.py
2. Replace 'your_input.txt' with your actual input file path
3. Replace 'aligned_output.txt' with your desired output file path
4. Run the script with Python 3.x

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