使用mlr重排CSV列时遇表头与数据长度不匹配错误的问询

问题场景 #

用户需要重排CSV列顺序，原始CSV数据如下：

USI,CFG LEI,Counterparty LEI,UPI,Product Class,Execution venue,MTM,Currency 1,Currency 2,Notional amount 1,Notional amount 2,Exchange rate,Delivery type,Settlement or expiration date,Execution Venue LEI,Indication of collateralization,Trade ID,CounterParty full Name,Start Date,Deal Code,Settlement Currency,Valuation Fixing Date,Trade Date and timestamp,CIS Code (Internal),Trade Description,UTI,Is CFG Reporting Party?,Contract type,UTI Leg 2 ,Counterparty purchasing protection ,Counterparty selling protection ,Reference Entity ,Maturity Date ,Price ,Cap Strike ,Floor Strike ,Notional Amount ,Notional Currency ,Upfront Payment Amount ,Upfront Payment Currency ,Payment frequency of the reporting counterparty. ,Payment frequency of the non-reporting counterparty ,Day count convention ,Notional amount (leg 1) ,Notional currency (leg 1) ,Notional amount (leg 2) ,Notional currency (leg 2) ,Pay Leg Type (CFG) ,Receive Leg Type (CFG) ,Direction ,Option type ,Fixed rate ,Fixed rate 2 ,Fixed rate day count fraction ,Floating rate payment frequency ,Floating rate reset frequency ,Floating rate index name/rate period ,Floating rate Index 2 ,Buyer ,Seller ,Quantity unit ,Quantity frequency ,Total quantity ,Total Remaining Quantity ,Notional ,Settlement method ,Price unit ,Price currency ,Buyer pay index ,Buyer pay averaging method ,Seller pay index ,Seller pay averaging method ,Grade ,Option style ,Option premium ,Hours from through ,Hours from through time zone ,Days of week ,Load type

,DRMSV1Q0EKMEXLAU1P80,549300UGRJZFKLBDNB67,,FXF,OffFacility,-6573.66,EUR,USD,817785,923933.49,1.1298,Physical,4/13/2028,,Uncollateralized,2.02304E+12,AG NET LEASE RLTY FND IV Q LP,4/12/2023,B,,,12-Apr-23,AA077C7,FX FORWARD TRANSACTION,DRMSV1Q0EKMEXLAU1P80WSSFX202304120000436044681000001,Y,FX FORWARD TRANSACTION,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

,DRMSV1Q0EKMEXLAU1P80,549300UGRJZFKLBDNB67,,FXF,OffFacility,-6573.66,EUR,USD,817785,923933.49,1.1298,Physical,4/13/2028,,Uncollateralized,2.02304E+12,AG NET LEASE RLTY FND IV Q LP,4/12/2023,B,,,12-Apr-23,AA077C7,FX FORWARD TRANSACTION,DRMSV1Q0EKMEXLAU1P80WSSFX202304120000436044681000001,Y,FX FORWARD TRANSACTION,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

用户编写的bash脚本：

#!/bin/bash
 
#Reordering the  columns
    
temp_file=$(mktemp)
    
mlr --csv reorder -f "\"Trade ID\",\"Trade Date and timestamp\",\"CounterParty full Name\",\"CIS Code (Internal)\",\"Trade Description\",\"USI\",\"UTI\",\"UTI Leg 2\",\"CFG LEI\",\"Counterparty LEI\",\"Is CFG Reporting Party?\",\"UPI\",\"Contract type\",\"Execution venue\",\"Execution Venue LEI\",\"Counterparty purchasing protection\",\"Counterparty selling protection\",\"Reference Entity\",\"Start Date\",\"Maturity Date\",\"Price\",\"Cap Strike\",\"Floor Strike\",\"Notional Amount\",\"Notional Currency\",\"Upfront Payment Amount\",\"Upfront Payment Currency\",\"Payment frequency of the reporting counterparty.\",\"Payment frequency of the non-reporting counterparty\",\"Currency 1\",\"Currency 2\",\"Notional amount 1\",\"Notional amount 2\",\"Exchange rate\",\"Delivery type\",\"Settlement or expiration date\",\"Day count convention\",\"Notional amount (leg 1)\",\"Notional currency (leg 1)\",\"Notional amount (leg 2)\",\"Notional currency (leg 2)\",\"Pay Leg Type (CFG)\",\"Receive Leg Type (CFG)\",\"Direction\",\"Option type\",\"Fixed rate\",\"Fixed rate 2\",\"Fixed rate day count fraction\",\"Floating rate payment frequency\",\"Floating rate reset frequency\",\"Floating rate index name/rate period\",\"Floating rate Index 2\",\"Buyer\",\"Seller\",\"Quantity unit\",\"Quantity frequency\",\"Total quantity\",\"Total Remaining Quantity\",\"Notional\",\"Settlement method\",\"Price unit\",\"Price currency\",\"Buyer pay index\",\"Buyer pay averaging method\",\"Seller pay index\",\"Seller pay averaging method\",\"Grade\",\"Option style\",\"Option premium\",\"Hours from through\",\"Hours from through time zone\",\"Days of week\",\"Load type\",\"Indication of collateralization\",\"MTM\",\"Product Class\",\"Deal Code\",\"Settlement Currency\",\"Valuation Fixing Date\"" Wallstreet_Source_File.csv > "$temp_file"
    
mv "$temp_file" Wallstreet_Source_File.csv

执行脚本后报错：

mlr CSV header/data length mismatch 79 != 28 at filename Wallstreet_Source_File.csv row2

错误原因分析 #

• 列数校验不匹配：原始CSV表头有79列，但数据行的逗号数量不足，导致mill判定数据行只有28列，和表头列数不一致，触发严格校验报错。
• 字段引用格式错误：使用mlr reorder -f时，字段名无需额外加双引号（你的字段名不含逗号，不需要引号包裹），多余的双引号会被mill当作字段名的一部分，无法匹配原始表头字段，进一步加剧列数不匹配问题。

解决方案 #

方法1：修正mill命令的字段引用格式 #

去掉字段名的双引号，直接用逗号分隔字段列表即可：

#!/bin/bash
 
# Reordering the columns
temp_file=$(mktemp)

# 字段名直接用逗号分隔，无需双引号
mlr --csv reorder -f Trade ID,Trade Date and timestamp,CounterParty full Name,CIS Code (Internal),Trade Description,USI,UTI,UTI Leg 2,CFG LEI,Counterparty LEI,Is CFG Reporting Party?,UPI,Contract type,Execution venue,Execution Venue LEI,Counterparty purchasing protection,Counterparty selling protection,Reference Entity,Start Date,Maturity Date,Price,Cap Strike,Floor Strike,Notional Amount,Notional Currency,Upfront Payment Amount,Upfront Payment Currency,Payment frequency of the reporting counterparty.,Payment frequency of the non-reporting counterparty,Currency 1,Currency 2,Notional amount 1,Notional amount 2,Exchange rate,Delivery type,Settlement or expiration date,Day count convention,Notional amount (leg 1),Notional currency (leg 1),Notional amount (leg 2),Notional currency (leg 2),Pay Leg Type (CFG),Receive Leg Type (CFG),Direction,Option type,Fixed rate,Fixed rate 2,Fixed rate day count fraction,Floating rate payment frequency,Floating rate reset frequency,Floating rate index name/rate period,Floating rate Index 2,Buyer,Seller,Quantity unit,Quantity frequency,Total quantity,Total Remaining Quantity,Notional,Settlement method,Price unit,Price currency,Buyer pay index,Buyer pay averaging method,Seller pay index,Seller pay averaging method,Grade,Option style,Option premium,Hours from through,Hours from through time zone,Days of week,Load type,Indication of collateralization,MTM,Product Class,Deal Code,Settlement Currency,Valuation Fixing Date Wallstreet_Source_File.csv > "$temp_file"

mv "$temp_file" Wallstreet_Source_File.csv

方法2：先补全数据行列数再重排 #

如果数据行确实缺少逗号导致列数不足，先用awk补全每行的逗号数量到表头列数，再执行重排：

#!/bin/bash
temp_file=$(mktemp)
temp_file2=$(mktemp)

# 获取表头的列数
header_cols=$(head -n1 Wallstreet_Source_File.csv | tr ',' '\n' | wc -l)

# 补全每行的逗号数量，确保列数和表头一致
awk -v cols="$header_cols" -F, '{
    while (NF < cols) $0 = $0 ","
    print $0
}' Wallstreet_Source_File.csv > "$temp_file"

# 执行列重排
mlr --csv reorder -f Trade ID,Trade Date and timestamp,CounterParty full Name,CIS Code (Internal),Trade Description,USI,UTI,UTI Leg 2,CFG LEI,Counterparty LEI,Is CFG Reporting Party?,UPI,Contract type,Execution venue,Execution Venue LEI,Counterparty purchasing protection,Counterparty selling protection,Reference Entity,Start Date,Maturity Date,Price,Cap Strike,Floor Strike,Notional Amount,Notional Currency,Upfront Payment Amount,Upfront Payment Currency,Payment frequency of the reporting counterparty.,Payment frequency of the non-reporting counterparty,Currency 1,Currency 2,Notional amount 1,Notional amount 2,Exchange rate,Delivery type,Settlement or expiration date,Day count convention,Notional amount (leg 1),Notional currency (leg 1),Notional amount (leg 2),Notional currency (leg 2),Pay Leg Type (CFG),Receive Leg Type (CFG),Direction,Option type,Fixed rate,Fixed rate 2,Fixed rate day count fraction,Floating rate payment frequency,Floating rate reset frequency,Floating rate index name/rate period,Floating rate Index 2,Buyer,Seller,Quantity unit,Quantity frequency,Total quantity,Total Remaining Quantity,Notional,Settlement method,Price unit,Price currency,Buyer pay index,Buyer pay averaging method,Seller pay index,Seller pay averaging method,Grade,Option style,Option premium,Hours from through,Hours from through time zone,Days of week,Load type,Indication of collateralization,MTM,Product Class,Deal Code,Settlement Currency,Valuation Fixing Date "$temp_file" > "$temp_file2"

mv "$temp_file2" Wallstreet_Source_File.csv
rm "$temp_file"

验证 #

执行修正后的脚本后，检查输出的CSV文件，列会按照指定顺序排列，且无报错。

内容的提问来源于stack exchange，提问作者Loganayaki mahalingam