Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2009
    Posts
    16

    Unanswered: Separating the Columns based on the ColumnSize

    i had Client Table with the colummns
    Code:
    col1 		 Decimal(3),
    col2 		 Decimal(2),
    col3 		 String(1),
    col4_blank1* string(2),
    col5_blank2 *string(10),
    col6	     string(3);
    
    
    the flat file is
    
    89522D            085NOVOLIN BRAND                 888888ALL OTHER
    
    i want to dispaly the output by seperating the columns with ~ based on the column size is 3 like col1 Decimal(3) and col2 having the size 2
    
    895~22~D~    ~          ~085~NOVOLIN
    Thanks in Advance
    Last edited by SeenuGuddu; 10-28-09 at 11:44.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    This starts you off. It passes your file through a small awk program. You'll need to expand the awk program to do all your fields - alter the 2nd param to be the postion in the line and the 3rd param to be the number of characters to use. In your code the 3rd field is 1 character but your example seems to hold many characters. You might also be able to use the cut with the -c option to get certain parts and then perhaps use the output-delimiter option to put in the ~.
    Code:
    #!/bin/sh
    
    cat your_file | \
    awk '{ print    substr( $0,1,3 ) "~" substr( $0,4,2 ) "~" substr( $0,6,1 ); }'
    
    exit

  3. #3
    Join Date
    Sep 2009
    Posts
    16
    in Temp table having the column size Value

    Code:
    Id Column_name     Col_size 
    1    col1                   3
    2    col2                   2
    3    col3                   1
    
    the records of the file  is 
    
    89522D            085
    
    based on the column size.. i have to delimitate the records of file into 
    
    895~22~D~            085
    can u give some example on this
    Last edited by SeenuGuddu; 10-29-09 at 07:11.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by SeenuGuddu
    can u give some example on this
    This example just runs your example data:
    Code:
    #!/bin/sh
    echo "89522D            085NOVOLIN BRAND                 888888ALL OTHER" | \
    awk '{ print    substr( $0,1,3 ) "~" substr( $0,4,2 ) "~" substr( $0,6,1 ); }'
    
    exit
    Running the above produces:
    Code:
    895~22~D

  5. #5
    Join Date
    Sep 2009
    Posts
    16

    Appending ~ delimeters between column values.

    Hi,

    Thanks for your reply.

    i had 100 records in each record having 50 columns of Data
    Code:
    sams11Y
    johns12Y
    ravi13N
    
     how would i get the position of the start string in substr( $0,1,3 )
    
    in that loop of each record i have to print with ~ delimated  like 
    sams~11~Y
    johns~12~Y
    Thanks
    Last edited by SeenuGuddu; 10-29-09 at 08:56.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by SeenuGuddu
    how would i get the position of the start string in substr( $0,1,3 )
    I'd suggest counting. The first character in the line is 1, the next is 2 and so fourth. You then put this value in the 2nd field above. The length of the field goes into the 3rd field. Repeat. This measuring device might also help:
    Code:
             1         2         3         4         5         6
    123456789012345678901234567890123456789012345678901234567890
    89522D            085NOVOLIN BRAND                 888888ALL OTHER
    895 starts at position 1.
    22 starts at position 4.
    D starts at position 6.
    ...

    Mike

  7. #7
    Join Date
    Sep 2009
    Posts
    16
    Thanks Mike

    Can u share the Example for this start String and End String in substr( $0,1,3 )

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    it generally doesn't hurt if you try a little experimenting for yourself ...

  9. #9
    Join Date
    Sep 2009
    Posts
    16
    sure Mike .. i will get back to u.

    Thanks

  10. #10
    Join Date
    Sep 2009
    Posts
    16
    hi mike

    here is the code but i have some problem with that

    ${COL_SIZE_DAT its a flat file of records of column size

    Code:
    	normalfile="john_789_has_2007-11-30_s"
    	
        j=1
        export delim=""
        while read col_size_dat
        do 
            export col_size=$(echo "${col_size_dat}" | cut -f2 -d"|")
            export delim_temp="${delim} substr( $0,$j,$col_size) \"~\"" 
            j=`expr $j + ${col_size}`   
        done < ${COL_SIZE_DAT}
       
       export delim_temp1=$(echo "$delim_temp" | sed 's/\(.*\)"~"/\1/')   
       export delim_cmd=`echo " awk '{ print ${delim_temp1}; }' " ` 
       echo $delim_cmd
     
      cat normalfilename | $delim_cmd > delimatedfile
    instead of normalfile="john_789_has_2007-11-30_s"
    u want me to give normalfile for $0 or any other way we had the substr( $0,$j,$col_size)

    wat should i use for redirecting the filewith delimated
    is that ok

    cat normalfilename | $delim_cmd > delimatedfile
    and after running this
    i am getting the error

    awk: cmd. line:1: '{
    awk: cmd. line:1: ^ invalid char ''' in expression
    Last edited by SeenuGuddu; 11-23-09 at 01:32.

  11. #11
    Join Date
    May 2009
    Location
    India
    Posts
    66
    col1 Decimal(3), cols 1,2,3 substr($0,1,3) in awk
    col2 Decimal(2), cols 4,5 : substr($0,4,2) in awk
    col3 String(1), and so on
    col4_blank1* string(2),
    col5_blank2 *string(10),
    col6 string(3);

    as another post said, it helps if you try it yourself first.

    End

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •