Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Awk to convert a flat file to CSV file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Awk to convert a flat file to CSV file
# 1  
Old 07-10-2008
Awk to convert a flat file to CSV file
Hi ,

I have a file with contents as below:

Code:
Contract Cancellation Report UARCNCL 
LOS CODE DATE REAS TYPE AMOUNT AMOUNT LETTER BY
========= ======= ==== ==== ==== ========= ==== ==== 
8174739 7641509 1S NONE CRCD 30-JUN-2008 NPAR N .00 .00 CCAN 
8678696 8091709 1S NONE DDEB 30-JUN-2008 MVD P .00 .00 LOVES1 
8198258 7663210 1S NONE GIRO 30-JUN-2008 NPAR N .00 .00 CCAN

With the above text, i have two requirements:
1) remove the lines that end with UARCNCL, remove the lines that start with LOS.
2) In rest of the lines, the fields should be comma separated and written to a new file.

the new file should be like:

Code:
8174739,7641509,1S,NONE,CRCD,30-JUN-2008,NPAR,N,.00,.00,CCAN 
8678696,8091709,1S,NONE,DDEB,30-JUN-2008,MVD,P,.00,.00,LOVES1 
8198258,7663210,1S,NONE,GIRO,30-JUN-2008,NPAR,N,.00,.00,CCAN

I'am new to AWK and read through some stuufs in net and wrote a small script( but it did not work)

Code:
awk '{if ($4 != 'UARCNCL') print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12}' new > new1

Please correct me on the above script or please advice me on how shall i get the two rewuirements done.

Thanks in advance.

Thanks,
kumudha
Last edited by radoulov; 07-10-2008 at 06:20 AM.. Reason: please use code tags
# 2  
Old 07-10-2008
Hope this will be helpful
Code:
$ cat csv.awk
{
  if($NF=="UARCNCL" || $1=="LOS" || $1=="=========") next;
  gsub(" ",",");
  print
}

$ cat test.txt
Contract Cancellation Report UARCNCL 
LOS CODE DATE REAS TYPE AMOUNT AMOUNT LETTER BY
========= ======= ==== ==== ==== ========= ==== ==== 
8174739 7641509 1S NONE CRCD 30-JUN-2008 NPAR N .00 .00 CCAN
8678696 8091709 1S NONE DDEB 30-JUN-2008 MVD P .00 .00 LOVES1
8198258 7663210 1S NONE GIRO 30-JUN-2008 NPAR N .00 .00 CCAN

$ awk -f csv.awk test.txt
8174739,7641509,1S,NONE,CRCD,30-JUN-2008,NPAR,N,.00,.00,CCAN
8678696,8091709,1S,NONE,DDEB,30-JUN-2008,MVD,P,.00,.00,LOVES1
8198258,7663210,1S,NONE,GIRO,30-JUN-2008,NPAR,N,.00,.00,CCAN

Last edited by radoulov; 07-10-2008 at 06:21 AM.. Reason: please use code tags
# 3  
Old 07-10-2008
thanks Ranjith!

But when i run the awk, i get the syntax error at line 3, please help me
# 4  
Old 07-10-2008
Code:
awk 'NR < 4{next}{gsub(" ",",");print}' file > newfile

With sed it should be something like:

Code:
sed -e '1,3d' -e 's/ /,/g'  file > newfile

Regards
# 5  
Old 07-11-2008
Code:
egrep -v "UARCNCL$|^LOS|^===" inputfile |sed -e 's/ /,/g'

# 6  
Old 07-11-2008
Or try:
Code:
awk '! /^LOS|UARCNCL$/{gsub(" ",",");print}' file

# 7  
Old 07-17-2008
Hi all,

Thanks a lot for your help, the script worked perfectly.

But i have a small requirement change, since the input text file is a fixed width file, my requirement is to check the field width and seperate them by ",".

The field lenght is determined by the lenght of '====' below the heading of each field as below:

1st field --> 9 bit long
2nd field --> 7 bit long
3rd field --> 4 bit long ....

Input text file

CUST PREM RATE LOS CODE DATE REAS TYPE
========= ======= ==== ==== ==== =========== ==== ====
8174739 7641509 1S NONE CRCD 30-JUN-2008 NPAR N
8678696 8091709 1S NONE DDEB 30-JUN-2008 MVD P
8198258 7663210 1S NONE GIRO 30-JUN-2008 NPAR N
8064034 7540836 1S NONE GUAR 30-JUN-2008 NPAR N

Output required:

8174739 ,7641509,1S ,NONE,CRCD,30-JUN-2008,NPAR,N
8678696 ,8091709,1S ,NONE,DDEB,30-JUN-2008,MVD ,P
8198258 ,7663210,1S ,NONE,GIRO,30-JUN-2008,NPAR,N
8064034 ,7540836,1S ,NONE,GUAR,30-JUN-2008,NPAR,N

thanks in advance for your help.

Thanks !
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Converting csv file to flat file

Hi All, I have a csv file which is comma seperated. I need to convert to flat file with preferred column length country,id Australia,1234 Africa,12399999 Expected output country id Australia 1234 Africa 12399999 the flat file should predefined length on respective... (8 Replies)
Discussion started by: rohit_shinez
8 Replies

2. UNIX for Dummies Questions & Answers

Convert flat file to csv

Hi I have a file like this: a=1 b=2 c=3 a=4 b=2 d=3 a=3 c=4 How can I change this to csv format a,b,c,d 1,2,3,, 4,2,,3 3,,4,, Please use code tags next time for your code and data. Thanks (10 Replies)
Discussion started by: sandip_2014
10 Replies

3. Shell Programming and Scripting

How to convert excel file to csv file or text file?

Hi all, I need to find a way to convert excel file into csv or a text file in linux command. The reason is I have hundreds of files to convert. Another complication is the I need to delete the first 5 lines of the excel file before conversion. so for instance input.xls description of... (6 Replies)
Discussion started by: johnkim0806
6 Replies

4. Shell Programming and Scripting

Awk to convert a text file to CSV file with some string manipulation

Hi , I have a simple text file with contents as below: 12345678900 971,76 4234560890 22345678900 5971,72 5234560990 32345678900 71,12 6234560190 the new csv-file should be like: Column1;Column2;Column3;Column4;Column5 123456;78900;971,76;423456;0890... (9 Replies)
Discussion started by: FreddyDaKing
9 Replies

5. Shell Programming and Scripting

reading a csv file and creating a flat file

hi i have written a script for reading a csv file and creating a flat file, suggest if this script can be optimized #---------------- FILENAME="$1" SCRIPT=$(basename $0) #-----------------------------------------// function usage { echo "\nUSAGE: $THIS_SCRIPT file_to_process\n"... (3 Replies)
Discussion started by: mprakasheee
3 Replies

6. Shell Programming and Scripting

Convert CSV file (with double quoted strings) to pipe delimited file

Hi, could some help me convert CSV file (with double quoted strings) to pipe delimited file: here you go with the same data: 1,Friends,"$3.99 per 1,000 listings",8158here " 1,000 listings " should be a single field. Thanks, Ram (8 Replies)
Discussion started by: Ram.Math
8 Replies

7. 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

8. Shell Programming and Scripting

Flat file to csv conversion

Hi Guy's can someone help me in converting the following I have a flat text file which has several thousand lines which I need to convert to a csv it's got a consistent format but basically want every time it hit's txt to create a new line with the subsequent lines comma delimited for example ... (6 Replies)
Discussion started by: p1_ben
6 Replies

9. Shell Programming and Scripting

Convert case on specified position of flat file

Please help Need a script which will do the following : Search on fixed width file , go to position (25,2) which means 25th and 26th position, Find if there are any char in lower case: For example 25,2 can be (9T) or (9w) or (Ww) or (wW)....The two positions can be numeric or alpha...no... (13 Replies)
Discussion started by: ssantoshss
13 Replies

10. Shell Programming and Scripting

Need help to convert Flat file to HTML

Hello I need help to convert flat file data to HTML Table format. I am generating everyday Flat file and want to convert into HTML Table format. The format of my file is: version host Total YRS NO APPS PSD 10 Sun 30 2 4 6 7 and flat... (11 Replies)
Discussion started by: getdpg
11 Replies
Login or Register to Ask a Question