linux - 将列转置为格式化输出 :

Question

想要将下面的非格式化输入转置为格式化输出，因为它具有多个分隔符，很想继续前进并寻求您的建议。

Sample_Input.txt

  UMTSGSMPLMNCallDataRecord                                   
    callForwarding                                              
      chargeableDuration                                            0  4 44'BCD
      dateForStartOfCharge                                        09011B'H
      recordSequenceNumber                                        57526'D




UMTSGSMPLMNCallDataRecord                                   
    mSTerminating                                               
      chargeableDuration                                            0  4 44'BCD
      dateForStartOfCharge                                        09011B'H
      recordSequenceNumber                                        57573'D
      originalCalledNumber                                        149212345678'TBCD
      redirectingNumber                                           149387654321'TBCD




!!!!!!!!!!!!!!!!!!!!!!!!1164!!!!!!!!!!!!!!!!!!!!!!


UMTSGSMPLMNCallDataRecord                                   
    mSTerminating                                               
      chargeableDuration                                            0  0 52'BCD
      dateForStartOfCharge                                        09011B'H
      recordSequenceNumber                                        45761'D
      tariffClass                                                 2'D
      timeForStartOfCharge                                          9 46 58'BCD
      calledSubscriberIMSI                                        21329701412F'TBCD

搜索了之前的问题并从 Mr.Porges 得到了一些相关的输入答案:

 #!/bin/sh
 # split lines on " " and use "," for output field separator
 awk 'BEGIN { FS = " "; i = 0; h = 0; ofs = "," }

   # empty line - increment item count and skip it
   /^\s*$/ { i++ ; next } 

   # normal line - add the item to the object and the header to the header list
   # and keep track of first seen order of headers
   {
      current[i, $1] = $2
      if (!($1 in headers)) {headers_ordered[h++] = $1}
      headers[$1]
   }

   END {
      h--

      # print headers
      for (k = 0; k <= h; k++)
      {
         printf "%s", headers_ordered[k]
         if (k != h) {printf "%s", ofs}
      } 
      print "" 

      # print the items for each object
      for (j = 0; j <= i; j++)
      {
         for (k = 0; k <= h; k++)
         {
            printf "%s", current[j, headers_ordered[k]]
            if (k != h) {printf "%s", ofs}
         }
         print ""
      }
  }' Sample_Input.txt

输出低于:

UMTSGSMPLMNCallDataRecord,callForwarding,chargeableDuration,dateForStartOfCharge,recordSequenceNumber,mSTerminating,originalCalledNumber,redirectingNumber,!!!!!!!!!!!!!!!!!!!!!!!!1164!!!!!!!!!!!!!!!!!!!!!!,tariffClass,timeForStartOfCharge,calledSubscriberIMSI
  ,,,,,,,,,,,
  ,,0,09011B'H,57526'D,,,,,,,
  ,,,,,,,,,,,
  ,,,,,,,,,,,
  ,,,,,,,,,,,
  ,,0,09011B'H,57573'D,,149212345678'TBCD,149387654321'TBCD,,,,
  ,,,,,,,,,,,
  ,,,,,,,,,,,
  ,,,,,,,,,,,
  ,,,,,,,,,,,
  ,,,,,,,,,,,
  ,,0,09011B'H,45761'D,,,,,2'D,9,21329701412F'TBCD
  ,,,,,,,,,,,

卡在哪里，(A)。需要处理 block 何时开始，如“UMTSGSMPLMNCallDataRecord”和空字段，然后是下一行单词，如 callForwarding/mSTerminate 等和空字段，第一个单词需要被视为行(“UMTSGSMPLMNCallDataRecord”)，下一行单词需要被视为列(callForwarding/mSTerminate)

(b)。需要避免将 ALPHAPET 放入列字段，即 09011B'H 放入 09011 、 149212345678'TBCD 放入 149212345678

预期输出:

UMTSGSMPLMNCallDataRecord,chargeableDuration,dateForStartOfCharge,recordSequenceNumber,originalCalledNumber,redirectingNumber,tariffClass,timeForStartOfCharge,calledSubscriberIMSI
callForwarding,0  4 44,09011,57526,,,,,
mSTerminating,0  4 44,09011,57573,149212345678,149387654321,,,
mSTerminating,0  0 52, 09011,45761,,,2,9 46 58,21329701412

编辑:我尝试过以下输入:

  UMTSGSMPLMNCallDataRecord                                   
    callForwarding                                              
      chargeableDuration                                            0  4 44'BCD
      dateForStartOfCharge                                        09011B'H
      recordSequenceNumber                                        57526'D




UMTSGSMPLMNCallDataRecord                                   
    mSTerminating                                               
      chargeableDuration                                            0  4 44'BCD
      dateForStartOfCharge                                        09011B'H
      recordSequenceNumber                                        57573'D
      originalCalledNumber                                        149212345678'TBCD
      redirectingNumber                                           149387654321'TBCD




!!!!!!!!!!!!!!!!!!!!!!!!1164!!!!!!!!!!!!!!!!!!!!!!


UMTSGSMPLMNCallDataRecord                                   
    mSTerminating                                               
      chargeableDuration                                            0  0 52'BCD
      dateForStartOfCharge                                        09011B'H
      recordSequenceNumber                                        45761'D
      tariffClass                                                 2'D
      timeForStartOfCharge                                          9 46 58'BCD
      calledSubscriberIMSI                                        21329701412F'TBCD

Answer 1

讨论

这是一个复杂的问题，因为记录不一致:有些记录缺少字段。由于每条记录占用多行，我们可以使用AWK的多记录功能来处理它:通过设置RS(记录分隔符)和FS(字段分隔符)变量。

接下来，我们需要收集 header 字段。我没有一个好的方法来做到这一点，所以我对标题行进行了硬编码。

一旦我们在 header 中建立了顺序，我们就需要一种从记录中提取特定字段的方法，我们可以通过函数 get_column() 来实现这一点。此函数还会根据您的要求在末尾去除非数字数据。

最后一件事，我们需要使用自制的 trim() 函数修剪第一列 ($2) 中的空格。

命令行

我将代码放在 make_csv.awk 中。运行它:

awk -f make_csv.awk Sample_Input.txt

文件 make_csv.awk

BEGIN { 
    # Next two lines: each record is of multiple lines, each line is a
    # separate field
    RS = ""
    FS = "\n"
    print "UMTSGSMPLMNCallDataRecord,chargeableDuration,dateForStartOfCharge,recordSequenceNumber,originalCalledNumber,redirectingNumber,tariffClass,timeForStartOfCharge,calledSubscriberIMSI"
}

function get_column(name, i, f, len) {
    # i, f and len are "local" variables
    for (i = 1; i <= NF; i++) {
        len = split($i, f, " ")
        if (f[1] == name) {
            result = f[2]
            for (i = 3; i <= len; i++) {
                result = result " " f[i]
            }

            # Remove the trailing non numeric data
            sub(/[a-zA-Z']+/, "", result) 
            return result
        }
    }
    return "" # get_column not found, return empty string
}

# Remove leading and trailing spaces
function trim(s) {
    sub(/[ \t]+$/, "", s)
    sub(/^[ \t]+/, "", s)
    return s
}

/UMTSGSMPLMNCallDataRecord/ {
    print trim($2) \
        "," get_column("chargeableDuration") \
        "," get_column("dateForStartOfCharge") \
        "," get_column("recordSequenceNumber") \
        "," get_column("originalCalledNumber") \
        "," get_column("redirectingNumber") \
        "," get_column("tariffClass") \
        "," get_column("timeForStartOfCharge") \
        "," get_column("calledSubscriberIMSI") \
        ""
}

更新

我根据 AVN 的最新输入尝试了 AWK 脚本，并得到了以下输出:

UMTSGSMPLMNCallDataRecord,chargeableDuration,dateForStartOfCharge,recordSequenceNumber,originalCalledNumber,redirectingNumber,tariffClass,timeForStartOfCharge,calledSubscriberIMSI
callForwarding,0 4 44,09011,57526,,,,,
mSTerminating,0 4 44,09011,57573,149212345678,149387654321,,,
mSTerminating,0 0 52,09011,45761,,,2,9 46 58,21329701412