How to parse this file using awk and output in CSV format?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to parse this file using awk and output in CSV format?
# 1  
Old 11-23-2015
How to parse this file using awk and output in CSV format?

My source file looks like this:
Code:
Cust-Number = "101"
Cust-Name="Joe"
Cust-Town="London"
Cust-hobby="tennis"
Cust-purchase="200"

Cust-Number = "102"
Cust-Name="Mary"
Cust-Town="Newyork"
Cust-hobby="reading"
Cust-purchase="125"

Now I want to parse this file (leaving out hobby) and output in csv format
Code:
Cust-Number   Cust-Name  Cust-Town  Cust-Purchase
101,Joe,London,200
102,Mary,Newyork,125

Anybody can help me out please?

Thanks
Balav

Last edited by Franklin52; 11-23-2015 at 10:17 AM.. Reason: Please use code tags
# 2  
Old 11-23-2015
Please use code tags as required by forum rules!

Any attempts/ideas/thoughts from your side?
# 3  
Old 11-23-2015
Novice in Unix scripting

Hi Rudic

No, I am very new to shell programming and AWK principles. As part of a project I have to process the data in the files and load them into database tables.

Can someone help me?

Cheers
# 4  
Old 11-23-2015
Howsoever, try
Code:
awk -F= '
BEGIN                   {HD = "Cust-Number,Cust-Name,Cust-Town,Cust-purchase"
                         print HD
                         HDCnt  = split (HD, HDArr, ",")
                         NXTREC = HDArr[1]
                         HDCM=","HD","
                        }

                        {gsub (/[ "]*/, "")
                        }

 $1 == NXTREC           {if (PR)        {for (i=1; i<=HDCnt; i++) printf "%s%s", RES[HDArr[i]], (i == HDCnt)?"\n":","
                                         delete RES
                                        }
                         PR = 1
                        }

 HDCM ~ OFS $1 OFS      {RES[$1] = $0
                         sub ($1 FS, "", RES[$1])
                        }

END                     {for (i=1; i<=HDCnt; i++) printf "%s%s", RES[HDArr[i]], (i == HDCnt)?"\n":","
                        }
' OFS="," file
Cust-Number,Cust-Name,Cust-Town,Cust-purchase
101,Joe,London,200
102,Mary,Newyork,125

Your sample's header doesn't use commas as does the rest of the file; this Ihave adapted. Should you insist on the spaces as field separators, correct script accordingly.
These 2 Users Gave Thanks to RudiC For This Post:
# 5  
Old 11-23-2015
Hi Rudi

Thanks for your help. I didn't expect you to write a full code to a solution but just give me some recommendations, tips, etc. But it's really so kind of you. I'll try and understand this code myself.

I just ran it against my file but it gave me syntax errors. I am just wondering if the AWK works differently (syntax wise) across different Unix platforms. I am on SPARC Solaris

This is the error:

Code:
awk: syntax error near line 8
awk: illegal statement near line 8
awk: newline in string near line 8
awk: syntax error near line 10
awk: illegal statement near line 10
awk: syntax error near line 15
awk: bailing out near line 15


Last edited by Don Cragun; 02-19-2016 at 01:07 PM.. Reason: Add CODE tags.
# 6  
Old 11-23-2015
Let me quote Don Cragun for you:
Quote:
If you are using a Solaris/SunOS system, use /usr/xpg4/bin/awk or nawk instead of awk .
This User Gave Thanks to RudiC For This Post:
# 7  
Old 11-23-2015
Another approach:
Code:
awk -F\" 'BEGIN{print "Cust-Number,Cust-Name,Cust-Town,Cust-Purchase"}
  /hobby/ || !NF{next} 
  /purchase/{print $2; next}
  {printf $2 ","}
' file

This User Gave Thanks to Franklin52 For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Programming

Python or Shell script to Grep strings from input file and output in csv format

Hi Experts, I am writing a python script to grep string from file and display output in csv file as in attached screenshot https://drive.google.com/file/d/1gfUUdfmQma33tz65NskThYDhkZUGQO0H/view Input file(result_EPFT_config_device) Below is the python script i have prepared as of... (1 Reply)
Discussion started by: as7951
1 Replies

2. Shell Programming and Scripting

Csv format output file using scirpt

Hi All, I get the test result file daily after running the main test script. from the resultfile, need to fetch only server info and status and put them in tabular format in a file and as well in CSV format output file. I tried using awk command but am not able to put them in tabluar... (6 Replies)
Discussion started by: Optimus81
6 Replies

3. Shell Programming and Scripting

Match list of strings in File A and compare with File B, C and write to a output file in CSV format

Hi Friends, I'm a great fan of this forum... it has helped me tone my skills in shell scripting. I have a challenge here, which I'm sure you guys would help me in achieving... File A has a list of job ids and I need to compare this with the File B (*.log) and File C (extend *.log) and copy... (6 Replies)
Discussion started by: asnandhakumar
6 Replies

4. Shell Programming and Scripting

How to parse csv format?

Hi, I have a file with 3 fields in csv format: /tmp/foo/,MODIFY,bar/toto "/tmp/foo, bar/","ATTRIB,ISDIR","toto, tata/foobar"I would like to split fields in order to obtain the following: Line1: /tmp/foo/ MODIFY bar/totoLine2: /tmp/foo, bar/ ATTRIB,ISDIR toto, tata/foobarCan't find my way... (11 Replies)
Discussion started by: chebarbudo
11 Replies

5. Shell Programming and Scripting

Parse file using awk and work in awk output

hi guys, i want to parse a file using public function, the file contain raw data in the below format i want to get the output like this to load it to Oracle DB MARWA1,BSS:26,1,3,0,0,0,0,0.00,22,22,22.00 MARWA2,BSS:26,1,3,0,0,0,0,0.00,22,22,22.00 this the file raw format: Number of... (6 Replies)
Discussion started by: dagigg
6 Replies

6. Shell Programming and Scripting

format output in csv file

I am sending the output of a file to .csv file. The output should look like this: Total Customers Processed:,8 Total Customers Skipped:,0 Total Customers Added:,8 Total Customers Changed:,0 Total Policies Deleted:,0 Total Policies Failed:,0 total:,8 Now i want this output in... (1 Reply)
Discussion started by: Prashant Jain
1 Replies

7. Shell Programming and Scripting

how to parse the file in xml format using awk/nawk

Hi All, I have an xml file with the below format. <a>111</a><b>222</b><c>333<c><d><e>123</e><f>234</f><d><e>456</e><f>789</f> output needed is 111,222,333,123,234 111,222,333,456,789 nawk 'BEGIN{FS="<|>"} {print a,b,c,e,f a="" ... (7 Replies)
Discussion started by: natalie23
7 Replies

8. Shell Programming and Scripting

parse csv file, sha1 hash and output

I have a file, not really a csv, but containing delineated data just the same. Lets call that file "raw_data.txt". It contains data in the format of company name:fein number like this: first company name:123456789 second company name:987654321 what i need to do is read this file, apply... (11 Replies)
Discussion started by: FreddyG
11 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. UNIX for Advanced & Expert Users

How to Parse a CSV file into a Different Format

Hi I have a CSV file with me in this format Currency, USD, EUR, USD, 1.00, 1.32, EUR, 0.66, 1.00, How do I transpose the file to get to the format below. currency, currency, rate USD, USD, 1.00 USD, EUR, 1.32 EUR, USD, 0.66 EUR, EUR, 1.00 Thanks for your help We are using... (2 Replies)
Discussion started by: cdesiks
2 Replies
Login or Register to Ask a Question