我有一个文本格式的大型数据文件,想要通过指定每列的长度将其转换为CSV格式。
列数 = 5
每列的长度:
[4 2 5 1 1]
样本观测:
aasdfh9013512
ajshdj 2445df
预期输出
aasd,fh,90135,1,2
ajsh,dj, 2445,d,f
我有一个文本格式的大型数据文件,想要通过指定每列的长度将其转换为CSV格式。
列数 = 5
每列的长度:
[4 2 5 1 1]
样本观测:
aasdfh9013512
ajshdj 2445df
预期输出
aasd,fh,90135,1,2
ajsh,dj, 2445,d,f
GNU awk(gawk)直接支持FIELDWIDTHS
,例如:
gawk '$1=$1' FIELDWIDTHS='4 2 5 1 1' OFS=, infile
输出:
aasd,fh,90135,1,2
ajsh,dj, 2445,d,f
sed
命令并匹配给定长度的组:$ sed -r 's/^(.{4})(.{2})(.{5})(.{1})(.{1})$/\1,\2,\3,\4,\5/' file
aasd,fh,90135,1,2
ajsh,dj, 2445,d,f
awk
的答案。 - fedorqui这里有一个解决方案可以与普通的 awk
一起使用(不需要 gawk
)。
awk -v OFS=',' '{print substr($0,1,4), substr($0,5,2), substr($0,7,5), substr($0,12,1), substr($0,13,1)}'
substr
函数来定义每个字段的起始位置和长度。OFS
定义输出字段分隔符是什么(在这种情况下,是逗号)。echo 'aasdfh9013512
ajshdj 2445df' |
awk -v OFS=',' '{print substr($0,1,4), substr($0,5,2), substr($0,7,5), substr($0,12,1), substr($0,13,1)}'
输出:
aasd,fh,90135,1,2
ajsh,dj, 2445,d,f
awk
中,不需要硬编码子字符串位置,只需根据用户提供的位置数插入逗号即可。以下是实现该功能的写法,经过GNU awk
测试。要使用此功能,我们必须定义值(如样例所示),在需要插入逗号的位置上给出位置编号,awk
变量名为colLength
,并在它们之间留有空格。awk -v colLengh="4 2 5 1 1" '
BEGIN{
num=split(colLengh,arr,OFS)
}
{
j=sum=0
while(++j<=num){
if(length($0)>sum){
sub("^.{"arr[j]+sum"}","&,")
}
sum+=arr[j]+1
}
}
1
' Input_file
说明:简单来说,我们需要创建一个名为colLengh
的awk
变量,在其中定义需要在哪些位置插入逗号。然后在BEGIN
部分创建一个名为arr
的数组,其中包含需要在其中插入逗号的索引值。
在主程序部分,首先将变量j
和sum
归零。然后从j=1开始运行一个while
循环,直到j的值等于num为止。在每次运行中,根据需要从当前行的开头(如果当前行的长度大于sum,则执行替换操作,否则不执行)进行替换。例如:sub
函数在第一次循环运行时将成为.{4}
,然后它变成了.{7}
,因为我们需要在第7个位置插入逗号,以此类推。因此,sub
将用匹配值+,
替换从起始位置到生成的数字之间的相应数量的字符。最后在该程序中提到1
将打印编辑/未编辑的行。
https://github.com/just10minutes/FixedWidthToDelimited/blob/master/FixedWidthToDelimiter.py
"""
This script will convert Fixed width File into Delimiter File, tried on Python 3.5 only
Sample run: (Order of argument doesnt matter)
python ConvertFixedToDelimiter.py -i SrcFile.txt -o TrgFile.txt -c Config.txt -d "|"
Inputs are as follows
1. Input FIle - Mandatory(Argument -i) - File which has fixed Width data in it
2. Config File - Optional (Argument -c, if not provided will look for Config.txt file on same path, if not present script will not run)
Should have format as
FieldName,fieldLength
eg:
FirstName,10
SecondName,8
Address,30
etc:
3. Output File - Optional (Argument -o, if not provided will be used as InputFIleName plus Delimited.txt)
4. Delimiter - Optional (Argument -d, if not provided default value is "|" (pipe))
"""
from collections import OrderedDict
import argparse
from argparse import ArgumentParser
import os.path
import sys
def slices(s, args):
position = 0
for length in args:
length = int(length)
yield s[position:position + length]
position += length
def extant_file(x):
"""
'Type' for argparse - checks that file exists but does not open.
"""
if not os.path.exists(x):
# Argparse uses the ArgumentTypeError to give a rejection message like:
# error: argument input: x does not exist
raise argparse.ArgumentTypeError("{0} does not exist".format(x))
return x
parser = ArgumentParser(description="Please provide your Inputs as -i InputFile -o OutPutFile -c ConfigFile")
parser.add_argument("-i", dest="InputFile", required=True, help="Provide your Input file name here, if file is on different path than where this script resides then provide full path of the file", metavar="FILE", type=extant_file)
parser.add_argument("-o", dest="OutputFile", required=False, help="Provide your Output file name here, if file is on different path than where this script resides then provide full path of the file", metavar="FILE")
parser.add_argument("-c", dest="ConfigFile", required=False, help="Provide your Config file name here,File should have value as fieldName,fieldLength. if file is on different path than where this script resides then provide full path of the file", metavar="FILE",type=extant_file)
parser.add_argument("-d", dest="Delimiter", required=False, help="Provide the delimiter string you want",metavar="STRING", default="|")
args = parser.parse_args()
#Input file madatory
InputFile = args.InputFile
#Delimiter by default "|"
DELIMITER = args.Delimiter
#Output file checks
if args.OutputFile is None:
OutputFile = str(InputFile) + "Delimited.txt"
print ("Setting Ouput file as "+ OutputFile)
else:
OutputFile = args.OutputFile
#Config file check
if args.ConfigFile is None:
if not os.path.exists("Config.txt"):
print ("There is no Config File provided exiting the script")
sys.exit()
else:
ConfigFile = "Config.txt"
print ("Taking Config.txt file on this path as Default Config File")
else:
ConfigFile = args.ConfigFile
fieldNames = []
fieldLength = []
myvars = OrderedDict()
with open(ConfigFile) as myfile:
for line in myfile:
name, var = line.partition(",")[::2]
myvars[name.strip()] = int(var)
for key,value in myvars.items():
fieldNames.append(key)
fieldLength.append(value)
with open(OutputFile, 'w') as f1:
fieldNames = DELIMITER.join(map(str, fieldNames))
f1.write(fieldNames + "\n")
with open(InputFile, 'r') as f:
for line in f:
rec = (list(slices(line, fieldLength)))
myLine = DELIMITER.join(map(str, rec))
f1.write(myLine + "\n")
awk
生成带有适当的子字符串命令的 awk 脚本
cat cols
4
2
5
1
1
<cols awk '{ print "substr($0,"p","$1")"; cs+=$1; p=cs+1 }' p=1
输出:
substr($0,1,4)
substr($0,5,2)
substr($0,7,5)
substr($0,12,1)
substr($0,13,1)
将这些行合并成一个有效的 awk 脚本:
<cols awk '{ print "substr($0,"p","$1")"; cs+=$1; p=cs+1 }' p=1 |
paste -sd, | sed 's/^/{ print /; s/$/ }/'
输出:
{ print substr($0,1,4),substr($0,5,2),substr($0,7,5),substr($0,12,1),substr($0,13,1) }
将上述内容重定向到文件中,例如/tmp/t.awk
,并在输入文件上运行它:
<infile awk -f /tmp/t.awk
输出:
aasd fh 90135 1 2
ajsh dj 2445 d f
或者使用逗号作为输出分隔符:
<infile awk -f /tmp/t.awk OFS=,
输出:
aasd,fh,90135,1,2
ajsh,dj, 2445,d,f
gawk
,那么“FIELDWIDTHS”参数才能对我起作用;在Ubuntu 14.04.3上。 - Arthurgawk
它就无所谓了。至少对于我来说,在Ubuntu 14.04.3上安装了awk
但没有安装gawk
。 - Arthur