英文:
How can I reformat already acquired psql query output?
问题
我有默认的psql查询输出保存在.txt文件中，很难阅读。有没有任何命令或服务可以将其转换成更方便处理的格式，比如CSV？
查看了文档，但没有找到解决方案。有没有可能以交互模式在psql中打开它，就像我自己查询了一样？
英文:
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?
答案1
得分: 1
我已经为此编写了Python脚本，没有找到更好的解决方案。
import sysdef convert_to_csv(input_file, output_file):with open(input_file, 'r') as inp, open(output_file, 'w') as outp:lines = inp.readlines()# 处理标题buffer = ''i = 1 # 跳过查询行while not lines[i].startswith('---'):buffer += lines[i].rstrip()i += 1header = buffer.split('|')header = [h.strip() for h in header]num_of_columns = len(header)outp.write(','.join(header) + '\n')# 跳过分隔线while lines[i].startswith('---'):i += 1# 处理数据行buffer = ''for line in lines[i:-1]: # 跳过最后一行（行数）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: # 确保最后一部分数据被写入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])
在终端中运行它：python3 psql_to_csv.py exp1.txt outp1.csv。输入文件应该以查询命令开头，如db_name=# select * ...，并以(N行)结尾。
英文:
I have written python script for that, haven't find no better solution.
import sysdef convert_to_csv(input_file, output_file):with open(input_file, 'r') as inp, open(output_file, 'w') as outp:lines = inp.readlines()# Process headerbuffer = ''i = 1 # Skip query linewhile not lines[i].startswith('---'):buffer += lines[i].rstrip()i += 1header = 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 rowsbuffer = ''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 writtenrow = 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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论