Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

how to get the columns name and fields from this file.

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting how to get the columns name and fields from this file.
# 8  
Old 06-07-2007
Are you sure that you typed the awk script correctly ?
Did the awk program run without error ?

I ran again the awk program with all your sample datas :
Code:
$ cat base_compare.txt  
            || Base Compare
key_NUM     || FNL_PIPLN FNL_PIPLN Diff. % Diff
            || _EXIT_DT _EXIT_DT
___________ || _________ _________ _________ _________
            ||
L0051041044 || 17303 . . .
L0053125829 || 17303 . . .
L0053855466 || 17303 . . .
L0061072864 || 17303 . . .
L0061288205 || 17303 17103 . .
L0062343827 || 17303 17203 . .
L0062526348 || 17303 23201 . .
L0063725295 || 17303 . . .
L0064004237 || 17303 . . .
L0064723026 || 17303 . . .
            || Base Compare
key_NUM     || INIT_WHSE INIT_WHSE Diff. % Diff
            || _AMT _AMT
___________ || _________ _________ _________ _________
            ||
L0051041044 || 100.2340 12 . .
L0053125829 || 99.9700 . . .
L0053855466 || 98.2790 . . .
L0061072864 || 99.9990 . . .
L0061288205 || 99.1050 134 . .
L0062343827 || 98.5080 . . .
L0062526348 || 100.2340 . . .
L0063725295 || 98.9560 . . .
L0064004237 || 98.9560 . . .
L0064723026 || 99.2930 213 . .
            || Base Compare
key_NUM     || FNL_PIPLN FNL_PIPLN Diff. % Diff
            || _AMT _AMT
___________ || _________ _________ _________ _________
            ||
L0051041044 || 100.2340 . . .
L0053125829 || 99.9700 . . .
L0053855466 || 98.2790 . . .
            || Base Value Compare Value
key_Num     || FNL_PIPLN_EXIT_RSN_C FNL_PIPLN_EXIT_RSN_C
            || D D
___________ || _ _
            ||
L0051041044 || C
L0053125829 || C
L0053855466 || C
L0061072864 || C
L0061288205 || C
L0062343827 || C
L0062526348 || C
L0063725295 || C
L0064004237 || C
L0064723026 || C
$ awk -f extract.awk base_compare.txt
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,17103
FNL_PIPLN_EXIT_DT,17303,17203
FNL_PIPLN_EXIT_DT,17303,23201
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
INIT_WHSE_AMT,100.2340,12
INIT_WHSE_AMT,99.9700,.
INIT_WHSE_AMT,98.2790,.
INIT_WHSE_AMT,99.9990,.
INIT_WHSE_AMT,99.1050,134
INIT_WHSE_AMT,98.5080,.
INIT_WHSE_AMT,100.2340,.
INIT_WHSE_AMT,98.9560,.
INIT_WHSE_AMT,98.9560,.
INIT_WHSE_AMT,99.2930,213
FNL_PIPLN_AMT,100.2340,.
FNL_PIPLN_AMT,99.9700,.
FNL_PIPLN_AMT,98.2790,.
FNL_PIPLN_AMT,Base,Value
FNL_PIPLN_AMT,FNL_PIPLN_EXIT_RSN_C,FNL_PIPLN_EXIT_RSN_C
FNL_PIPLN_AMT,D,D
FNL_PIPLN_AMT,_,_
FNL_PIPLN_AMT,C,
FNL_PIPLN_AMT,C,
FNL_PIPLN_AMT,C,
FNL_PIPLN_AMT,C,
FNL_PIPLN_AMT,C,
FNL_PIPLN_AMT,C,
FNL_PIPLN_AMT,C,
FNL_PIPLN_AMT,C,
FNL_PIPLN_AMT,C,
FNL_PIPLN_AMT,C,
$

From the line "Base Value Compare Value', the file haven't the same format as the rest of the file.
I add a control to bypass this section of the file :
Code:
#!/usr/bin/awk -f
# Awk File: extract.awk

BEGIN {
   FS  = "|";
   OFS = ",";
}

(/Base Value/) {
   column_name = "";
   next;
}

(/Base Compare/),(NF==3 && $1 ~ /^_+ *$/) {
   if (/Base Compare/) {
      column_name = "";
      next;
   }
   if ($3 ~ /^ *__+/)        next;
   sub(/^[[:space:]]*/,  "", $3);
   sub(/[[:space:]]+.*/, "", $3)
   column_name = column_name $3
   next;
}

NF == 3 && column_name  {
   sub(/^[[:space:]]*/,  "", $3);
   if ($3 == "") next;
   split($3, values, /[[:space:]]+/);
   print column_name,values[1],values[2];
}

Output:
Code:
$ awk -f extract.awk base_compare.txt
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,17103
FNL_PIPLN_EXIT_DT,17303,17203
FNL_PIPLN_EXIT_DT,17303,23201
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
INIT_WHSE_AMT,100.2340,12
INIT_WHSE_AMT,99.9700,.
INIT_WHSE_AMT,98.2790,.
INIT_WHSE_AMT,99.9990,.
INIT_WHSE_AMT,99.1050,134
INIT_WHSE_AMT,98.5080,.
INIT_WHSE_AMT,100.2340,.
INIT_WHSE_AMT,98.9560,.
INIT_WHSE_AMT,98.9560,.
INIT_WHSE_AMT,99.2930,213
FNL_PIPLN_AMT,100.2340,.
FNL_PIPLN_AMT,99.9700,.
FNL_PIPLN_AMT,98.2790,.
$

Jean-Pierre.
# 9  
Old 06-07-2007
Fnl_pipln_exit_rsn_cd
hi Jean-Pierre.

we need this column Fnl_pipln_exit_rsn_cd and values well ,we can delete the Value in both places after Base and Compare for this column.
# 10  
Old 06-07-2007
What is the right result that you are expected for with the sample datas of my previous post ?
Show us the datas that the script must display.

Jean-Pierre.
# 11  
Old 06-07-2007
Expected result.
Hi

here is the expected result.

FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,17103
FNL_PIPLN_EXIT_DT,17303,17203
FNL_PIPLN_EXIT_DT,17303,23201
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
FNL_PIPLN_EXIT_DT,17303,.
INIT_WHSE_AMT,100.2340,12
INIT_WHSE_AMT,99.9700,.
INIT_WHSE_AMT,98.2790,.
INIT_WHSE_AMT,99.9990,.
INIT_WHSE_AMT,99.1050,134
INIT_WHSE_AMT,98.5080,.
INIT_WHSE_AMT,100.2340,.
INIT_WHSE_AMT,98.9560,.
INIT_WHSE_AMT,98.9560,.
INIT_WHSE_AMT,99.2930,213
FNL_PIPLN_AMT,100.2340,.
FNL_PIPLN_AMT,99.9700,.
FNL_PIPLN_AMT,98.2790,.
FNL_PIPLN_EXIT_RSN_CD,c,
FNL_PIPLN_EXIT_RSN_CD,C,
FNL_PIPLN_EXIT_RSN_CD,C,
FNL_PIPLN_EXIT_RSN_CD,C,
FNL_PIPLN_EXIT_RSN_CD,C,
FNL_PIPLN_EXIT_RSN_CD,C,
FNL_PIPLN_EXIT_RSN_CD,C,
FNL_PIPLN_EXIT_RSN_CD,C,
FNL_PIPLN_EXIT_RSN_CD,C,
FNL_PIPLN_EXIT_RSN_CD,C,
FNL_PIPLN_EXIT_RSN_CD,C,
Last edited by rnallamothu; 06-07-2007 at 05:17 PM..
# 12  
Old 06-07-2007
Final version :
Code:
#!/usr/bin/awk -f
# Awk File: extract.awk

BEGIN {
   FS  = "|";
   OFS = ",";
}

(/Base Compare|Base Value/),(NF==3 && $1 ~ /^_+ *$/) {
   if (/Base Compare|Base Value/) {
      column_name = "";
      next;
   }
   if ($1 ~ /^ *__+/)        next;
   sub(/^[[:space:]]*/,  "", $3);
   sub(/[[:space:]]+.*/, "", $3)
   column_name = column_name $3
   next;
}

NF == 3 && column_name  {
   sub(/^[[:space:]]*/,  "", $3);
   if ($3 == "") next;
   split($3, values, /[[:space:]]+/);
   print column_name,values[1],values[2];
}

Jean-Pierre.
# 13  
Old 06-08-2007
Thank you
Jean-Pierre.
it works fine thanks lot for yor time.
# 14  
Old 06-11-2007
sorry i need little changes the scripts.
previously we used the input file has only one key column left to the ||'s but it is keep changing this input file has 3 columns some files has 5 columns or more left to the || but after the pipes always two columns only these are constant.We have more that 100 files like this to extract data store into one file load into DB.

we need the out put like
PHP Code:
TMID|ORIG_NUM|QRM_RUN_TYP,1530|C0050140063|C,RUN_DATE,17203
TMID|ORIG_NUM|QRM_RUN_TYP,1530|C0050202418|C,RUN_DATE,17204
TMID|ORIG_NUM|QRM_RUN_TYP,1530|C0050202418|C,COMM_SRC_TXT,Purcha.COR_MA.30 DAY,NONE
Key_NUM,L0064723026,Key_NUM,FNL_PIPLN_EXIT_DT,17303,.
Key_NUM,L0064723027,FNL_PIPLN_EXIT_DT,17303,.
Key_NUM,L006472306,FNL_PIPLN_AMT,99.9700,.
Key_NUM,L006472303,FNL_PIPLN_AMT,98.2790,.
Key_NUM,L006472301,FNL_PIPLN_EXIT_RSN_CD,c,
Key_NUM,L006472301,FNL_PIPLN_EXIT_RSN_CD,C,
Key_NUM,L006472311,FNL_PIPLN_EXIT_RSN_CD,C, 

example file 1:
PHP Code:
                                      ||       Base    Compare
TM_ID       ORIG_NUM     QRM_RUN_TYP  ||   RUN_DATE   RUN_DATE      Diff.     % Diff
__________  ___________  ___________  ||  _________  _________  _________  _________
                                      ||
1530        C0050140063  C            ||      17203      17318   115.0000     0.6685
1530        C0050202418  C            ||      17204          .          .          .
                                      ||  Base Value           Compare Value
TM_ID       ORIG_NUM     QRM_RUN_TYP  ||  COMM_SRC_TXT          COMM_SRC_TXT
__________  ___________  ___________  ||  ____________________  ____________________
                                      ||
1530        C0050140063  C            ||  Purcha.COR_MA.30 DAY  NONE 
Exple: 2 we already used this file.
PHP Code:
 cat base_compare.txt  we used this file before.
            || Base Compare
key_NUM     || FNL_PIPLN FNL_PIPLN Diff. % Diff
            || _EXIT_DT _EXIT_DT
___________ || _________ _________ _________ _________
            ||
L0051041044 || 17303 . . .
L0053125829 || 17303 . . .
L0053855466 || 17303 . . .
L0061072864 || 17303 . . .
L0061288205 || 17303 17103 . .
L0062343827 || 17303 17203 . .
L0062526348 || 17303 23201 . .
L0063725295 || 17303 . . .
L0064004237 || 17303 . . .
L0064723026 || 17303 . . .
            || Base Compare
key_NUM     || INIT_WHSE INIT_WHSE Diff. % Diff
            || _AMT _AMT
___________ || _________ _________ _________ _________
            ||
L0051041044 || 100.2340 12 . .
L0053125829 || 99.9700 . . .
L0053855466 || 98.2790 . . . 


Thanks you
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to concatenate 2-columns by 2 -columns for a text file?

Hello, I want to concatenate 2-columns by 2-columns separated by colon. How can I do so? For example, I have a text file containing 6 columns separated by tab. I want to concatenate column 1 and 2; column 3 and 4; column 5 and 6, respectively, and put a colon in between. input file: 1 0 0 1... (10 Replies)
Discussion started by: huiyee1
10 Replies

2. UNIX for Advanced & Expert Users

Sort in fields date and columns.

Hi colleagues, I have this output, i need sort for 7th and 8th columns, the column 7th is date mm/dd/yyyy format and column 8th is time hh:mm:ss.number PRUEBA 1263 0007 1 0 7131292 03/25/2013 16:43:04.159976 PROCESS PRUEBA1 666 0146 1 0 11600064 ... (1 Reply)
Discussion started by: systemoper
1 Replies

3. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

4. Shell Programming and Scripting

How to parse fixed-width columns which may include empty fields?

I am trying to selectively display several columns from a db2 query, which gives me a fixed-width output (partial output listed here): --------- -------------------------- ------------ ------ 000 0000000000198012 702 29 000 0000000000198013 ... (9 Replies)
Discussion started by: ahsh79
9 Replies

5. UNIX for Advanced & Expert Users

Help in Deleting columns and Renaming Mutliple columns in a .Csv File

Hi All, i have a .Csv file in the below format startTime, endTime, delta, gName, rName, rNumber, m2239max, m2239min, m2239avg, m100016509avg, m100019240max, metric3min, m100019240avg, propValues 11-Mar-2012 00:00:00, 11-Mar-2012 00:05:00, 300.0, vma3550a, a-1_CPU Index<1>, 200237463, 0.0,... (9 Replies)
Discussion started by: mahi_mayu069
9 Replies

6. UNIX for Dummies Questions & Answers

Removing columns from a text file that do not have any values in second and third columns

I have a text file that has three columns. But at the end of the text file, there are trailing lines that have missing second and third columns: 4 0.04972604 KLHL28 4 0.0497332 CSTB 4 0.04979822 AIF1 4 0.04983331 DECR2 4 0.04990344 KATNB1 4 4 4 4 How can I remove the trailing... (3 Replies)
Discussion started by: evelibertine
3 Replies

7. Shell Programming and Scripting

Compare Fields from two text files using key columns

Hi All, I have two files to compare. Each has 10 columns with first 4 columns being key index together. The rest of the columns have monetary values. Using Perl, I want to read one file into hash; check for the key value availability in file 2; then compare the values in the rest of 6... (2 Replies)
Discussion started by: Sangtha
2 Replies

8. Web Development

split the fields in a column into 3 columns

Have a column "address" which is combination of city, region and postal code like. Format is : city<comma><space>region<space>postal code abc, xyz 123456 All these three city, region and postal code are not mandatory. There can be any one of the above. In that case a nell... (2 Replies)
Discussion started by: rakshit
2 Replies

9. Shell Programming and Scripting

how to convert fields from a text file to excel columns

i have this file which has the following contents: ,-0.3000 ,-0.3000 ,-0.3000 ,-0.9000 ,-0.9000 ,-0.9000 i would like to get this: -0.3-0.9-0.3-0.9-0.3-0.9 so far i am trying: awk '{for(i=1; i<=NF; i++) {printf("%f\n",$i)}}' test1 > test2 any help... (4 Replies)
Discussion started by: npatwardhan
4 Replies

10. Shell Programming and Scripting

Joining two files based on columns/fields

I've got two files, File1 and File2 File 1 has got combination of col1, col2 and col3 which comes on file2 as well, file2 does not get col4. Now based on col1, col2 and col3, I would like to get col4 from file1 and all the columns from file2 in a new file Any ideas? File1 ------ Col1 col2... (11 Replies)
Discussion started by: rudoraj
11 Replies
Login or Register to Ask a Question