Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Unanswered: Flat file-make field length equal to header length

    Hello Everyone,

    I am stuck with one issue while working on abstract flat file which i have to use as input and load data to table.

    Input Data-

    ------ ------------------------ ---- -----------------
    WFI001 Xxxxxx Control Work Item A Number of Records

    ------ ------------------------ ---- -------------------------
    WFI001 Xxxxxx Control Work Item B Count of Distinct Hook Id

    ------ ------------------------ ---- -------------------
    WFI001 Xxxxxx Control Work Item C Sum of Workitem Num

    ------ ---------------------- ---- -----------------
    WFI008 Xxxxxx Control DocTABA A Number of Records

    You can consider ----- as header for a field.
    I cant starighforward load this file because if you check column values -they have spaces in them ;so i need to enclose indivisual column in double quotes.

    Output i expect is
    "WFI001" "Xxxxxx Control Work Item" "A" "Number of Records"
    "WFI001" "Xxxxxx Control Work Item" "B" "Count of Distinct Hook Id "
    "WFI001" "Xxxxxx Control Work Item" "C" "Sum of Workitem Num "
    "WFI008" "Xxxxxx Control DocTABA" "A" "Number of Records "

    Any help or suggestions in this query will be of great help.

    Thanks!!!

  2. #2
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    I don't have time to completely do this, but:
    Code:
    while read line
    do
        col1=`echo "$line" |cut -c1-6`
        col2=`echo "$line" |cut -c8-29`
    etc
         echo \""$col1"\" \""$col2"\" 
    *or*
         echo -e \""$col1"\" \""$col2"\"
    done <inputfile
    Depending on which version of unix/linux you are using, you may need "echo -e" instead of "echo"
    Don't forget to enclose $colx in quotes to preserve the internal spaces.
    Last edited by kitaman; 03-28-12 at 17:03.

  3. #3
    Join Date
    Mar 2012
    Posts
    12
    What about the A, B, and C values? They are only 1 char long in the data fields, but the "header" length fields show them as 4 characters long.

    Anyway, try this awk script. Usage: awk -f adjustdata.awk inputfile

    Code:
    # filename: adjustdata.awk
    /----/ {
      len1 = length($1)
      len2 = length($2)
      len3 = length($3)
    }
    
    /^\w/ {
      fld1 = substr($0, 1, len1)
      fld2 = substr($0, len1 + 2,  len2)
    
      # NB: the 3d field is only 1 char long, although the header is 4 chars long!
      fld3 = substr($0, len1 + len2 + 3, 1)
      fld4 = substr($0, len1 + len2 + len3 + 1)    # and omit the last param
    
      printf("\"%s\" \"%s\" \"%s\" \"%s\"\n", fld1, fld2, fld3, fld4)
    }
    Hope this helps.

  4. #4
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Quote Originally Posted by kitaman View Post
    I don't have time to completely do this, but:
    Code:
    while read line
    do
        
        col1=`echo "$line" |cut -c1-6`
        col2=`echo "$line" |cut -c8-29`
        col3=`echo "$line" |cut -c30`
        col4=`echo $line" |cut -c32-`
        if [ $col3  != "-" ] -a [ "a$col3" != "a" ]
        then
        echo \""$col1"\" \""$col2"\"  \""$col3"\" \""$col4"\"
    
    
        fi
    done <inputfile
    Depending on which version of unix/linux you are using, you may need "echo -e" instead of "echo"
    Don't forget to enclose $colx in quotes to preserve the internal spaces.
    I added the last two fields.
    Are the dash lines part of the input file, and are there blank lines?
    Code:
    while read line                                             
    do                                                          
                                                                
        col1=`echo "$line" |cut -c1-6`                          
        col2=`echo "$line" |cut -c8-31`                         
        col3=`echo "$line" |cut -c33`                           
        col4=`echo "$line" |cut -c35-`                          
        if [ "a$col3"  != "a-"  -a  "a$col3" != "a" ]           
        then                                                    
        echo \""$col1"\" \""$col2"\"  \""$col3"\" \""$col4"\"   
        fi                                                      
    done <inputfile
    The last line of your example has field two one character shorter than the others.
    Code:
    "WFI001" "Xxxxxx Control Work Item" "A" "Number of Records"         
    "WFI001" "Xxxxxx Control Work Item" "B" "Count of Distinct Hook Id" 
    "WFI001" "Xxxxxx Control Work Item" "C" "Sum of Workitem Num"       
    "WFI008" "Xxxxxx Control DocTABA A" "N" "mber of Records"
    Last edited by kitaman; 03-30-12 at 10:36. Reason: add output

  5. #5
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Quote Originally Posted by kitaman View Post
    I don't have time to completely do this, but:
    Code:
    while read line
    do
        
        col1=`echo "$line" |cut -c1-6`
        col2=`echo "$line" |cut -c8-29`
        col3=`echo "$line" |cut -c30`
        col4=`echo $line" |cut -c32-`
        if [ $col3  != "-" ] -a [ "a$col3" != "a" ]
        then
        echo \""$col1"\" \""$col2"\"  \""$col3"\" \""$col4"\"
        fi
    done <inputfile
    Depending on which version of unix/linux you are using, you may need "echo -e" instead of "echo"
    Don't forget to enclose $colx in quotes to preserve the internal spaces.
    I added the last two fields.
    Are the dash lines part of the input file, and are there blank lines?
    Code:
    while read line                                             
    do                                                          
                                                                
        col1=`echo "$line" |cut -c1-6`                          
        col2=`echo "$line" |cut -c8-31`                         
        col3=`echo "$line" |cut -c33`                           
        col4=`echo "$line" |cut -c35-`                          
        if [ "a$col3"  != "a-"  -a  "a$col3" != "a" ]           
        then                                                    
        echo \""$col1"\" \""$col2"\"  \""$col3"\" \""$col4"\"   
        fi                                                      
    done <inputfile
    The last last of your example has field two one character shorter than the others.

Posting Permissions

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