Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need help on awk script to format a file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need help on awk script to format a file
# 1  
Old 04-08-2014
Need help on awk script to format a file
Hi everyone,

Looking for some help in awk script to modify the format of a file. I seem to be struggling with this:

Input:
*****************************************************
Code:
,,,,TOTAL,2014/1-,2014/2-
0,TOTAL,BLANK,"Description",Total 1,Total 2,Total 3
1,  MFG Name 1,,"Description",MFG Total 1,MFG Total 2,MFG Total 3
2,    BRAND 1,,"Description",BRAND Total 1,BRAND Total 2,BRAND Total 3
3,      SUB BRAND 1,,"Description",SUB BRAND Total 1,SUB BRAND Total 2,SUB BRAND Total 3
4,        FORM 1,,"Description",FORM Total 1,FORM Total 2,FORM Total 3
5,          SKU NAME 1,SKU # 1,"Description",12.16,12.05,12.26
5,          SKU NAME 2,SKU # 2,"Description",13.54,13.95,13.12
5,          SKU NAME 3,SKU # 3,"Description",14.62,7.73,21.51
5,          SKU NAME 4,SKU # 4,"Description",10.75,-,21.51
5,          SKU NAME 5,SKU # 5,"Description",15.61,15.92,15.30
2,    BRAND 2,,"Description",BRAND Total 1,BRAND Total 2,BRAND Total 3
3,      SUB BRAND X,,"Description",SUB BRAND Total 1,SUB BRAND Total 2,SUB BRAND Total 3
4,        FORM Y,,"Description",FORM Total 1,FORM Total 2,FORM Total 3
5,          SKU NAME 6,SKU # 6,"Description",212.16,212.05,12.26
5,          SKU NAME 7,SKU # 7,"Description",13.54,13.95,13.12
5,          SKU NAME 8,SKU # 8,"Description",14.62,7.73,21.51
5,          SKU NAME 9,SKU # 9,"Description",10.75,-,21.51
5,          SKU NAME 10,SKU # 10,"Description",2.61,2.92,2.30
1,  MFG Name 2,,"Description",MFG Total 1,MFG Total 2,MFG Total 3
2,    BRAND 3,,"Description",BRAND Total 1,BRAND Total 2,BRAND Total 3
3,      SUB BRAND P,,"Description",SUB BRAND Total 1,SUB BRAND Total 2,SUB BRAND Total 3
4,        FORM Q,,"Description",FORM Total 1,FORM Total 2,FORM Total 3
5,          SKU NAME 11,SKU # 1,"Description",152.16,12.05,12.26
5,          SKU NAME 12,SKU # 2,"Description",16.54,16.95,16.12
5,          SKU NAME 13,SKU # 3,"Description",14.62,7.73,21.51
5,          SKU NAME 14,SKU # 4,"Description",10.75,-,21.51
5,          SKU NAME 15,SKU # 5,"Description",2.61,2.92,3.30
2,    BRAND W,,"Description",BRAND Total 1,BRAND Total 2,BRAND Total 3
3,      SUB BRAND 4,,"Description",SUB BRAND Total 1,SUB BRAND Total 2,SUB BRAND Total 3
4,        FORM U,,"Description",FORM Total 1,FORM Total 2,FORM Total 3
5,          SKU NAME 16,SKU # 6,"Description",14.16,14.05,15.26
5,          SKU NAME 17,SKU # 7,"Description",12.54,1.95,23.12
5,          SKU NAME 18,SKU # 8,"Description",14.62,7.73,21.51
5,          SKU NAME 19,SKU # 9,"Description",10.75,-,21.51
5,          SKU NAME 20,SKU # 10,"Description",34.61,45.92,4.30

*****************************************************
Expected output
*****************************************************
Code:
,,,,TOTAL,2014/1-,2014/2-
0,TOTAL,BLANK,"Description",Total 1,Total 2,Total 3
1,MFG Name 1,,,,,,"Description",MFG Total 1,MFG Total 2,MFG Total 3
2,MFG Name 1,BRAND 1,,,,,"Description",BRAND Total 1,BRAND Total 2,BRAND Total 3
3,MFG Name 1,BRAND 1,SUB BRAND 1,,,,"Description",SUB BRAND Total 1,SUB BRAND Total 2,SUB BRAND Total 3
4,MFG Name 1,BRAND 1,SUB BRAND 1,FORM 1,,,"Description",FORM Total 1,FORM Total 2,FORM Total 3
5,MFG Name 1,BRAND 1,SUB BRAND 1,FORM 1,SKU NAME 1,SKU # 1,"Description",12.16,12.05,12.26
5,MFG Name 1,BRAND 1,SUB BRAND 1,FORM 1,SKU NAME 2,SKU # 2,"Description",13.54,13.95,13.12
5,MFG Name 1,BRAND 1,SUB BRAND 1,FORM 1,SKU NAME 3,SKU # 3,"Description",14.62,7.73,21.51
5,MFG Name 1,BRAND 1,SUB BRAND 1,FORM 1,SKU NAME 4,SKU # 4,"Description",10.75,-,21.51
5,MFG Name 1,BRAND 1,SUB BRAND 1,FORM 1,SKU NAME 5,SKU # 5,"Description",15.61,15.92,15.30
2,MFG Name 1,BRAND 2,,,,,"Description",BRAND Total 1,BRAND Total 2,BRAND Total 3
3,MFG Name 1,BRAND 2,SUB BRAND X,,,,"Description",SUB BRAND Total 1,SUB BRAND Total 2,SUB BRAND Total 3
4,MFG Name 1,BRAND 2,SUB BRAND X,FORM Y,,,"Description",FORM Total 1,FORM Total 2,FORM Total 3
5,MFG Name 1,BRAND 2,SUB BRAND X,FORM Y,SKU NAME 1,SKU # 6,"Description",212.16,212.05,12.26
5,MFG Name 1,BRAND 2,SUB BRAND X,FORM Y,SKU NAME 2,SKU # 7,"Description",13.54,13.95,13.12
5,MFG Name 1,BRAND 2,SUB BRAND X,FORM Y,SKU NAME 3,SKU # 8,"Description",14.62,7.73,21.51
5,MFG Name 1,BRAND 2,SUB BRAND X,FORM Y,SKU NAME 4,SKU # 9,"Description",10.75,-,21.51
5,MFG Name 1,BRAND 2,SUB BRAND X,FORM Y,SKU NAME 5,SKU # 10,"Description",2.61,2.92,2.30
1,MFG Name 2,,,,,,"Description",MFG Total 1,MFG Total 2,MFG Total 3
2,MFG Name 2,BRAND 3,,,,,"Description",BRAND Total 1,BRAND Total 2,BRAND Total 3
3,MFG Name 2,BRAND 3,SUB BRAND P,,,,"Description",SUB BRAND Total 1,SUB BRAND Total 2,SUB BRAND Total 3
4,MFG Name 2,BRAND 3,SUB BRAND P,FORM Q,,,"Description",FORM Total 1,FORM Total 2,FORM Total 3
5,MFG Name 2,BRAND 3,SUB BRAND P,FORM Q,SKU NAME 11,SKU # 1,"Description",152.16,12.05,12.26
5,MFG Name 2,BRAND 3,SUB BRAND P,FORM Q,SKU NAME 12,SKU # 2,"Description",16.54,16.95,16.12
5,MFG Name 2,BRAND 3,SUB BRAND P,FORM Q,SKU NAME 13,SKU # 3,"Description",14.62,7.73,21.51
5,MFG Name 2,BRAND 3,SUB BRAND P,FORM Q,SKU NAME 14,SKU # 4,"Description",10.75,-,21.51
5,MFG Name 2,BRAND 3,SUB BRAND P,FORM Q,SKU NAME 15,SKU # 5,"Description",2.61,2.92,3.30
2,MFG Name 2,BRAND 4,,,,,"Description",BRAND Total 1,BRAND Total 2,BRAND Total 3
3,MFG Name 2,BRAND 4,SUB BRAND V,,,,"Description",SUB BRAND Total 1,SUB BRAND Total 2,SUB BRAND Total 3
4,MFG Name 2,BRAND 4,SUB BRAND V,FORM U,,,"Description",FORM Total 1,FORM Total 2,FORM Total 3
5,MFG Name 2,BRAND 4,SUB BRAND V,FORM U,SKU NAME 11,SKU # 1,"Description",14.16,14.05,15.26
5,MFG Name 2,BRAND 4,SUB BRAND V,FORM U,SKU NAME 12,SKU # 2,"Description",12.54,1.95,23.12
5,MFG Name 2,BRAND 4,SUB BRAND V,FORM U,SKU NAME 13,SKU # 3,"Description",14.62,7.73,21.51
5,MFG Name 2,BRAND 4,SUB BRAND V,FORM U,SKU NAME 14,SKU # 4,"Description",10.75,-,21.51
5,MFG Name 2,BRAND 4,SUB BRAND V,FORM U,SKU NAME 15,SKU # 5,"Description",34.61,45.92,4.30




Moderator's Comments:
Mod Comment Please use code tags next time for your code and data. Thanks
# 2  
Old 04-08-2014
You need to trim the leading space from the values in the file.
# 3  
Old 04-08-2014
Thanks.
I think there is more to it than just the trimming.

In Principle, here is what I need to do - not sure how to write that in awk:

From column 1:
If the # = 1 identify the mfg name
If the # = 2 identify the brand name
If the # = 3 identify the sub-brand name
If the # = 4 identify the form name
If the # = 5 identify the sku no and name

If the script is reading identifer 2 - then fill in the mfg name as captured above (seperated by comma)
If the script is reading identifer 3 - then fill in the mfg name and brand name as captured above (seperated by comma)
If the script is reading identifer 4 - then fill in the mfg name and brand name and sub brand as captured above (seperated by comma)
Do this until a new pattern is identified - i.e. new form, subbrand, brand of mfg and repeat this.

Any suggestions?
# 4  
Old 04-08-2014
I checked your file. If you delimit by comma then you have 7 fields of data. This is standard awk stuff. You need to start with this...

Code:
awk -F\, '{printf("%-s,%s,%-s,%s,%-s,%-s,%-s\n",$1,$2,$3,$4,$5,$6,$7)}' x.x

and a routine to trim the strings and adjust the string directive
as required.
and something like this for trimming
Code:
awk -F, '/,/{gsub(/ /, "", $0); print} ' input.txt

Put that logic together.
# 5  
Old 04-09-2014
Unfortunately, your sample input does not correspond to your sample output. Try
Code:
awk -F\, 'NR==1 ||
          $1==0 {print; next}
                {sub(/^ */, "", $2)
                 TMP[$1]=$2
                 printf "%s", $1
                 for (i=1;  i<=$1; i++) printf ",%s", TMP[i]
                 for (i=$1; i<5;   i++) printf ","
                 for (i=3;  i<=NF; i++) printf ",%s", $i
                 printf "\n"}
        ' file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Format a file using awk

I have a file as below empty May7 noread May8 How can process this file and print as below. neat and clean. Filename Date Created empty May7 noread May8 I tried something with printf before creating the file itself... (3 Replies)
Discussion started by: Tuxidow
3 Replies

2. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

3. Shell Programming and Scripting

awk Script to format output

Hi all, I'm fairly new to this and learning along the way, so bare with me... I'm trying to format the output from a script to be more read-friendly. The output contains more servers and more processes but for as example it will do: Server: Test1 ... (4 Replies)
Discussion started by: Bobsonm
4 Replies

4. Shell Programming and Scripting

Changing format of file with awk

Hi all, I have a file that looks like this: Closest words to: manifesto >>>> Closest words to: passport >>>> and I want to reformat this with awk with the following desired result: manifesto 0.99999999999999978, 'manifesto' 0.72008211381623111, 'communiqu\xe9'... (5 Replies)
Discussion started by: owwow14
5 Replies

5. Shell Programming and Scripting

awk to format file

Hello, I shall like using the function awk to modify the contents of the following file: /tmp/conf-1 -sec=sys,rw=lpar1:lpar2:lpar3,access=lpar1:lpar2:lpar3 /tmp/conf-2 -vers=4,sec=sys,rw=lpar4:lpar5:lpar6,access=lpar4:lpar5:lpar6 I need to have the result below towards another file ... (5 Replies)
Discussion started by: khalidou13
5 Replies

6. Shell Programming and Scripting

Need awk/sed to format a file

My content of source file is as below scr1 a1 scr2 a2 b2 scr3 a3 b3 c3 I need a awk/sed command (to be used in C shell)to format it to something like below scr1 $a1 >file1 scr2 $a2 $b2 >file2 scr3 $a3 $b3 $c3 >file3 (12 Replies)
Discussion started by: animesharma
12 Replies

7. Shell Programming and Scripting

Converting windows format file to unix format using script

Hi, I am having couple of files which i used to copy from windows to Linux, so now in case of text files (CTRL^M) appears at end of line. I know i can convert this windows format file to unix format file by running dos2unix. My requirement here is that i want to do it automatically using a... (5 Replies)
Discussion started by: sarbjit
5 Replies

8. Programming

awk script to convert a text file into csv format

hi...... thanks for allowing me to start a discussion i am collecting usb usage details of all users and convert it into csv files so that i can export it into some database.. the input text file is as follows:- USB History Dump by nabiy (c)2008 (1) --- Kingston DataTraveler 130 USB... (2 Replies)
Discussion started by: certteam
2 Replies

9. Shell Programming and Scripting

AWK CSV to TXT format, TXT file not in a correct column format

HI guys, I have created a script to read 1 column in a csv file and then place it in text file. However, when i checked out the text file, it is not in a column format... Example: CSV file contains name,age aa,11 bb,22 cc,33 After using awk to get first column TXT file... (1 Reply)
Discussion started by: mdap
1 Replies

10. Shell Programming and Scripting

Format output using awk in script.

Guys, I have a script which hits the database and pulls the information that I need into files. Now I want to format these files to make them easy to read. The sample format of the file will be like.... <Start_of_File> Header1 .....xsdfsfa...adfa...... Header2 ....afefas .aefaefsdf...... (8 Replies)
Discussion started by: bperl
8 Replies
Login or Register to Ask a Question