3 Replies Latest reply: Dec 3, 2013 1:47 PM by 966239 RSS

    Need to know how to get the first three columns from multi-line string variable

    966239

      Hi,

       

         I'n on a UNIX system.

       

      I have a multline string I'm trying grab the first three value from and store each into their own variable.

       

      So Basiacally

       

      export MYTEST=`sed 's/\t//g' V_INTC_BLD_INSTR_CHR.dat | tr -s " "| sed 's/|/ , /g' | awk '{print $1,$3,$5,"\n"}'`
      
      
      echo MYTEST- $MYTEST
      
      
      for MYTEST_LN in ${MYTEST}
          do
      
      
      export col1=`echo $MYTEST_LN | awk '{print $1}'`
      export col2=`echo $MYTEST_LN | awk '{print $2}'`
      export col3=`echo $MYTEST_LN | awk '{print $3}'`
      
      
      echo $col1,$col2,$col3
      
      
           echo "insert into INTC_BLD_INSTR_CHR values ('$col1','$col2','$col3');" >> insert.out
      
          done
      
      
      
      
      

       

      The output is like so:

       

      MYTEST- StgOutNT 13629 Y StgOutNT 13628 Y StgOutNT 8156 Y StgOutNT 8155 Y
      StgOutNT , ,
      13629 , ,
      Y, ,
      StgOutNT , ,
      13628 , ,
      Y , ,
      StgOutNT, ,
      8156 , ,
      Y , ,
      StgOutNT , ,
      8155 , ,
      Y , ,
      
      
      
      
      

       

      What I want is:

       

      MYTEST- StgOutNT 13629 Y StgOutNT 13628 Y StgOutNT 8156 Y StgOutNT 8155 Y
      StgOutNT, 13629, Y
      StgOutNT 13628, Y
      StgOutNT, 8156, Y
      StgOutNT ,8155, Y
      
      
      
      
      

       

      How  can I get this?

       

      I know I have to use sed or awk , I'm just new to it all.

       

      Thanks in Advance.

        • 1. Re: Need to know how to get the first three columns from multi-line string variable
          Catch-22

          The for loop will simply walk though the items delimited by a space or newline and store each item in a variable. The MYTEST_LN variable of your for loop example will only contain one item therefore no other information for awk to process.

           

          There are several ways to address your task. The most elegant way might be to extract the number of items in your variable and use bash variable arrays to construct columns. Variable arrays are available in Bash.

           

          I'n on a UNIX system.

           

          Since I don't know what shell you are using, or whether you are actually using Linux....?!

          • 2. Re: Need to know how to get the first three columns from multi-line string variable
            966239

            We're using ksh

             

            and the version is:

             

            Linux tparhedatd001.theserver.com 2.6.18-238.19.1.el5 x86_64

            • 3. Re: Need to know how to get the first three columns from multi-line string variable
              966239

              Here is my Answer: Feel free to make any improvements

               

              Get the number of rows from the table and:

               

              
                export NUM_ROWS=$(f_query_msm "select count(*) from V_INTC_BLD_INSTR_CHR;")
              
              
                echo NUM_ROWS - $NUM_ROWS
              
              
                QUERY="Select BLD_INSTR_NM, DIM_CHR_ID, CHR_DSP_FLG from V_INTC_BLD_INSTR_CHR where rownum < 10;"
                sqlplus -s $NTZ_NEWDIM_ENV_ACNT_NM/$NTZ_NEWDIM_ENV_ACNT_PWD@$NTZ_NEWDIM_ENV_DBLINK <<-EOF > ${TEMPDIR}/V_INTC_BLD_INSTR_CHR.dat
              
              
                set feedback off
                set head off
                set pagesize 0
                set echo off
                set colsep "|"
                set LIN 1500
                set TRIM ON
                ${QUERY}
              EOF
              
              
              export MYTEST=`sed 's/\t//g' V_INTC_BLD_INSTR_CHR.dat | tr -s " "| sed 's/|/ , /g' | awk '{print $1,$3,$5}'`
              
              
              
              COUNT=1
              while [ $COUNT -le $NUM_ROWS ]; do
              
              
                      export row1=$(sed -n ${COUNT}p <<< "$MYTEST")
              
              
                      export col1=`echo $row1 | awk '{print $1}'`
                      export col2=`echo $row1 | awk '{print $2}'`
                      export col3=`echo $row1 | awk '{print $3}'`
              
              
                      let COUNT=COUNT+1
              
              
                   echo "insert into INTC_BLD_INSTR_CHR values ('$col1','$col2','$col3');" >> insert.out
              
              
              done
              echo "#1"
              rm ${TEMPDIR}/V_INTC_BLD_INSTR_CHR.dat