How can I reformat already acquired psql query output?

Question

I have default psql query output in .txt file, which is very difficult to read. Any command or service to change it into a more convenient format to work with (e.g. CSV)?

Went through docs, but didn't find the solution. By any chance, can I somehow open it with psql in interactive mode, as if I queried it myself?

Answer 1

I have written python script for that, haven't find no better solution.

import sys

def convert_to_csv(input_file, output_file):
    with open(input_file, 'r') as inp, open(output_file, 'w') as outp:
        lines = inp.readlines()
        
        # Process header
        buffer = ''
        i = 1  # Skip query line
        while not lines[i].startswith('---'):
            buffer += lines[i].rstrip()
            i += 1

        header = buffer.split('|')
        header = [h.strip() for h in header]
        num_of_columns = len(header)
        outp.write(','.join(header) + '\n')
        
        # Skip separator line(s)
        while lines[i].startswith('---'):
            i += 1

        # Process data rows
        buffer = ''
        for line in lines[i:-1]:  # Skip last line (row count)
            buffer += line.rstrip()
            if buffer.count('|') == num_of_columns - 1:
                row = buffer.split('|')
                row = [f'"{value.strip()}"' if ',' in value.strip() else value.strip() for value in row]
                outp.write(','.join(row) + '\n')
                buffer = ''
        if buffer:  # Ensure last bit of data is written
            row = buffer.split('|')
            row = [f'"{value.strip()}"' if ',' in value.strip() else value.strip() for value in row]
            outp.write(','.join(row) + '\n')

convert_to_csv(sys.argv[1], sys.argv[2])

Run it in terminal python3 psql_to_csv.py exp1.txt outp1.csv. Input file should start with query command like db_name=# select * ... and end with (N rows)