☰

更改 csv 文件中的位置运算符

tag-icon • tag-icon awk sed python cat dash
更改 csv 文件中的位置运算符

我一直在编辑 CSV 文件，以便可以将其导入到 postgres 中。此时我想将值为负数“-”时的运算符从第5列更改为该列的左侧。当它是“+”时我想删除该运算符。

当前 CSV：

10013534,2021-01-01,I,0090922002,000000000009102629+,000000000000000000-,000000000000000000-,
10013534,2021-01-01,I,0091000002,000000000063288833-,000000000000000000-,000000000000000000-,
10013534,2021-01-01,I,0091100005,000000000063288833-,000000000000000000-,000000000000000000-,
10013534,2021-01-01,I,0091110002,000000000063288833+,000000000000000000-,000000000000000000-,
10013534,2021-01-01,I,0099999995,000000008017897139-,000000000000000000-,000000000000000000-,

它应该是什么样子

10013534,2021-01-01,I,0090922002,000000000009102629,000000000000000000-,000000000000000000-,
10013534,2021-01-01,I,0091000002,-000000000063288833,000000000000000000-,000000000000000000-,
10013534,2021-01-01,I,0091100005,-000000000063288833,000000000000000000-,000000000000000000-,
10013534,2021-01-01,I,0091110002,000000000063288833,000000000000000000-,000000000000000000-,
10013534,2021-01-01,I,0099999995,-000000008017897139,000000000000000000-,000000000000000000-,

如有必要，可以删除第 6 列和第 7 列

答案1

像这样的东西：

awk -F "," '{sign=substr($5,length($5),1);$5=substr($5,0,length($5)-1); if(sign =="-"){$5="-"$5}; print}' ./mycsv

答案2

你可能想尝试

function movesign (TMP) {IX  = sub (/\+$/, "&", TMP)       # create increment based on sign, so
                                                           # to drop the plus signs
                         return substr (TMP TMP, length(TMP) + IX, length(TMP) - IX)
                                                           # by writing the string twice, and chop-
                                                           # ping it off at the right position with
                                                           # the right length, we get the desired result
                        }                       

/^#/    {pfx = substr($0, 8, 7) OFS substr($0, 32, 4) "-" substr($0, 30, 2) "-01" OFS substr($0, 36) 
                                                           # prepare prefix from header lines
         sub (/ *$/, "", pfx)                              # trim trailing spaces
         next
        }
/^@/    {next
        }

        {gsub(/[+-]/, "&,")                                # massage the input line into the right shape
          sub(/[, ]*$/, "")
         gsub(/  */, ",")

         $1 = pfx OFS $1                                   # prepend the prefix
         $0 = $0                                           # and recalculate the fields

         $5 = movesign($5)                                 # use function to taste
#        $6 = movesign($6)
#        $7 = movesign($7)
        }
1
' OFS=, *.csv

它会窃取您迄今为止获得的答案，但会一次性对.csv您的cwd.请参阅评论的逻辑，如果出现问题，请回来。

答案3

假设结构保持不变，并且第 5 列是包含超过 10 个整数字符的逗号后的第一个匹配项，您可以尝试此操作，如果找到的字符sed将移动破折号，如果找到的字符则排除破折号。-+

$ sed 's/,\([0-9]\{10,\}[^,]*\)\(-\)\|+,/,\2\1/' input_file
10013534,2021-01-01,I,0090922002,000000000009102629,000000000000000000-,000000000000000000-,
10013534,2021-01-01,I,0091000002,-000000000063288833,000000000000000000-,000000000000000000-,
10013534,2021-01-01,I,0091100005,-000000000063288833,000000000000000000-,000000000000000000-,
10013534,2021-01-01,I,0091110002,000000000063288833,000000000000000000-,000000000000000000-,
10013534,2021-01-01,I,0099999995,-000000008017897139,000000000000000000-,000000000000000000-,

相关内容