我的 table :
Table To Be Searched MSEG
Number of hits 273208
Maximum No. of Entri 0
Runtime 00:24:17
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|Mat. Doc. |MatYr|MvT|Material |Plnt|SLoc|Batch |Customer| Amount in LC| Amount| Quantity|BUn| Qty in UnE|EUn|PO |MatYr|Mat. Doc. |Order |Profit Ctr|SLED/BBD |Pstng Date|Entry Date|Time |User name |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|4912693062|2015 |551|100062 |HDC2|0001|5G30MC1A11| | 9.03 | 9.06 | 0.083 |CS | 2 |EA | | | | |IN1165B085|26.01.2016|01.08.2015|01.08.2015|01:13:16|O33462 |
|4912693063|2015 |501|166 |HDC2|0004| | | 0.00 | 0.00 | 2 |EA | 2 |EA | | | | |IN1165B085| |01.08.2015|01.08.2015|01:13:17|O33462 |
|4912693320|2015 |551|101343 |HDC2|0001|5G28MC1A11| | 53.73 | 53.72 | 0.500 |CS | 12 |EA | | | | |IN1165B085|25.01.2016|01.08.2015|01.08.2015|01:16:30|O33462 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table To Be Searched MSEG
Number of hits 273208
Maximum No. of Entri 0
Runtime 00:24:17
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|Mat. Doc. |MatYr|MvT|Material |Plnt|SLoc|Batch |Customer| Amount in LC| Amount| Quantity|BUn| Qty in UnE|EUn|PO |MatYr|Mat. Doc. |Order |Profit Ctr|SLED/BBD |Pstng Date|Entry Date|Time |User name |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|4912696602|2015 |344|100399 |HMH3|0001|5G29MH3S11| | 0.00 | 0.00 | 9,061 |CS | 9,061 |CS | | | | |IN1165B074|26.01.2016|01.08.2015|01.08.2015|01:54:15|A70475 |
它包含超过 100 万行。以前,我首先使用以下 Python 代码将此文件转换为 CSV 格式:
def hasNumbers(inputString):
return any(char.isdigit() for char in inputString)
with open("Input.txt") as infile:
for line in infile:
with open("Out.csv", "a") as myfile:
if line[:1] == '|':
if hasNumbers(line) == True:
newline = line[1:-2].replace(' ','')
newline.translate(None, ",!.;")
myfile.write(newline+'\n')
是否有更好的方法将这种文件转换为 CSV 格式,或者我应该如何将其直接解析为 pandas?
请您参考如下方法:
你可以使用 itertools.ifilter仅过滤表数据和一个 csv.reader 来解析重要的行,如下所示:
import csv
import itertools
import StringIO
with open('input.txt', 'rb') as f_input:
for line in itertools.ifilter(lambda x: len(x) > 2 and x[0] == '|' and x[1].isalpha(), f_input):
header = [cols.strip() for cols in next(csv.reader(StringIO.StringIO(line), delimiter='|', skipinitialspace=True))][1:-1]
break
with open('input.txt', 'rb') as f_input, open('output.csv', 'wb') as f_output:
csv_output = csv.writer(f_output)
csv_output.writerow(header)
for line in itertools.ifilter(lambda x: len(x) > 2 and x[0] == '|' and x[1] != '-' and not x[1].isalpha(), f_input):
csv_input = csv.reader(StringIO.StringIO(line), delimiter='|', skipinitialspace=True)
csv_output.writerow([col.strip().translate(None, ",!.;") for col in next(csv_input)[1:-1]])
这将为您提供如下输出 csv 文件:
Mat. Doc.,MatYr,MvT,Material,Plnt,SLoc,Batch,Customer,Amount in LC,Amount,Quantity,BUn,Qty in UnE,EUn,PO,MatYr,Mat. Doc.,Order,Profit Ctr,SLED/BBD,Pstng Date,Entry Date,Time,User name
4912693062,2015,551,100062,HDC2,0001,5G30MC1A11,,903,906,0083,CS,2,EA,,,,,IN1165B085,26012016,01082015,01082015,01:13:16,O33462
4912693063,2015,501,166,HDC2,0004,,,000,000,2,EA,2,EA,,,,,IN1165B085,,01082015,01082015,01:13:17,O33462
4912693320,2015,551,101343,HDC2,0001,5G28MC1A11,,5373,5372,0500,CS,12,EA,,,,,IN1165B085,25012016,01082015,01082015,01:16:30,O33462
4912696602,2015,344,100399,HMH3,0001,5G29MH3S11,,000,000,9061,CS,9061,CS,,,,,IN1165B074,26012016,01082015,01082015,01:54:15,A70475
