Skip to main content
 首页 » 编程设计

Python之以特定方式将制表符分隔文件转换为csv

2024年10月25日13freeliver54

我有一个制表符分隔的文本文件,每条记录有 10 列,如下所示:

p001    64      20141209      meals (attendees)    ML   ENTER   Entertainment   xyz Restaurants      6.0     "_e' Restaurants (123) 456-7890 \r\n          FORUM \r\n     ,Around \r\n\r\n':33 113-2 \r\n\r\n 8440 XYZ09'15       1:11PM \r\n\r\n   1 Burger          6.00 \r\n\r\n    SSIONS      6.00 \r\n    TOTAL PAID    6 .00 \r\n    XXXXXXXXXXX2012 XX/XX \r\n    XYZ EXPRESS 
6.00 \r\n\r\n\r\n  7,-10( YOU! FOR DINING WITH US! \r\n\r\n         113-2 \r\n\r\nYour r is: 840     \r\n" 

P.S:最后一列的文本用“”括起来。我的第一列不是唯一的。

我想将此文本文件转换为 csv 文件,以便我只从记录的第 1、2、8、9 和 10 列中选取数据。另外,所有的数据都要用“.”括起来。

例如,上面的记录应该在输出的csv文件中转换成下面一行:

"p001","64","xyz Restaurants","6.0","_e' Restaurants (123) 456-7890 \r\n          FORUM \r\n     ,Around \r\n\r\n':33 113-2 \r\n\r\n 8440 XYZ09'15       1:11PM \r\n\r\n   1 Burger          6.00 \r\n\r\n    SSIONS      6.00 \r\n    TOTAL PAID    6 .00 \r\n    XXXXXXXXXXX2012 XX/XX \r\n    XYZ EXPRESS 
    6.00 \r\n\r\n\r\n  7,-10( YOU! FOR DINING WITH US! \r\n\r\n         113-2 \r\n\r\nYour r is: 840     \r\n" 

请您参考如下方法:

这应该适合您。请注意,这使用了 csv输入和输出库,我们只需更改文本分隔符。 CSV写入文件时应该自动转义引号字符。

import csv 
try: 
    with open(r'input.tsv', 'r', newline='\n') as in_f, \ 
         open(r'output.csv', 'w', newline='\n') as out_f: 
        reader = csv.reader(in_f, delimiter='\t') 
        writer = csv.writer(out_f, delimiter=',', quoting=csv.QUOTE_ALL)  # Quoting added per comment from @Rob. 
        for li in reader: 
            try: 
                writer.writerow([li[0], li[1], li[2], li[7], li[8], li[9],]) 
            except IndexError:  # Prevent errors on blank lines. 
                pass 
except IOError as err: 
    print(err) 

我无法解析制表符在您的示例数据中的位置(而不是空格),但使用以下 input.tsv 数据对其进行了测试:

1   2   3   4   5   6   7   8   9   10 
11  12  13  14  15  16  17  18  19  20 
21  22  23  24  25  26  27  28  29  30 

将在output.csv中生成如下结果:

"1","2","3","8","9","10" 
"11","12","13","18","19","20" 
"21","22","23","28","29","30" 

更新

请注意,根据 Rob 评论中的建议,添加 quoting=csv.QUOTE_ALL 的代码更新。感谢捕获!