☰

我有一個 csv 文件，其中一個欄位的資料用「」括起來，中間用逗號分隔。我想用另一個替換它

tag-icon • tag-icon text-processing text-formatting
我有一個 csv 文件，其中一個欄位的資料用「」括起來，中間用逗號分隔。我想用另一個替換它

下面是我的文件的前 5 行。在這裡，我想將第五列的“10,00,000.0”替換為“10,000,000.0”。

DE000A2200V7,09:30:00,8.5,8.509,"10,00,000.0","10,00,000.0","850,450.0"
DE000A2200V7,11:30:00,8.7,8.709,"20,00,000.0","20,000.0","870,450.0"
DE000A2200V7,13:30:00,8.763,8.883,"30,00,000.0","20,000.0","882,300.0"
DE000A2200V7,15:30:00,8.481,8.501,"10,00,000.0","10,00,000.0","849,100.0"
DE000A2200W5,09:30:00,15.826,15.835,"20,000.0","20,000.0","1,583,050.0"

答案1

透過使用from暫時將 CSV 分隔符號變更為 a @（或尚未屬於資料的任何其他字元）csvformatcsvkit，然後用 更改第 5 個欄位中的相關字串awk，並將分隔符號傳回原來的逗號：

csvformat -D '@' data.csv |
awk 'BEGIN { OFS=FS="@" } $5 == "10,00,000.0" { $5 = "10,000,000.0" }; 1' |
csvformat -d '@'

使用您的數據data.csv，這將產生：

DE000A2200V7,09:30:00,8.5,8.509,"10,000,000.0","10,00,000.0","850,450.0"
DE000A2200V7,11:30:00,8.7,8.709,"20,00,000.0","20,000.0","870,450.0"
DE000A2200V7,13:30:00,8.763,8.883,"30,00,000.0","20,000.0","882,300.0"
DE000A2200V7,15:30:00,8.481,8.501,"10,000,000.0","10,00,000.0","849,100.0"
DE000A2200W5,09:30:00,15.826,15.835,"20,000.0","20,000.0","1,583,050.0"

答案2

您可以使用以下sed命令來執行此操作：

sed -i 's/^\(\([^,]*,\)\{4\}\)\("[^"]*"\)\(.*\)$/\1"10,000,000.0"\4/' data.csv

答案3

確定「20,00,000.0」正確嗎？如果沒有，請嘗試

sed 's/,00,/,000,/' file

如果所有錯誤的號碼都應該被糾正，請添加s的標誌...g

答案4

與GNUawk

awk -vFPAT='([^,]*)|("[^"]+")' -vOFS=, '$5 == "\"10,00,000.0\"" \
{ $5="\"10,000,000.0\""}; {print}' file

測試

$ cat file
DE000A2200V7,09:30:00,8.5,8.509,"10,00,000.0","10,00,000.0","850,450.0"
DE000A2200V7,11:30:00,8.7,8.709,"20,00,000.0","20,000.0","870,450.0"
DE000A2200V7,13:30:00,8.763,8.883,"30,00,000.0","20,000.0","882,300.0"
DE000A2200V7,15:30:00,8.481,8.501,"10,00,000.0","10,00,000.0","849,100.0"
DE000A2200W5,09:30:00,15.826,15.835,"20,000.0","20,000.0","1,583,050.0"

$ awk -vFPAT='([^,]*)|("[^"]+")' -vOFS=, '$5 == "\"10,00,000.0\"" { $5="\"10,000,000.0\""}; {print}' file
DE000A2200V7,09:30:00,8.5,8.509,10,000,000.0,"10,00,000.0","850,450.0"
DE000A2200V7,11:30:00,8.7,8.709,"20,00,000.0","20,000.0","870,450.0"
DE000A2200V7,13:30:00,8.763,8.883,"30,00,000.0","20,000.0","882,300.0"
DE000A2200V7,15:30:00,8.481,8.501,10,000,000.0,"10,00,000.0","849,100.0"
DE000A2200W5,09:30:00,15.826,15.835,"20,000.0","20,000.0","1,583,050.0"

解釋

-vFPAT='([^,]*)|("[^"]+")'

以逗號分隔字段，處理字段可能包含嵌入逗號的情況（請參閱 GNU awk 手冊按內容定義字段）。

-vOFS=,

聲明輸出文件分隔符號是逗號,。

'$5 == "\"10,00,000.0\"" { $5="\"10,000,000.0\""}; {print}'

如果第五列符合字串“10,00,000.0”，則將其替換為“10,000,000.0”；列印該行。

相關內容