英文:
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 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()
# 处理标题
buffer = ''
i = 1 # 跳过查询行
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')
# 跳过分隔线
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 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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论