☰

我想使用 awk 在多行中打印带有变量公式的字符串

tag-icon • tag-icon bash printing format awk
我想使用 awk 在多行中打印带有变量公式的字符串

我继承了一个脚本，它获取一个文件并从中提取一些数据，创建一个新文件，并根据这些数据编写基于提取数据的公式。提取的数据在每次运行时数量都会有所不同，但这些数据的位置和使用的公式是一致的。

目前，脚本编写公式，由于它是硬编码的，因此单元格引用对于每一行都保持不变。我希望这个公式被写入引用它所在的行，而不是当前硬编码的引用第二行 - 公式的第一行是正确的。

目前，下面打印所有行的公式 - 我希望它是动态的并引用公式所在的实际行。

awk -F"," 'NR==1 {
   for (i=1;i<=NF;i++) {
      if ($i=="Primary") {
         Primary=i;
      }
      if ($i=="Sub Type") {
         Sub_Type=i;
      }   
      if ($i=="METHOD") {
         METHOD=i;
      }
      if ($i=="GLOBAL") {
         GLOBAL=i;
      }
     if ($i=="UNIQUE") {
         UNIQUE=i;
      }   
      if ($i=="ID") {
         ID=i;
      }
      if ($i=="PRIVATE_PLACEMENT") {
         PRIVATE_PLACEMENT=i;
      }
   }
}
NR > 1 {print $METHOD "," $Sub_Type "," $Primary "," $GLOBAL "," $ID "," $UNIQUE "," "\"\=abc\(D2\&\"\" xxxx\"\"\,\"\"SECURITY\"\"\"\)" "," "\"\=xyz\(D2\&\"\" xxxx\"\"\,\"\"last\"\"\,\"\"01\/01\/1900\"\"\,Today\(\)\,\"\"Quote\"\"\,\"\"P\"\"\,\"\"Points\=1\"\"\,\"\"cols\=2\;rows\=1\"\")\"" "," "," "," "\"\=xyz\(E2\&\"\" xxxx\"\"\,\"\"last\"\"\,\"\"01\/01\/1900\"\"\,Today\(\)\,\"\"Quote\"\"\,\"\"P\"\"\,\"\"Points\=1\"\"\,\"\"cols\=2\;rows\=1\"\")\"" "," "," "," "\"\=xyz\(F2\&\"\" buid\"\"\,\"\"last\"\"\,\"\"01\/01\/1900\"\"\,Today\(\)\,\"\"QuoteType\"\"\,\"\"P\"\"\,\"\"Points\=1\"\"\,\"\"cols\=2\;rows\=1\"\")\"" "," "," "," "\"\=abc\(D2\&\"\" xxxx\"\"\,\"\"source\"\"\"\)" "," "\"\=abc\(D2\&\"\" xxxx\"\"\,\"\"CRNCY\"\"\"\)""," "\"\=abc\(D2\&\"\" xxxx\"\"\,\"\"CRNCY\"\"\"\)" "," "\"\=IF\(ISBLANK\(H2\)\,\"\"\"\"\,IF\(ISBLANK\(K2\)\,\"\"\"\"\,IF\(H2\=K2\,\"\"Match\"\"\,\"\"No Match\"\"\"\)\)\)" ","  "\"\=IF\(ISBLANK\(H2\)\,\"\"\"\"\,IF\(ISBLANK\(N2\)\,\"\"\"\"\,IF\(H2\=N2\,\"\"Match\"\"\,\"\"No Match\"\"\"\)\)\)" "," "\"\=IF\(ISBLANK\(K2\)\,\"\"\"\"\,IF\(ISBLANK\(N2\)\,\"\"\"\"\,IF\(K2\=N2\,\"\"Match\"\"\,\"\"No Match\"\"\"\)\)\)" "," "," "=C2" "," "" "," "=H2" "," "=I2" "," "=Q2" "," "=R2"}' $WORK_DIR/sortedmissing.csv > $WORK_DIR/output1.xls

最好的方法是什么？是否有函数可以将公式从第 7 列（最后一列）拖到文件的最后一行？或者是否有循环函数可以为我执行此操作？

输入文件是一个打开的.xlsx文件，从中提取一些数据并放入新文件中 - 这可以是任意的。

Method  Primary Sub Type    GLOBAL  ID  UNIQUE
    123456789^DEFAULT^EOD^2019-07-12    Missing abcdefj 1234567 xyzxyz
    123456789^DEFAULT^EOD^2019-07-12    Missing abcdefj 1234567 xyzxyz
    123456789^DEFAULT^EOD^2019-07-12    Missing abcdefj 1234567 xyzxyz
    123456789^DEFAULT^EOD^2019-07-12    Missing abcdefj 1234567 xyzxyz
    123456789^DEFAULT^EOD^2019-07-12    Missing abcdefj 1234567 xyzxyz
    123456789^DEFAULT^EOD^2019-07-12    Missing abcdefj 1234567 xyzxyz
    123456789^DEFAULT^EOD^2019-07-12    Missing abcdefj 1234567 xyzxyz
    123456789^DEFAULT^EOD^2019-07-12    Missing abcdefj 1234567 xyzxyz
    123456789^DEFAULT^EOD^2019-07-12    Missing abcdefj 1234567 xyzxyz
    123456789^DEFAULT^EOD^2019-07-12    Missing abcdefj 1234567 xyzxyz

输出在 csv 中看起来像这样 - 第一行是标题，然后在其下是从输入文件复制的 5 列数据 - 第 6 列和整个脚本创建了许多公式，这些公式在每一行中重复使用相同的单元格引用，但是我想使这个引用位于 D2 中的行，第 3 行应该是 D3 等等......

Method  Sub Type    Primary GLOBAL  ID  UNIQUE SECURITY_TYP FIGI LAST_UPDATE_DT 

Missing Price   123456^XXXXX^XXXXXEOD^2019-07-12 1234567890 1234567 123456789   =xxx(D2&" figi","SECURITY_TYP") =xxx(D2&" figi","last","01/01/1900",BToday(),"QuoteType","P","Points=1") I  

Missing Price 123456^XXXXX^XXXXXEOD^2019-07-12  1234567890  1234567 123456789   =BDP(D2&" figi","SECURITY_TYP") =xxxx(D2&" figi","last","01/01/1900",BToday(),"QuoteType","P","Points=1")

以真实形式输出

Method,Sub Type,Primary,GLOBAL,ID,UNIQUE,SECURITY_TYP,FIGI LAST_UPDATE_DT,FIGI PX_LAST,,SEDOL LAST_UPDATE_DT,SEDOL PX_LAST,,BB_UNIQUE LAST_UPDATE_DT,BB_UNIQUE PX_LAST,,PRICING_SOURCE,CRNCY,QUOTED_CRNCY,FIGI SEDOL PRICE CHECK,FIGI PRICE CHECK,SEDOL PRICE CHECK,,PrimaryIdentifier,CL_SUBSCRIBER,PRICE_DATE,BPL_SERVICE_PRICE,BPL_PRICING_SOURCE,PRICE_CRNCY
,Missing Price,123456789^DEFAULT^EOD^2019-07-19,abcdefj,123456,xyzxyzxyz,"=abc(D2&"" moon"",""TYP"""),"=abc(D2&"" moon"",""last"",""01/01/1900"",BToday(),""QuoteType"",""P"",""Points=1"",""cols=2;rows=1"")",,,"=abc(E2&"" star"",""last"",""01/01/1900"",BToday(),""QuoteType"",""P"",""Points=1"",""cols=2;rows=1"")",,,"=abc(F2&"" buid"",""last"",""01/01/1900"",BToday(),""QuoteType"",""P"",""Points=1"",""cols=2;rows=1"")",,,"=abc(D2&"" moon"",""pricing_source"""),"=abc(D2&"" moon"",""CRNCY"""),"=abc(D2&"" moon"",""QUOTED_CRNCY"""),"=IF(ISBLANK(H2),"""",IF(ISBLANK(K2),"""",IF(H2=K2,""Match"",""No Match"""))),"=IF(ISBLANK(H2),"""",IF(ISBLANK(N2),"""",IF(H2=N2,""Match"",""No Match"""))),"=IF(ISBLANK(K2),"""",IF(ISBLANK(N2),"""",IF(K2=N2,""Match"",""No Match"""))),,=C2,,=H2,=I2,=Q2,=R2
I,Missing Price,123456789^DEFAULT^EOD^2019-07-19,abcdefj,,123456,"=abc(D2&"" moon"",""TYP"""),"=abc(D2&"" moon"",""last"",""01/01/1900"",BToday(),""QuoteType"",""P"",""Points=1"",""cols=2;rows=1"")",,,"=abc(E2&"" star"",""last"",""01/01/1900"",BToday(),""QuoteType"",""P"",""Points=1"",""cols=2;rows=1"")",,,"=abc(F2&"" buid"",""last"",""01/01/1900"",BToday(),""QuoteType"",""P"",""Points=1"",""cols=2;rows=1"")",,,"=abc(D2&"" moon"",""pricing_source"""),"=abc(D2&"" moon"",""CRNCY"""),"=abc(D2&"" moon"",""QUOTED_CRNCY"""),"=IF(ISBLANK(H2),"""",IF(ISBLANK(K2),"""",IF(H2=K2,""Match"",""No Match"""))),"=IF(ISBLANK(H2),"""",IF(ISBLANK(N2),"""",IF(H2=N2,""Match"",""No Match"""))),"=IF(ISBLANK(K2),"""",IF(ISBLANK(N2),"""",IF(K2=N2,""Match"",""No Match"""))),,=C2,,=H2,=I2,=Q2,=R2

您可以在此处看到，上面的代码将公式的行写在两行中，但使用相同的单元格引用“D2” - 我希望第二行引用 D3 等

提前致谢

相关内容